StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POdjango left join with null
text
Body
copied!<p>The model:</p> <pre><code>class Product(models.Model): name = models.CharField(max_length = 128) def __unicode__(self): return self.name class Receipt(models.Model): name = models.CharField(max_length=128) components = models.ManyToManyField(Product, through='ReceiptComponent') class Admin: pass def __unicode__(self): return self.name class ReceiptComponent(models.Model): product = models.ForeignKey(Product) receipt = models.ForeignKey(Receipt) quantity = models.FloatField(max_length=9) unit = models.ForeignKey(Unit) def __unicode__(self): return unicode(self.quantity!=0 and self.quantity or '') + ' ' + unicode(self.unit) + ' ' + self.product.genitive </code></pre> <p>The idea: there are a components on stock. I'd like to find out which recipes I can made with components which I have.</p> <p>It's not easy - but possible - I made a SQL view, which gets the solution. But I'm learning python and Django so I'd like to make it Django-style ;D</p> <p>The concept of solution:</p> <ol> <li><p>get the set of recipes which has at last one component:</p> <p>list_of_available_components = ReceiptComponent.objects.filter(product__in=list_of_available_products).distinct() list_of_related_receipts = Receipt.objects.filter(receiptcomponent__in = list_of_available_components).distinct()</p></li> <li><p>get recipes (from list_of_related_receipts) which has not at last one component</p> <p>list_of_incomplete_recipes = (SELECT * FROM drinkbook_receiptcomponent LEFT JOIN drinkstore_stock_products USING(product_id) WHERE drinkstore_stock_products.stock_id IS NULL AND receipt_id IN (SELECT receipt_id FROM drinkbook_receiptcomponent JOIN drinkstore_stock_products USING(product_id)))</p></li> <li><p>get recipes (from list_of_related_receipts) which are not in "list_of_incomplete_recipes" </p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload