Django Not in for Related Fields

Question

How would I do a not in filter in Django when I have related fields?

class Publisher(Model):
  pass

class Author(Model):
  name = models.CharField(...)
  publisher = models.ForeignKey(Publisher)

I can write:

Publisher.objects.filter(author__name__in=XXX)

this does not work:

Publisher.objects.filter(author__name__not_in=XXX)

And while this Django equivalent of SQL not in claims you can use exclude, this is not correct:

Publisher.objects.exclude(author__name__in=XXX)

XXX is a list of names.

To clarify what I want to get: I would like to find all publishers that have an author NOT in that list. Note: these publishers may also have an author IN that list.

Let's say I have two publishers A, B and the following Authors for the publishers:

A: Alex, Bob, Greg
B: Alex, Greg

xxx is ['Alex', 'Greg']

I would like to find any Publisher that has an Author not in that list. In this case A should be returned since A has author Bob which is not in that list.

danielcorreia · Answer 1 · 2015-05-30T15:07:47.817

0

It looks Q objects would be a good fit for this query.

You can build up the query params first.

import operator

query = reduce(
    lambda x, y: operator.or_(Q(author__name=x), Q(author__name=y)),
    XXX
)
# query would be something like Q(author__name='Alex') | Q(author__name='Greg')

Publisher.objects.exclude(query)

Example using exact matches, if you're looking to search in a CharField you have to use contains or icontains. Just update Q object with Q(author__name__icontains=n).

edited May 30 '15 at 15:07

answered May 29 '15 at 20:50

danielcorreia

2,108
2
24
36

If xxx is the variable's name containing a string, then, `''` are not needed. – Gocht May 29 '15 at 20:59
Yes, you are right. Wasn't clear what XXX was before last update in the question, so I tried to make it clear by saying it had to be a string. – danielcorreia May 29 '15 at 21:01

score 0 · Answer 2 · answered May 29 '15 at 20:51

0

Check the exclude() function. It's the inverse of the filter() function, but does exactly what you're looking for.

Note that you can also chain functions together:

>>> Entry.objects.filter(
...     headline__startswith='What'
... ).exclude(
...     pub_date__gte=datetime.date.today()
... ).filter(
...     pub_date__gte=datetime(2005, 1, 30)
... )

answered May 29 '15 at 20:51

eykanal

26,437
19
82
113

I'm a pretty sure I do NOT want the inverse of filter in this case. I would like to find all publishers that have an author NOT in that list. Note: these publishers may also have an Author IN that list. – Alex Rothberg May 29 '15 at 20:53

score 0 · Answer 3 · answered May 29 '15 at 20:53

0

What is XXX, If it is a list you should be able to use exclude in your query.

Publisher.objects.exclude(author__name__in=XXX)  # Remember XXX must be a list

If it is a string you should use:

Publisher.objects.exclude(author__name__contains=XXX)

Or if case does not matter:

Publisher.objects.exclude(author__name__icontains=XXX)

answered May 29 '15 at 20:53

Gocht

9,924
3
42
81

Updated to show `XXX` is a list. Per http://stackoverflow.com/questions/30538768/django-not-in-for-related-fields#comment49151097_30538846 exclude is not what I want. – Alex Rothberg May 29 '15 at 20:55
Please be clear `exclude` does not work, or it is not returning what you expect? – Gocht May 29 '15 at 20:57
It is not returning what I want. I do not want to negate the entire filter, I want to negate the in. – Alex Rothberg May 29 '15 at 20:58
Can you show an example with data, XXX value, and what you expect? – Gocht May 29 '15 at 21:00
Updated with richer example. – Alex Rothberg May 29 '15 at 21:02
I think this is the [answer](http://stackoverflow.com/a/30539157/3945375). As he said it will take a cost, but is what you need. – Gocht May 29 '15 at 21:19

score 0 · Answer 4 · answered May 29 '15 at 21:12

The following can be used:

Publisher.author.filter(author__in=Author.objects.exclude(name__in=xxx))

There is a performance cost associated with the potentially huge subquery, especially on MySQL which doesn't handle subqueries all that well.

An alternative is to implement a NOT IN custom lookup, which has been possible since 1.7. It would look something like this:

class NotInLookup(Lookup):
    lookup_name = 'not_in'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s NOT IN (%s)' % (lhs, rhs), params

You'll have to investigate the documentation and possibly the source code of IN for the right way to write this lookup. There might be interest to add it to core, so if you get it working, feel free to bring it up on the mailing list at https://groups.google.com/forum/#!forum/django-developers.

Django Not in for Related Fields

4 Answers4