pythonsqldjangodjango-modelsdjango-orm

How to make group by in django ORM


I've an Order model as below:

class Order(models.Model):
    bill = models.ForeignKey(Bill, on_delete=models.PROTECT, null=True, blank=True)
    address_from = models.ForeignKey(Address, on_delete=models.PROTECT)
    address_to = models.ForeignKey(Address, on_delete=models.PROTECT)

How can I group a queryset from it and iterate in each group like the following:

bill = models.Bill.objects.get(pk=1)
groups = models.Order.objects.all().group_by('address_from', 'address_to')

for group in groups:
    group.update(bill = bill)

The point is to assign a specific Bill instance for each group of the orders that have the same address from and address to.


Solution

  • I've solved this problem by combining the address from & address to primary keys into annotated unique key - uk - field and filter each uk.

    from django.db.models import CharField, Value
    from django.db.models.functions import Concat
    
    orders = models.Order.objects.annotate(
        addresses_uk=Concat('address_from__pk', Value('-'), 'address_to__pk',
            output_field=CharField())
        )
    
    for uk in set(orders.values_list('addresses_uk', flat=True)):
        bill = models.Bill.objects.create(
            address_from=models.Address.objects.get(pk=int(uk.split("-")[0])),
            address_to=models.Address.objects.get(pk=int(uk.split("-")[1]))
        )
        orders.filter(addresses_uk=uk).update(bill=bill)