How to efficiently use Django query and Q to filter each object in a queryset and return 1 field value for each unique field in the queryset

Question

I have query that returns the following queryset:

results = <QuerySet [<Product: ItemA>, <Product: ItemA>, <Product: ItemB>, <Product: ItemB>, <Product: ItemB>, <Product: ItemC>, <Product: ItemC>]>

The __str__ representation of the model is name and each Product variation likely has a different value for the price field. After this query, I need to search my database for each Product in the queryset and return the lowest price for each unique name so like:

Lowest price for all in database where name is == to ItemA
Lowest price for all in database where name is == to ItemB
Lowest price for all in database where name is == to ItemC

I use the following block of code to accomplish this goal:

query_list = []
for each in results:
    if each.name not in query_list:   #Checks if the name of the object is not in in the query list
        query_list.append(each.name)   #Adds just the name of the objects so there is just one of each name in query_list

for each in query_list:
    priced = results.filter(name=each).order_by('price').first()  #Lowest price for each name in query_list

This feel very inefficient. Is there a way to make a similar computation without having to append the unique name of each Product to a separate list, and iterating over that list, and then making a query for each one? I feel like there is a way to use a type of complex lookup to accomplish my goals, maybe event use less Python, and make the db do more of the work, but the above is the best I've been able to figure out so far. There can be a lot of different hits in results so I need this block to be as efficient as possible

Answer 1

It is easy after reading docs Generating aggregates for each item in a QuerySet and also "Interaction with default ordering or order_by()".

from django.db.models import Min

prices = {x['name']: x['lowest_price']
          for x in results.values('name').annotate(lowest_price=Min('price').order_by()}

for product in results:
    if product.name in prices and product.price == prices[product.name]:
        priced = row  # output the row
        del prices[product.name]

That runs by two database queries.

An even more efficient solution with one query is probably possible with Window function, but it requires an advanced database backend and it can't work e.g. in tests with sqlite3.