django left join with null

Question

The model:

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

The idea: there are a components on stock. I'd like to find out which recipes I can made with components which I have.

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

The concept of solution:

1. get the set of recipes which has at last one component:

   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()

2. get recipes (from list_of_related_receipts) which has not at last one component

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

3. get recipes (from list_of_related_receipts) which are not in "list_of_incomplete_recipes"

Answer 1

Heh. How stupid am I. This could be solved much easier. I don't have to find receipes which have at least one component. I can (the same way!) find recipes which i can't do because there is at least one component which i DON'T have.

list_of_unavailable_components = ReceiptComponent.objects.exclude(product__in=list_of_available_products).distinct()

And now.

list_of_available_receipts = Receipt.objects.exclude(receiptcomponent__in = list_of_unavailable_components).distinct()

Simple and clean. Thank you for cooperation ;D