0

I have 3 models:

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")

I want to group all the data based on city and get total price of fruits city wise and total quantity citywise as well as person wise.

I want my result something like:

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

I have tried something like:

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)

Which is not much efficient. I want to remove this loop and minimize the queries. Thanks in advance. Let me know where I am wrong.

Alok Ramteke
  • 123
  • 4
  • 11

1 Answers1

0

You can reduce it to two queries:

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)
Horatiu Jeflea
  • 7,256
  • 6
  • 38
  • 67