CODEWITHSUNDEEP
djangodatabasemongodbnosqldjongo
Faruk Erat
Faruk Erat

Reputation: 1

Order of filters affect the query result in Djongo

I use Djongo package to connect to MongoDB from my Django app. However, there is inconsistency in the query results, even though not expected.

Here is my model:

class Item(models.Model):
    name = models.CharField(max_length=100)
    description = models.TextField()
    date = models.DateTimeField(db_index=True)
    port_inventory_id = models.IntegerField(db_index=True)

This query first filters by port_inventory_id, then date__range and returns the correct result Code:

items = Item.objects.filter(port_inventory_id=999999).filter(date__range=(date_from, date_to))

Query:

SELECT "myapp_item"."id", "myapp_item"."name", "myapp_item"."description", "myapp_item"."date", "myapp_item"."port_inventory_id" FROM "myapp_item" WHERE ("myapp_item"."port_inventory_id" = 999999 AND "myapp_item"."date" BETWEEN 2024-07-12 10:11:23.168955 AND 2024-08-01 10:11:23.168965)

However, this query first filters by date__range, then port_inventory_id and returns the same number of items without the filter(port_inventory_id=999999)

Code:

items = Item.objects.filter(date__range=(date_from, date_to)).filter(port_inventory_id=999999)

Query:

SELECT "myapp_item"."id", "myapp_item"."name", "myapp_item"."description", "myapp_item"."date", "myapp_item"."port_inventory_id" FROM "myapp_item" WHERE ("myapp_item"."date" BETWEEN 2024-07-12 10:23:02.248130 AND 2024-08-01 10:23:02.248138 AND "myapp_item"."port_inventory_id" = 999999)

I also tried making one filter call with two filters

Code:

items = Item.objects.filter(date__range=(date_from, date_to), port_inventory_id=999999)

Query:

SELECT "myapp_item"."id", "myapp_item"."name", "myapp_item"."description", "myapp_item"."date", "myapp_item"."port_inventory_id" FROM "myapp_item" WHERE ("myapp_item"."date" BETWEEN 2024-07-12 10:12:43.089380 AND 2024-08-01 10:12:43.089389 AND "myapp_item"."port_inventory_id" = 999999)

And also reverse

Code:

items = Item.objects.filter(port_inventory_id=999999, date__range=(date_from, date_to))

Query:

SELECT "myapp_item"."id", "myapp_item"."name", "myapp_item"."description", "myapp_item"."date", "myapp_item"."port_inventory_id" FROM "myapp_item" WHERE ("myapp_item"."date" BETWEEN 2024-07-12 10:16:14.734102 AND 2024-08-01 10:16:14.734110 AND "myapp_item"."port_inventory_id" = 999999)

The only way it works is this:

Code:

items = Item.objects.filter(port_inventory_id=999999).filter(date__range=(date_from, date_to))

Query:

SELECT "myapp_item"."id", "myapp_item"."name", "myapp_item"."description", "myapp_item"."date", "myapp_item"."port_inventory_id" FROM "myapp_item" WHERE ("myapp_item"."port_inventory_id" = 999999 AND "myapp_item"."date" BETWEEN 2024-07-12 10:11:23.168955 AND 2024-08-01 10:11:23.168965)

It only works when port_inventory_id is queried first. I did not expect the result to change this way.

Any help is much appeciated...

Upvotes: 0

Views: 50

Answers (1)

Maxim Danilov
Maxim Danilov

Reputation: 3350

I'm not sure if I solved your problem, but in our project we also have a problem with __range. We solved it in a stupid and simple way (KISS). For your case the solution could be like this:

items = Item.objects.filter(port_inventory_id=999999, date__gte=date_from, date__lte=date_to)

I recommend that you create a query and then apply it to the queryset:

query = {'port_inventory_id':999999, 'date__gte': date_from, 'date__lte': date_to}
items = Item.objects.filter(**query)

And for more complex queries, I suggest using the Q query helper:

query = Q(port_inventory_id=999999) & Q(date__gte=date_from) & Q(date__lte=date_to)
items = Item.objects.filter(query)

More about Q here: https://docs.djangoproject.com/en/5.0/topics/db/queries/#complex-lookups-with-q-objects

Upvotes: 0

Related Questions