CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
PepperoniPizza
PepperoniPizza

Reputation: 9102

Django Query Many relation "includes"

I have the following models:

class Contact(models.Model):
    email_list = models.ForeignKey(EmailList, related_name='contacts')
    customer = models.ForeignKey('Customer')

class EmailList(models.Model):
    customers = models.ManyToManyField('Customer',
        related_name='lists',
        through='Contact')

class Customer(models.Model):
    email = models.EmailField(max_length=255, unique=True)

Now I want to query all customers that belong to a certain set of EmailLists by the EmailList id. I thought I was gonna be able to query this with something:

all_customers.filter(lists__id__contains=[email_list_1.pk, email_list_2.pk])

that is weird looking I accept and returns an empty Queryset, but what surprised is that it works when querying for only 1 EmailList like:

all_customers.filter(lists__id__contains=email_list_1.pk)

So my next step was to query with and AND operator:

customers.filter(Q(lists__id__contains=el1.pk) & Q(lists__id__contains=el2.pk))

but that returns and empty QuerySet. I would like to find a way to be able to query lists__id__contains passing an iterable of ids, or at least find a way to concatenate subqueries

Upvotes: 1

Views: 120

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476594

The reason that this fails is because the __contains lookup [Django-doc] works for text, and if the right operand is not a string, it will convert it to a string. So you basically query for something that has as substring '[1, 4]' (with 1 and 4 ids, these can be different).

The customer is in any of the lists

You need to use the __in lookup [Django-doc] here:

all_customers.filter(lists__id__in=[email_list_1.pk, email_list_2.pk])

or you can use a "logical or":

customers.filter(Q(lists__id=el1.pk) | Q(lists__id=el2.pk))

The customer is in all of the lists

We can furthermore query for customers that are in all the lists by annotating and filtering:

ids = [email_list_1.pk, email_list_2.pk]

all_customers.filter(lists__id__in=ids).annotate(
    nlists=Count('lists')
).filter(
    nlists=len(set(ids))
)

Upvotes: 3

Related Questions