在Django ORM中加入两个查询 [英] Join two queries in Django ORM

问题描述

我有一个有生日的Person模型.我想创建一个查询，该查询返回所有人员信息以及一个附加字段，该字段告诉有多少人正在共享每个人的生日.在SQL中，我会这样写:

I have a Person model which has a birthday. I would like to create a query that returns all the persons information along with an additional field that tells how many people are sharing each person's birthday. In SQL I would write it like this:

SELECT p.name, b.count FROM persons as p INNER JOIN (SELECT birthday as date, COUNT(*) AS count FROM persons GROUP_BY birthday) AS b WHERE p.birthday = b.date

SELECT p.name, b.count FROM persons as p INNER JOIN (SELECT birthday as date, COUNT(*) AS count FROM persons GROUP_BY birthday) AS b WHERE p.birthday = b.date

使用Django查询集，我可以进行内部选择，但是我不知道如何进行内部联接.

With Django querysets I can do the inner select but I don't know how to do the inner join.

推荐答案

似乎很难使用ORM(尽管使用

Seems tough to do with the ORM (though maybe possible with extra).

您可以创建一个按日期计数的字典(最多忽略366个值，如果忽略年份):

You could create a dict of counts by date (max 366 values, if ignoring year):

 from django.db.models import Count
 birthdate = lambda d: d.strftime("%m-%d")

 # this runs the subquery in your SQL:
 birthdays = Person.objects.values('birthday')
 counts = birthdays.annotate(count=Count('birthday'))

 counts_by_date = {
     birthdate(r['birthday']): r['count'] 
     for r in counts
 }

 for person in Person.objects.all():
     count = counts_by_date[birthdate(person.birthday)]
     print "%d people share your birthday!" % count

这篇关于在Django ORM中加入两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！