The most effective way to build a list of the highest prices from a set of queries?

Question

On one page of my application, I try to display the most expensive car for each company. My models look something like this:

class Company(models.Model):
    id = models.IntegerField(primary_key=True)
    company = models.CharField(max_length=100)
    headcount = models.IntegerField(null=False)
    info = models.CharField(max_length=100)

class Car(models.Model):
    id = models.IntegerField(primary_key=True)
    company_unique = models.ForeignKey(Company)
    company = models.CharField(max_length=50)
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=9, decimal_places=2, default=0.00)

So, I want to create a list consisting of each of the company's most expensive automotive facilities.

I approached such a problem:

company_list = Company.objects.all()
most_expensive = []
for company in company_list:
    most_expensive.append(Car.objects.filter(company_unique=company.id).order_by("-price")[0])

However, this is apparently a very inefficient method. I can see with the Django Debug toolbar that this code makes too many mysql queries.

Can someone suggest a better way to create this list that could hit MySQL, maybe once or twice?

Answer 1

, , , .

1, . , - :

companies = Company.objects.annotate(max_price=Max('car__price'))
values = tuple((company.id, company.max_price) for company in companies)

expensive_cars = Car.objects.extra(where=['(company_unique_id, price) IN %s' % (values,)])

, - .extra - . , .

2, . Prefetch .

prefetch = Prefetch('cars', queryset=Car.objects.order_by('-price'), to_attr='cars_by_price')
companies = Company.objects.prefetch_related(prefetch)

most_expensive_cars = []
for company in companies:
    most_expensive_cars.append(list(company.cars_by_price.all())[0])

, , Cars, Companies . , list() : , , , , .

, Car.company, select_related, .

Answer 2

, , , , .

, Aggregation:

most_expensive = Car.objects.values('company_unique').annotate(Max('price'))

SQL, , : , :

from django.db import connection

cursor = connection.cursor()
cursor.execute("SELECT Max(price), company_unique FROM Car GROUP BY company_unique");
price_company = cursor.fetchall()

# This still does one query per car, only it fetches one item at a time.
most_expensive = [Cars.objects.get(price=pc[0],company_unique=pc[1]) 
                    for pc in price_company]

, raw:

most_expensive = Cars.objects.raw("""
   SELECT * FROM Cars 
     INNER JOIN 
       (SELECT Max(price) as price, company_unique FROM Car GROUP BY company_unique) m 
       ON m.price = Cars.price, m.company_unique = Cars.company_unique
""")

raw , , . (Oracle, , ).

, , SELECT Max(price) as price, company_unique FROM Car GROUP BY company_unique , - Django-native , .

Answer 3

, "priciest_car", , , , priciest_car. , , company.priciest_car . , sql . , , , . , "priciest_car" , , .