[Django]-Fetching single child row based on a max value using Django ORM

by

10👍

✅

Django 1.1 (currently beta) adds aggregation support to the database API. Your query can be done like this:

from django.db.models import Max, F

Contract.objects.annotate(max_price=Max('market__contract__current_price')).filter(current_price=F('max_price')).select_related()

This generates the following SQL query:

SELECT contract.id, contract.name, contract.market_id, contract.current_price, MAX(T3.current_price) AS max_price, market.id, market.name
FROM contract LEFT OUTER JOIN market ON (contract.market_id = market.id) LEFT OUTER JOIN contract T3 ON (market.id = T3.market_id)
GROUP BY contract.id, contract.name, contract.market_id, contract.current_price, market.id, market.name
HAVING contract.current_price =  MAX(T3.current_price)

The API uses an extra join instead of a subquery (like your query does). It is difficult to tell which query is faster, especially without knowing the database system. I suggest that you do some benchmarks and decide.

👤Ayman Hourieh

Leave a comment