Django filtering queries with Q

Question

I have confusing problem in querying in django. I know its not a bug and it is something that I don't know.

So models are:

class Product(models.Model):
    name = models.CharField(max_length=255)

class AttributeValue(models.Model):
    value = models.CharField(max_length=255)
    product = models.ForeignKey(Product, related_name='attribute_values', on_delete=models.CASCADE)

I have one product object called T-shirt and it has two AttributeValue with ids 1, 2. And when I filter like this (1):

Product.objects.filter(Q(attribute_values__pk__in=[1, 2]))

Its returning my T-shirt object. But when I filter like this (2)

Product.objects.filter(Q(attribute_values__pk__in=[1]) & Q(attribute_values__pk__in=[2]))

there is no queryset found here. How is this happening? Previous codes are just examples and I have project that really need to do with this second filter (2).

Answer 1

This will never return any query results:

Product.objects.filter(Q(attribute_values__pk__in=[1]) & Q(attribute_values__pk__in=[2]))

# Equivalent to:
Product.objects.filter(attribute_values__pk=1).filter(attribute_values__pk=2)

Why? Because there is no object that simultaneously possesses both a primary key of 1 AND a primary key of 2.

You want the Q objects to be "OR"ed together instead of "AND"ed, so this is what you actually want:

Product.objects.filter(Q(attribute_values__pk__in=[1]) | Q(attribute_values__pk__in=[2]))

But why use Q objects when you can achieve the same without them? For instance:

Product.objects.filter(attribute_values__pk__in=[1, 2])