Djongo中过滤器顺序影响查询结果的异常问题

在Django应用中使用Djongo连接MongoDB时，出现了查询结果不符合预期的不一致问题：仅当先过滤port_inventory_id再过滤date__range时能得到正确结果，其他过滤顺序或单filter多条件的方式均返回错误结果。

模型定义 #

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)

各查询方式及结果 #

正确查询（先过滤port_inventory_id） #

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

生成的查询语句：

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)

此方式返回正确结果。

错误查询1（先过滤date__range） #

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

生成的查询语句：

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)

此方式返回结果数量与未添加port_inventory_id过滤时一致，未正确过滤端口ID。

错误查询2（单filter多条件） #

无论两个条件顺序如何，结果均不正确：

顺序1：date__range在前

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

生成的查询语句：

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)

顺序2：port_inventory_id在前

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

生成的查询语句：

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)

解决思路 #

1. 临时规避方案：坚持使用两次filter且先过滤port_inventory_id的方式，确保查询结果正确。
2. 升级Djongo版本：该问题大概率是Djongo在将Django ORM查询转换为MongoDB查询时的顺序处理bug，升级到最新版本可能已修复该问题。
3. 使用MongoDB原生查询：绕过Djongo的ORM转换层，直接使用原生查询保证准确性：

   # 方式1：使用pymongo直接查询
   from pymongo import MongoClient
   
   client = MongoClient('你的MongoDB连接URI')
   db = client['你的数据库名']
   collection = db['myapp_item']
   # 注意date需转换为MongoDB可识别的datetime类型
   items = collection.find({
       'port_inventory_id': 999999,
       'date': {'$gte': date_from, '$lte': date_to}
   })
   

4. 提交bug反馈：若升级版本后问题仍存在，可向Djongo官方仓库提交该bug，说明过滤顺序影响结果的具体情况。

内容的提问来源于stack exchange，提问作者Faruk Erat