通过跨关系的字段查找在 django 模型上进行链过滤和排除

发布于 2024-09-09 22:54:08 字数 1231 浏览 7 评论 0原文

我有以下模型:

class Order_type(models.Model):
    description = models.CharField()

class Order(models.Model):
    type= models.ForeignKey(Order_type)
    order_date = models.DateField(default=datetime.date.today)
    status = models.CharField()
    processed_time= models.TimeField()

我想要一个包含满足此条件的订单的订单类型列表:(order_date <= 今天 AND processed_time 为空 AND status 不为空)

我尝试过:

qs = Order_type.objects.filter(order__order_date__lte=datetime.date.today(),\
     order__processed_time__isnull=True).exclude(order__status='')

这适用于原始订单列表:

orders_qs = Order.objects.filter(order_date__lte=datetime.date.today(), processed_time__isnull=True)
orders_qs = orders_qs.exclude(status='')

但是 qs 不是正确的查询集。我认为它实际上返回了一个更窄的过滤器(因为不存在记录),但我不确定是什么。根据这个(django参考),因为我引用了一个相关模型,所以我认为排除适用于原始查询集(而不是过滤器中的查询集),但我不知道具体如何操作。

好吧,我只是想到了这个,我认为可行,但感觉很草率(有更好的方法吗?):

qs = Order_type.objects.filter(order__id__in=[o.id for o in orders_qs])

I have the following models:

class Order_type(models.Model):
    description = models.CharField()

class Order(models.Model):
    type= models.ForeignKey(Order_type)
    order_date = models.DateField(default=datetime.date.today)
    status = models.CharField()
    processed_time= models.TimeField()

I want a list of the order types that have orders that meet this criteria: (order_date <= today AND processed_time is empty AND status is not blank)

I tried:

qs = Order_type.objects.filter(order__order_date__lte=datetime.date.today(),\
     order__processed_time__isnull=True).exclude(order__status='')

This works for the original list of orders:

orders_qs = Order.objects.filter(order_date__lte=datetime.date.today(), processed_time__isnull=True)
orders_qs = orders_qs.exclude(status='')

But qs isn't the right queryset. I think its actually returning a more narrowed filter (since no records are present) but I'm not sure what. According to this (django reference), because I'm referencing a related model I think the exclude works on the original queryset (not the one from the filter), but I don't get exactly how.

OK, I just thought of this, which I think works, but feels sloppy (Is there a better way?):

qs = Order_type.objects.filter(order__id__in=[o.id for o in orders_qs])

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

听闻余生 2024-09-16 22:54:08

发生的情况是,exclude() 查询把你的事情搞乱了。基本上,它排除了任何至少有一个没有状态的订单的 Order_type,这几乎肯定不是您想要发生的情况。

您的情况最简单的解决方案是在 filter() 参数中使用 order__status__gt='' 。但是,您还需要将 distinct() 附加到查询末尾,因为否则,如果查询集有多个与查询匹配的订单,那么您将得到一个具有相同 Order_type 的多个实例的 QuerySet。询问。这应该有效:

qs = Order_type.objects.filter(
    order__order_date__lte=datetime.date.today(),
    order__processed_time__isnull=True,
    order__status__gt='').distinct()

顺便说一句,在问题末尾给出的 qs 查询中,您不必说 order__id__in=[o.id for o inorders_qs],您可以只需使用 order__in=orders_qs (您仍然需要 distinct())。因此,这也将起作用:

qs = Order_type.objects.filter(order__in=Order.objects.filter(
    order_date__lte=datetime.date.today(),
    processed_time__isnull=True).exclude(status='')).distinct()

附录(编辑):

这是 Django 针对上述查询集发出的实际 SQL:

SELECT DISTINCT "testapp_order_type"."id", "testapp_order_type"."description"
    FROM "testapp_order_type"
    LEFT OUTER JOIN "testapp_order"
    ON ("testapp_order_type"."id" = "testapp_order"."type_id")
        WHERE ("testapp_order"."order_date" <= E'2010-07-18'
        AND "testapp_order"."processed_time" IS NULL
        AND "testapp_order"."status" > E'' );

SELECT DISTINCT "testapp_order_type"."id", "testapp_order_type"."description"
    FROM "testapp_order_type"
    INNER JOIN "testapp_order"
    ON ("testapp_order_type"."id" = "testapp_order"."type_id")
        WHERE "testapp_order"."id" IN
            (SELECT U0."id" FROM "testapp_order" U0
                WHERE (U0."order_date" <= E'2010-07-18'
                AND U0."processed_time" IS NULL
                AND NOT (U0."status" = E'' )));

EXPLAIN 揭示了第二个查询的成本稍微高一些(成本为 28.99,而使用非常小的数据集)。

What's happening is that the exclude() query is messing things up for you. Basically, it's excluding any Order_type that has at least one Order without a status, which is almost certainly not what you want to happen.

The simplest solution in your case is to use order__status__gt='' in you filter() arguments. However, you will also need to append distinct() to the end of your query, because otherwise you'd get a QuerySet with multiple instances of the same Order_type if it has more than one Order that matches the query. This should work:

qs = Order_type.objects.filter(
    order__order_date__lte=datetime.date.today(),
    order__processed_time__isnull=True,
    order__status__gt='').distinct()

On a side note, in the qs query you gave at the end of the question, you don't have to say order__id__in=[o.id for o in orders_qs], you can simply use order__in=orders_qs (you still also need the distinct()). So this will also work:

qs = Order_type.objects.filter(order__in=Order.objects.filter(
    order_date__lte=datetime.date.today(),
    processed_time__isnull=True).exclude(status='')).distinct()

Addendum (edit):

Here's the actual SQL that Django issues for the above querysets:

SELECT DISTINCT "testapp_order_type"."id", "testapp_order_type"."description"
    FROM "testapp_order_type"
    LEFT OUTER JOIN "testapp_order"
    ON ("testapp_order_type"."id" = "testapp_order"."type_id")
        WHERE ("testapp_order"."order_date" <= E'2010-07-18'
        AND "testapp_order"."processed_time" IS NULL
        AND "testapp_order"."status" > E'' );

SELECT DISTINCT "testapp_order_type"."id", "testapp_order_type"."description"
    FROM "testapp_order_type"
    INNER JOIN "testapp_order"
    ON ("testapp_order_type"."id" = "testapp_order"."type_id")
        WHERE "testapp_order"."id" IN
            (SELECT U0."id" FROM "testapp_order" U0
                WHERE (U0."order_date" <= E'2010-07-18'
                AND U0."processed_time" IS NULL
                AND NOT (U0."status" = E'' )));

EXPLAIN reveals that the second query is ever so slightly more expensive (cost of 28.99 versus 28.64 with a very small dataset).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文