Django Query: Group/Count orders by foreign key of foreign key?

user_9423974 0
Viewed 452

I have a hard time wrapping my head around how to achieve this. I have 3 fairly simple models: 

class Country(models.Model):
    name    = models.CharField(max_length=200, null=True, blank=True)
    def __str__(self):
        return self.name

class Seller(models.Model):
    name    = models.CharField(max_length=200, null=True, blank=True)
    country = models.ForeignKey(Country, null=True, blank=True, on_delete=models.SET_NULL)
    def __str__(self):
        return self.name

class Order(models.Model):
    order         = models.PositiveIntegerField(null=True, blank=True)
    seller        = models.ForeignKey(Seller, null=True, blank=True, on_delete=models.SET_NULL)
    order_date    = models.DateField(max_length=200, null=True, blank=True)
    def __str__(self):
        return self.name

"Orders" are simple product orders placed by "Sellers" are the businesses we supply and "Country" defines in which country these sellers are based. It could be the case that in country we have many sellers and that in the next country we only have 1.

A simplified description of the orders for better understanding of my question: 

Order N° 1 by seller A from Germany
Order N° 2 by seller B from Germany
Order N° 3 by seller C from France
Order N° 4 by seller D from Spain
Order N° 5 by seller F from France

What I would like to achieve is to group all orders from countries that exist in that Model. E.g.: (

Country  | Count of all orders by country
{Germany :      7 
Spain    :      12
France   :      5}

I have tried something like this: 

Order.objects.filter(customer__country__name__contains='Germany').annotate(count = Count('customer')).count()

It kinda works, but I would have to manually create as many queries as there are countries. Is there a possibility to loop over all countries and extract this information?

django-queryset django-models django python
2 Answers

You can simply use group by here(according to the models in the question):

from django.db.models import Count
Order.objects.values('seller__country__name').annotate(count=Count('seller')).values('seller__country__name','count')
user_26961650

You could simply wrap you country. Like this

for country in Country.objects.all():
    contains = {'customer__country__name__contains': country.name}
    Order.objects.filter(**contains).annotate(count = Count('customer')).count()
user_83717340
Related