时间戳

首页 > 解决方案 > Django lt+gt 条件与。不等条件

django - Django lt+gt 条件与。不等条件

问题描述

我有以下型号

class P(models.Model):
    name = models.CharField(max_length=30,  blank=False)


class pr(models.Model):
    p = models.ForeignKey(P, on_delete=models.CASCADE, related_name='cs')
    r = models.CharField(max_length=1)
    c = models.ForeignKey(P, on_delete=models.CASCADE, related_name='ps')
    rc = models.PositiveSmallIntegerField()

    class Meta:
        unique_together = (('p', 'c'),)

和数据

"id","name"
69,"Hunter"
104,"Savannah"
198,"Adrian"
205,"Andrew"
213,"Matthew"
214,"Aiden"
218,"Madison"
219,"Harper"
---
"id","r","rc","c_id","p_id"
7556,"F",1,219,213
7557,"M",1,219,218
7559,"H",3,218,213
7572,"F",1,214,213
7573,"M",1,214,218
7604,"F",1,198,213
7605,"M",1,198,218
7788,"H",3,104,205
7789,"F",1,104,213
7790,"M",1,104,218
7866,"M",1,69,104
7867,"F",1,69,205

以下两个查询应该产生相似的结果

A = P.objects.filter(Q(Q(ps__rc__lt = 3) | Q(ps__rc__gt = 3)), ps__p__cs__c = 198).exclude(pk = 198).annotate(bt=Count('ps__rc', filter=Q(ps__rc = 1, ps__p__cs__rc = 1)))

B = P.objects.filter(~Q(ps__rc = 3), ps__p__cs__c = 198).exclude(pk = 198).annotate(bt=Count('ps__rc', filter=Q(ps__rc = 1, ps__p__cs__rc = 1)))

奇怪的是；查询 A 产生预期结果，但 B 缺少模型实例 104！经过进一步的故障排除后，我发现查询 B 生成了以下 SQL：

SELECT "eftapp_p"."id", "eftapp_p"."name", COUNT("eftapp_pr"."rc") FILTER (WHERE (T4."rc" = 1 AND "eftapp_pr"."rc" = 1)) AS "bt" FROM "eftapp_p" LEFT OUTER JOIN "eftapp_pr" ON ("eftapp_p"."id" = "eftapp_pr"."c_id") LEFT OUTER JOIN "eftapp_p" T3 ON ("eftapp_pr"."p_id" = T3."id") LEFT OUTER JOIN "eftapp_pr" T4 ON (T3."id" = T4."p_id") WHERE (NOT ("eftapp_p"."id" IN (SELECT U1."c_id" FROM "eftapp_pr" U1 WHERE U1."rc" = 3)) AND T4."c_id" = 198 AND NOT ("eftapp_p"."id" = 198)) GROUP BY "eftapp_p"."id"

是否有一种解决方法可以强制生成的 SQL 查询的 WHERE 子句为：WHERE NOT ("eftapp_pr"."rc" = 3) AND T4."c_id" = 198 AND NOT ("eftapp_p"."id" = 198) ?

Django 版本：3.0.4 Python 版本：3.7.3 数据库：x86_64-pc-linux-gnu 上的 PostgreSQL 11.9 (Debian 11.9-0+deb10u1)，由 gcc (Debian 8.3.0-6) 8.3.0, 64- 编译少量

标签： djangodjango-models

解决方案

我可能在这里遗漏了一些东西，但你不能ps__rc=3把.exclude filter?

推荐阅读