如何将所有相关数据分组，并在django中作为值dict返回

Question

我有三种型号：

class City(models.Model):
name = models.CharField(max_length=150)
class Person(models.Model):
name = models.CharField(max_length=150)
city = models.ForeignKey(
City, related_name="persons", on_delete=models.PROTECT)
class Fruit(models.Model):
quantity = models.IntegerField()
price = models.IntegerField()
person = models.ForeignKey(Person, related_name="fruits")
city = models.ForeignKey(City, related_name="fruits")

我想根据城市对所有数据进行分组，得到城市水果的总价、城市水果的总量以及个人水果的总量。

我想要我的结果像：

{'city': 'XYZ', 'total_fruits': 20, 'total_price': 123, 'persons': [{'name': 'foo', 'total_fruits': 5}, {'name': 'bar', 'total_fruits': 6}]}

我试过这样的东西：

queryset = City.objects.annotate(total_price = Sum('persons__fruits__price'), total_fruits= Count('persons__fruits'))
city_list = []
for city in queryset:
abc = {'name': city.name, 'total_fruits':company.total_fruits, 
'total_price':company.total_price}
persons = city.persons.annotate(total_fruits= Count('fruits')).values('name','total_fruits')
abc.update({'persons':persons })
city_list.append(abc)

这不是很有效。我想删除这个循环并尽量减少查询。提前谢谢。让我知道我哪里错了。

Answer 1

您可以将其简化为两个查询：

city_qs = City.objects.annotate(total_price = Sum('persons__fruits__price'), total_fruits=Count('persons__fruits')).prefetch_related('persons')
person_qs = Person.objects.annotate(total_fruits=Count('fruits')).values('name','total_fruits')
city_list = []
for city in city_qs:
for person_id in city.persons:
person_data = person_qs[person_id]
...
city_list.append(abc)