4

I have models:

  • Product
  • Store
  • ProductStore (additional table with foreign keys to Store and Product, also Boolean 'enabled', and stock(integer) )

The questions:

How can I filter Products which has Enabled=True for current store__id (from request)? Also, how can I add an additional field for every objects with stock at current store?

UPDATED:

class Product(models.Model): 
   pass 
class Store(models.Model):
   pass
class ProductStoreInfo(models.Model):
   enabled = models.BooleanField(default=True)
   product = models.ForeignKey(Product, related_name='stock_info', on_delete=models.CASCADE)
   store = models.ForeignKey(Store, related_name="stock", on_delete=models.CASCADE)
   stock = models.IntegerField(verbose_name=_('Stock'), blank=True, null=True, default=0, max_length=255)
   price = models.FloatField(verbose_name=_('Price'), blank=True, null=True, max_length=255)
Taras
  • 447
  • 2
  • 7
  • 18

1 Answers1

3

You can filter this with:

Product.objects.filter(
    stock_info__enabled=True
    stock_info__store_id=my_store_id
)

This will thus return a QuerySet that contains only Products for which there is a related ProductStoreInfo for which enabled is True and the store_id is the my_store_id (to be replaced with an expression that results in such id).

We can also annotate our queryset with the stock data, like:

from django.db.models import F, Min

Product.objects.filter(
    stock_info__enabled=True
    stock_info__store_id=my_store_id
).annotate(
    stock=Min(F('store_info__stock'))
)

Here the Product objects from the queryset will have an extra attribute .stock that contains the stock column of the relevant ProductStore object (that satisfies the filtering).

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • AttributeError: 'F' object has no attribute 'lookup' (Django version 1.7.11) – Taras Oct 24 '18 at 03:08
  • @petriichuk: you should really upgrade Django, Django-1.7.11 is not supported anymore since december, 2015 (!) https://www.djangoproject.com/download/ and at that moment the ORM was indeed not "powerful enough" to do this. – Willem Van Onsem Oct 24 '18 at 05:26
  • upgraded, but does not filter... just return zero objects. Just to clarify, there are row for every store-product relation – Taras Oct 24 '18 at 06:05
  • getting error at annotate stage...the problem is that there are many related object...i need to get one by two parameters (product, store).. – Taras Oct 27 '18 at 21:10
  • You should get the one of the given `store_id`, if there are multiple `stock_info`s for that given `Product` and `Store`, then you get all of these in *separate* `Product` objects. – Willem Van Onsem Oct 27 '18 at 21:12
  • getting an error: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vw_dev_stock.products_productstoreinfo.stock' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") – Taras Oct 27 '18 at 21:18
  • @petriichuk: what if you add `Min(..)` over it? – Willem Van Onsem Oct 27 '18 at 21:20
  • Min, Sum etc works...but I need just annotate the data (stock field) from ProductStoreInfo object which relates to current product and store_id Im getting from request... There are many store_info objects related to every Product.. but I need exactly one, with store_id, which I get from request – Taras Oct 28 '18 at 13:34
  • @petriichuk: yes, this should do. The problem is that SQL does not allow to mention fields, since it assumes there can be multiple. We thus need an aggregate, and `MIN(..)` will give the lexicographically minimum of all `stock_info` for *that* product, and *that* stock. Here there is one, but the SQL "concept" layer does not know that. – Willem Van Onsem Oct 28 '18 at 13:36