I have a model, "Market" that has a one-to-many relation to another model, "Contract":
class Market(models.Model):
name = ...
...
class Contract(models.Model):
name= ...
market = models.ForeignKey(Market, ...)
current_price = ...
I'd like to fetch Market objects along with the contract with the maximum price of each. This is how I'd do it via raw SQL:
SELECT M.id as market_id, M.name as market_name, C.name as contract_name, C.price
as price from pm_core_market M INNER JOIN
(SELECT market_id, id, name, MAX(current_price) as price
FROM pm_core_contract GROUP BY market_id) AS C
ON M.id = C.market_id
Is there a way to implement this without using SQL? If there is, which one should be preferred in terms of performance?