Django ORM annotate with subquery

Question

I've got following models:

class Store(models.Model):
   name = models.CharField()

class Goods(models.Model):
   store = models.ForeigKey(Store, on_delete=models.CASCADE)
   total_cost = models.DecimalField()
   different values ...

So, I filtered all the goods according to the parameters, and now my goal is to get one good from each store, which has the lowest price among other goods from this store

stores = Store.objects.all() - all stores
goods = Good.objects.filter(..) - filter goods

goods.annotate(min_price=Subquery(Min(stores.values('goods__total_cost'))))                                                                   

I tried something like this, but I've got an error:

AttributeError: 'Min' object has no attribute 'query'

Answer 1

I think in you context, you need a Group By feature than a Django annotation,
from this SO answer,

>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1

q is the queryset of books, but each book has been annotated with the number of authors. That is, if you annotate your goods queryset, they won't give you back some sorted/filtered set of objects. It will annotate with new field min_price only.

So I would suggest you to do a Group By operation as follow

from django.db.models import Min

result = Goods.objects.values('store').annotate(min_val=Min('total_cost'))

Example

In [2]: from django.db.models import Min

In [3]: Goods.objects.values('store').annotate(min_val=Min('total_cost'))
Out[3]: <QuerySet [{'store': 1, 'min_val': 1}, {'store': 2, 'min_val': 2}]>

In [6]: Goods.objects.annotate(min_val=Min('total_cost'))
Out[6]: <QuerySet [<Goods: Goods object>, <Goods: Goods object>, <Goods: Goods object>, <Goods: Goods object>, <Goods: Goods object>]>

In [7]: Goods.objects.annotate(min_val=Min('total_cost'))[0].__dict__
Out[7]:
{'_state': <django.db.models.base.ModelState at 0x7f5b60168ef0>,
 'id': 1,
 'min_val': 1,
 'store_id': 1,
 'total_cost': 1}

In [8]: Goods.objects.annotate(min_val=Min('total_cost'))[1].__dict__
Out[8]:
{'_state': <django.db.models.base.ModelState at 0x7f5b6016af98>,
 'id': 2,
 'min_val': 123,
 'store_id': 1,
 'total_cost': 123}

UPDATE-1
I think, this is not a good idea, may some optimization issues occur, but you can try if you want

from django.db.models import Min

store_list = Store.objects.values_list('id', flat=True)  # list of id's od Store instance
result_queryset = []
for store_id in store_list:
    min_value = Goods.objects.filter(store_id=store_id).aggregate(min_value=Min('total_cost'))
    result_queryset = result_queryset|Goods.objects.filter(store_id=store_id, total_cost=min_value)

UPDATE-2

I think my Update-1 section has very large amount of performance issues, So I found one possible answer to your question, which is ,

goods_queryset = Goods.objects.filter(**you_possible_filters)
result = goods_queryset.filter(store_id__in=[good['store'] for good in Goods.objects.values('store').annotate(min_val=Min('total_cost'))])