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'

JPG · Accepted Answer · 2018-03-19 03:49:53Z

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'))])

edited Mar 19, 2018 at 3:49

answered Mar 18, 2018 at 18:21

JPG

89.4k24 gold badges164 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ernst Over a year ago

is it possible to get instances of Goods in queryset?

Ernst Over a year ago

update-2 works incorrectly I want to find all the products with just one complex query in the database, without calculating something in python, since now I'm looking for the right products in python and the response takes 0.6 seconds. I expect that the response time will grow in future..

Collectives™ on Stack Overflow

Django ORM annotate with subquery

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related