Django ORM DB Query Optimization

Question

I have a Query that consists of two DB hits.
which the first contains 'aggregate' - so it perform immediately.
I want to know if I can reduce it to only one DB hit.

The query is:
"Find customers that their id's numbers are bigger than all of texas's customers id's."

The two querysets are:

highest_tx_id = Customer.objects.filter(state='TX').aggregate(Max('id'))
res = Customer.objects.filter(id__gt=highest_tx_id['id_max'])

Adding an example:

>>> reset_queries()
>>> highest_tx_id = Customer.objects.filter(state='TX').aggregate(Max('id'))
>>> res = Customer.objects.filter(id__gt=highest_tx_id['id__max'])
>>> connection.queries
[{'sql': 'SELECT MAX("customer"."id") AS "id__max" FROM "customer" WHERE "customer"."state" = \'TX\'', 'time': '0.001'}]
>>> res
<QuerySet [<Customer: Customer object (550)>, <Customer: Customer object (551)>, <Customer: Customer object (552)>, <Customer: Customer object (553)>, <Customer: Customer object (555)>, <Customer: Customer object (661)>, <Customer: Customer object (665)>]>
>>> connection.queries
[{'sql': 'SELECT MAX("customer"."id") AS "id__max" FROM "customer" WHERE "customer"."state" = \'TX\'', 'time': '0.001'}, {'sql': 'SELECT "customer"."id", "customer"."fname", "customer"."lname", "customer"."address", "customer"."city", "customer"."state", "customer"."zip_code", "customer"."phone", "customer"."company_name" FROM "customer" WHERE "customer"."id" > 444 LIMIT 21', 'time': '0.001'}]      
>>>

Any way to do these type of queries using 1 DB hit ?

Brian Destura · Accepted Answer · 2021-11-22 23:41:11Z

1

Building up on @TaipanRex's answer, you can do something like this:

from django.db.models import Subquery

highest_tx_id = Customer.objects.filter(state='TX').order_by('-id').values('id')[:1]
res = Customer.objects.filter(id__gt=Subquery(highest_tx_id))

answered Nov 22, 2021 at 23:41

Brian Destura

12.1k3 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TaipanRex · Accepted Answer · 2021-11-20 14:36:24Z

1

You can try using Subquery.

from django.db.models import Subquery
highest_tx_id = Customer.objects.filter(state='TX').aggregate(Max('id'))['id__max']
res = Customer.objects.filter(id__gt=Subquery(highest_tx_id))

answered Nov 20, 2021 at 14:36

TaipanRex

8301 gold badge6 silver badges11 bronze badges

1 Comment

LiorA Over a year ago

Tried something like that. The first query hits the DB (I think because the aggregate). The second query yields an AttributeError: 'int' object has no attribute 'query'..

Collectives™ on Stack Overflow

Django ORM DB Query Optimization

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related