Django通过最后创建的对象进行反向查询 [英] Django reverse query by the last created object

问题描述

我有两个模型：

class SomeActivity(models.Model):
    name = models.ChartField(max_length=100)

class SomeStatus(models.Model):
    name = models.CharField(max_length=100)
    status = models.IntegerField(choises=STATUS_CHOISES)
    some_activity = models.ForeignKey(SomeActivity, related_name='statuses')

上次为之创建的状态该活动是当前活动。要获得它，我使用以下代码：

The last created status for the activity is the current one. To get it I use this code:

try:
    last_status = some_activity.statuses.latest('id')
except:
    last_status = None

但是问题是我想进行查询，以返回所有具有 last_status 与 status__in = [1，2]相匹配的活动 ] 。

But the problem is when I want to make a query that returns all Activities that have a last_status matching status__in=[1, 2].

推荐答案

此解决方案有点扭曲，但我认为它可以使用：

This solution is a little twisted, but I think it will work:

from django.db.models import Max

max_status_ids = SomeActivity.objects.filter(statuses__isnull=False).annotate(
              last_status_id=Max('statuses__id')
              ).values_list('last_status_id', flat=True)
status_satisfied_ids = SomeStatus.objects.filter(id__in=list(max_status_ids),
              status__in=[1, 2]).values_list('id', flat=True)
activities = SomeActivity.objects.filter(statuses__id__in=list(
              status_satisfied_ids))

我希望有更好的解决方案。

I hope there is a better solution.

更新

尝试

max_status_ids = SomeActivity.objects.annotate(last_status_id=Max('statuses')
                                    ).values('last_status_id')
activities = SomeActivity.objects.filter(statuses__in=max_status_ids,
                                         statuses__status__in=(1,2))

1. Django自动生成子查询 qs ， qs.values（）或 qs.values_list（） __ in 查找之后使用c $ c>。因此，无需包装带有 list（）的查询集（这也引入了不必要的评估，从而也引入了不必要的中间SQL）或编写 flat = qs.values_list（）


2. statuss__id__in c>活动 中的查找已经引入了表联接，因此可以将 status__in 从中移出将status_satisfied_ids 放入活动以利用联接。否则， status_satisfied_ids 会引入额外的选择。



1. Django automatically generates sub-query when qs, qs.values() or qs.values_list() is used after __in lookup. Thus there is no need to wrap the querysets w/ list() (which also introduces unnecessary evaluation and thus unnecessary intermediate SQLs) or to write flat=True inside qs.values_list()
2. statuses__id__in lookup inside activities already introduces a table join, thus it's bette to move status__in from status_satisfied_ids into activities to take advantage of the join. Or else, status_satisfied_ids would introduce extra select.

或者您可以使用<一个href = http://www.postgresql.org/docs/9.2/static/tutorial-window.html rel = nofollow> PostgreSQL的窗口函数以按排名直接过滤。

Or you could use Window functions of PostgreSQL to filter by ranked position directly.

这篇关于Django通过最后创建的对象进行反向查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！