How to Query as Group by in Django

How to query as GROUP BY in django?

If you mean to do aggregation you can use the aggregation features of the ORM:

from django.db.models import Count
result = (Members.objects
    .values('designation')
    .annotate(dcount=Count('designation'))
    .order_by()
)

This results in a query similar to

SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation

and the output would be of the form

[{'designation': 'Salesman', 'dcount': 2}, 
 {'designation': 'Manager', 'dcount': 2}]

If you don't include the order_by(), you may get incorrect results if the default sorting is not what you expect.

If you want to include multiple fields in the results, just add them as arguments to values, for example:

    .values('designation', 'first_name', 'last_name')

References:

• Django documentation: values(), annotate(), and Count
• Django documentation: Aggregation, and in particular the section entitled Interaction with default ordering or order_by()

Group by query in Django ORM

I made some assumptions about your models, you didn't provide any:

class Ticket(models.Model):
    name = models.CharField(max_length=50)
    type = models.CharField(max_length=50)
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)


# Model names should NEVER end with "s"
class TicketDetail(models.Model):
    ticket = models.ForeignKey(Ticket, on_delete=models.CASCADE)
    message = models.CharField(max_length=50)
    created_time = models.DateTimeField(auto_now_add=True)

You have 2 options:

1. you can write it in pure sql, you lose the ability to filter

   sql = """
   SELECT ticket.id, ticket.name, ticket.type, ticket.user_id, detail.message
     FROM {ticket} ticket
     LEFT JOIN (
       SELECT detail.ticket_id, detail.message 
         FROM {detail} detail
        INNER JOIN (
           SELECT MAX(id) id, ticket_id 
             FROM {detail} 
            GROUP BY ticket_id
       ) detail_message ON detail.id = detail_message.id
   ) detail ON detail.ticket_id = ticket.id
   """.format(ticket=Ticket._meta.db_table, detail=TicketDetail._meta.db_table)
   tickets = Ticket.objects.raw(sql)
   
   for ticket in tickets:
       print(ticket.id, ticket.message)
   

2. Write it in the "django" way

   latest_messages = TicketDetail.objects.values('ticket_id').annotate(id=models.Max('id')).values('id')
   tickets = Ticket.objects.prefetch_related(models.Prefetch('ticketdetail_set', TicketDetail.objects.filter(id__in=latest_messages))).order_by('id')
   
   for ticket in tickets:
       print(ticket.id)
       # this iteration will only ever yield 1 result.. or nothing.
       for detail in ticket.ticketdetail_set.all():
           print(detail.message)
   

---

Here are the tests:

 # uses factoryboy and faker to fill in the data

 class UserFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = auth.models.User
        django_get_or_create = ('username',)

    first_name = fake.first_name()
    last_name = fake.last_name()
    email = factory.LazyAttribute(lambda obj: "{}.{}@gmail.com".format(obj.last_name, obj.first_name).lower())
    username = factory.Sequence(lambda n: 'user' + str(n))


class SimpleTestCase(TestCase):
    
    def setUp(self):
        ticket1 = Ticket.objects.create(user=UserFactory(), type='A', name='Number 1')
        TicketDetail.objects.create(ticket=ticket1, message='you wont see this')
        TicketDetail.objects.create(ticket=ticket1, message='you wont see this either')
        TicketDetail.objects.create(ticket=ticket1, message='YES!!')

        ticket2 = Ticket.objects.create(user=UserFactory(), type='B', name='Number 2')
        TicketDetail.objects.create(ticket=ticket2, message='you also wont see this')
        TicketDetail.objects.create(ticket=ticket2, message='you also wont see this either')
        TicketDetail.objects.create(ticket=ticket2, message='also YES!!')

    def test_flatten_pure_sql(self):
        sql = """
        SELECT ticket.id, ticket.name, ticket.type, ticket.user_id, detail.message
          FROM {ticket} ticket
          LEFT JOIN (
            SELECT detail.ticket_id, detail.message 
              FROM {detail} detail
             INNER JOIN (
                SELECT MAX(id) id, ticket_id 
                  FROM {detail} 
                 GROUP BY ticket_id
            ) detail_message ON detail.id = detail_message.id
        ) detail ON detail.ticket_id = ticket.id
        """.format(ticket=Ticket._meta.db_table, detail=TicketDetail._meta.db_table)
        self.assertEquals(['YES!!', 'also YES!!'], [x.message for x in Ticket.objects.raw(sql)])

    def test_orm_way(self):
        latest_messages = TicketDetail.objects.values('ticket_id').annotate(id=models.Max('id')).values('id')
        tickets = Ticket.objects.prefetch_related(models.Prefetch('ticketdetail_set', TicketDetail.objects.filter(id__in=latest_messages))).order_by('id')
        self.assertEquals(['Number 1', 'Number 2'], [x.name for x in tickets])
        self.assertEquals(['YES!!'], [x.message for x in tickets[0].ticketdetail_set.all()])
        self.assertEquals(['also YES!!'], [x.message for x in tickets[1].ticketdetail_set.all()])

How to execute a GROUP BY ... COUNT or SUM in Django ORM?

We can perform a GROUP BY ... COUNT or a GROUP BY ... SUM SQL equivalent queries on Django ORM, with the use of annotate(), values(), the django.db.models's Count and Sum methods respectfully and optionally the order_by() method:

• GROUP BY ... COUNT:

   from django.db.models import Count
  
   result = Books.objects.values('author')
                         .order_by('author')
                         .annotate(count=Count('author'))
  

  Now result contains a dictionary with two keys: author and count:

     author    | count
   ------------|-------
    OneAuthor  |   5
   OtherAuthor |   2
      ...      |  ...
  

• GROUP BY ... SUM:

   from django.db.models import Sum
  
    result = Books.objects.values('author')
                          .order_by('author')
                          .annotate(total_price=Sum('price'))
  

  Now result contains a dictionary with two columns: author and total_price:

     author    | total_price
   ------------|-------------
    OneAuthor  |    100.35
   OtherAuthor |     50.00
       ...     |      ...
  

UPDATE 13/04/2021

As @dgw points out in the comments, in the case that the model uses a meta option to order rows (ex. ordering), the order_by() clause is paramount for the success of the aggregation!

django queryset group by field

You must doing something like this:

from django.db.models import Count

MyModel.objects.values('Type').annotate(dcount=Count('Type'))

MyModel.objects.values('Type__id').annotate(dcount=Count('Type__id'))

you can also read this document can help you....
good luck!

Making a group by query in Django

You can achieve that by using Count along with .annotate():

>>> from django.db.models import Count
>>> skills = Skill.objects.annotate(form_count=Count("appform"))

You can get the total count of forms tied to each Skill instance:

>>> skills[0].form_count

You can use .values() to represent each instance as a dictionary containing only id and form_count:

>>> skills.values("id", "form_count")
<QuerySet [{'id': 1, 'form_count': 2}, {'id': 2, 'form_count': 3}, ...>

How to do SELECT COUNT(*) GROUP BY and ORDER BY in Django?

According to the documentation, you should use:

from django.db.models import Count
Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')

values() : specifies which columns are going to be used to "group by"

Django docs:

"When a values() clause is used to constrain the columns that are
returned in the result set, the method for evaluating annotations is
slightly different. Instead of returning an annotated result for each
result in the original QuerySet, the original results are grouped
according to the unique combinations of the fields specified in the
values() clause"

annotate() : specifies an operation over the grouped values

Django docs:

The second way to generate summary values is to generate an independent summary for each object in a QuerySet. For example, if you
are retrieving a list of books, you may want to know how many authors
contributed to each book. Each Book has a many-to-many relationship
with the Author; we want to summarize this relationship for each book
in the QuerySet.

Per-object summaries can be generated using the annotate() clause.
When an annotate() clause is specified, each object in the QuerySet
will be annotated with the specified values.

The order by clause is self explanatory.

To summarize: you group by, generating a queryset of authors, add the annotation (this will add an extra field to the returned values) and finally, you order them by this value

Refer to https://docs.djangoproject.com/en/dev/topics/db/aggregation/ for more insight

Good to note: if using Count, the value passed to Count does not affect the aggregation, just the name given to the final value. The aggregator groups by unique combinations of the values (as mentioned above), not by the value passed to Count. The following queries are the same:

Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')
Transaction.objects.all().values('actor').annotate(total=Count('id')).order_by('total')

How to get Sum with group by in Django query

You should add a .order_by(..) to force grouping, like:

productinventory_ds = ProductInventory.objects.filter(
    product_id=product_id
).values('product_id').annotate(
    instock_quantity_s=Sum('instock_quantity'),
    delivered_quantity_s=Sum('delivered_quantity')
).order_by('product_id')

Here however it looks like you want to extract the columns for a single product_id. In that case, .aggregate(..) makes more sense:

productinventory_ds = ProductInventory.objects.filter(
    product_id=product_id
).aggregate(
    instock_quantity_s=Sum('instock_quantity'),
    delivered_quantity_s=Sum('delivered_quantity')
)

This will return a dictionary that contains two items with as keys 'instock_quantity_s' and 'delivered_quantity_s'.

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

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

---

Related Topics