对具有外键的多个表进行分组查询

I'm having a hard time figuring out a query to do multiple groups in django: Here is my model:

在django中，我很难找到要执行多个组的查询:这是我的模型:

class Profile(models.Model):
    name = models.charField(max_length=15)

class Release(models.Model):
    date = models.DateField(auto_now_add=True)

class Analysis(models.Model):
    date = models.DateField(auto_now_add=True)
    profile = models.ForeignKey(Profile)
    release = models.ForeignKey(Release, null=True, blank=True, default=None)

What I want to do is get all analyses of profile 1&2 which have the latest release for that profile. Example:

我想做的是得到所有的概要文件1和2的分析，其中有该概要文件的最新版本。例子:

profile id | analysis_id   | release_id
    1      |     1         |   1
    1      |     2         |   1
    1      |    *3*        |   2
    1      |    *4*        |   2
    2      |    *5*        |   1
    2      |    *6*        |   1

Note that I am not just after the greatest release id, but the greatest release id for that profile.

请注意，我并不是仅仅追求最大的发布id，而是该概要文件的最大发布id。

I want to to get analysis ids 3,4,5,6. Here is my attempt which does not give me what I want:

我想要得到分析id 3 4 5 6。我的尝试并没有给我想要的:

print Analysis.objects.filter(profile__id__in=[1, 2]).annotate(rid=Max('release__id')).values_list('id', flat=True)

It returns all the ids. Any ideas how this can be done?

它返回所有的id。你知道怎么做吗?

1 个解决方案

#1

Use a reference filter after the annotation to refer back to the maximum value.

在注释之后使用引用过滤器来引用最大值。

from django.db.models import F
print Analysis.objects.filter(profile__id__in=[1, 2]
   ).annotate(rid=Max('release__id')
   ).filter(release__id=F('rid') 
   ).values_list('id', flat=True)

Note, I've used these before to great effect, but I haven't fully tested it with your model ^*.

注意，我以前用过这些，效果很好，但是我还没有完全用你的模型*测试它。

^{_{* Your millage may vary. Consult a doctor if database queries last longer than 4 hours.}}

*你的工龄可能不同。如果数据库查询持续时间超过4小时，请咨询医生。

#1