Home > database >  Django query for column value search?
Django query for column value search?

Time:07-22

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?

CodePudding user response:

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.

  • Related