Django query for column value search?

What is the Django query for this?

DB data –

col1     |    Col2
-------------------------
sahil1   | Cat 1.2.3
sahil2   | 1.2.3-XY2
sahil3   | 9.8.7,1.2.3,11.12.13
sahil4   | 1.2.3
sahil5   | 9.8.4,1.2.3-XY2,9.8.7
sahil6   | Cat 1.2.3,9.8.2,1.2.3

I only need record that contain “1.2.3” values not like – (“Cat 1.2.3” or “1.2.3-XY2” or any such value). And pattern “1.2.3” can be anywhere in column where column value can have comma separated values too.

Desired Result –

col1     |    Col2
-------------------------
sahil3   | 9.8.7,1.2.3,11.12.13
sahil4   | 1.2.3
sahil6   | Cat 1.2.3,9.8.2,1.2.3

When i am performing below Django query –

col2_count = TableName.objects.filter(col2__contains="1.2.3")

Getting all record but i only need record that contain “1.2.3” values not like – (“Cat 1.2.3” or “1.2.3-XY2” or any such value).

How do I implement this in Django?

Answer

Your data structure is a bit odd, in my opinion you should have separate objects for each of those entries rather than keeping potentially comma-separated values. However, you can do this by joining together Django Q() objects:

1. Complete match to “1.2.3”, or
2. Results that contain “,1.2.3”, or
3. Results that contain “, 1.2.3”

from django.db.models import Q

col_2_count = TableName.objects.filter(
   Q(col2="1.2.3")|
   Q(col2__icontains=",1.2.3")|
   Q(col2__icontains=", 1.2.3")
)

Because you have a variety of possible options, you need to string a few Q() options together. The | character is treated as OR.

Edit – per comments

col_2_count = TableName.objects.filter(
   Q(col2="1.2.3")|
   Q(col2__icontains=",1.2.3,")|
   Q(col2__icontains=", 1.2.3,")|
   Q(col2__icontains=", 1.2.3 ,")|
   Q(col2__endswith="1.2.3")
)