CODEWITHSUNDEEP
pythonsqldjangodjango-modelsdjango-orm
Ahmed Ashraf
Ahmed Ashraf

Reputation: 76

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.

Upvotes: 1

Views: 89

Answers (3)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477704

@Ahmed Ashraf answered the question. We can however retrieve and create data in bulk, which will boost performance a lot, with:

orders = {
    (order.address_from, order.adress_to): order
    for order in models.Order.objects.filter(bill=None)
}

bills = []
for (item1, item2), order in orders.items():
    bill = models.Bill(address_from_id=item1, address_to_id=item2)
    bills.append(bill)
    order.bill = bill

for bill in Bill.objects.bulk_create(bills):
  orders[bill.address_from_id, bill.address_to_id].bill = bill

Order.objects.bulk_update(orders.values(), fields=('bill',))

or for databases that don't return the primary keys for a .bulk_create(…) [Django-doc]:

from django.db.models import Q

orders = {
    (order.address_from, order.adress_to): order
    for order in models.Order.objects.filter(bill=None)
}

bills = []
records = []
for (item1, item2), order in orders.items():
    bill = models.Bill(address_from_id=item1, address_to_id=item2)
    bills.append(bill)
    order.bill = bill
    records.append(Q(address_from_id=item1, address_to_id=item2))

Bill.objects.bulk_create(bills)

bills = Bill.objects.filter(Q(records, _connector=Q.OR)).order_by('pk')
for bill in bills:
    orders[bill.address_from_id, bill.address_to_id].bill = bill

Order.objects.bulk_update(orders.values(), fields=('bill',))

This will work with three queries: one to fetch all orders without a bill, one to create all bills, and finally one to update all the Orders with the created Bills. In the last scenario, it will make four database queries.

Upvotes: 0

Ahmed Ashraf
Ahmed Ashraf

Reputation: 76

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)

Upvotes: 1

HbtVictor
HbtVictor

Reputation: 37

To do what you're looking for, there is no group_by as such, in your place I'd make a list with all the unique combinations of your 2 address fields, For each group, filter the corresponding commands and use update() to assign the specified instance of Bill

You can find more information in the documentation, particularly in the sections on values() and distinct() https://docs.djangoproject.com/en/5.1/ref/models/querysets/

I hope my answer helps solve your problem

Upvotes: 0

Related Questions