I have a MySQL table structured like this:
customer product
1 10
1 20
1 30
2 10
2 30
3 10
3 20
3 40
4 10
4 40
4 50
4 60
And I'd like to return all of the customers associated with a subset of a particular list of products. For example, if my query product IDs are (10, 20 30), I want customers 1 and 2 since their products are a subset of my list, but not 3 because 40 isn't in my list even though 10 and 20 are.
My query product IDs will typically be 3 - 20 entries long but there will be hundreds or thousands of different products possible.
I can do this in raw SQL:
SELECT * FROM customer c
WHERE NOT EXISTS (
SELECT 1 FROM cp_table j AND c.id=j.customer_id
AND j.product_id NOT IN (10,20,30)
);
But is there an easy way to construct such a query in Django? I'm using MySQL so I can't (apparently) use distinct on a particular column.
My Django models.py file is essentially:
class Customer(models.Model):
name = models.CharField(max_length=250)
products = models.ManyToManyField(Product, through='ProductList')
class Product(models.Model):
name = models.CharField(max_length=200)
class ProductList(models.Model):
customer = models.ForeignKey(Customer)
product = models.ForeignKey(Product)
EDIT: OK, I've found a way thanks to some of the comments and answers below:
subset_list = [10,20,30]
products_subset = Product.objects.filter(id__in=subset_list)
complement = Product.objects.exclude(id__in=subset_list)
c = Customer.objects.filter(products__in=products_subset)\
.exlude(products__in=complement).distinct()
However, I have my doubts about this method: it makes three queries (or perhaps doesn't, depending on how the QuerySet is evaluated) and the complement queryset is likely to be quite large. Is there a better way?
