843

In Django model QuerySets, I see that there is a __gt and __lt for comparative values, but is there a __ne or != (not equals)? I want to filter out using a not equals. For example, for

Model:
    bool a;
    int x;

I want to do

results = Model.objects.exclude(a=True, x!=5)

The != is not correct syntax. I also tried __ne.

I ended up using:

results = Model.objects.exclude(a=True, x__lt=5).exclude(a=True, x__gt=5)
2
  • 87
    Would results = Model.objects.exclude(a=true).filter(x=5) have worked?
    – hughdbrown
    Jul 27, 2009 at 21:00
  • 5
    @hughdbrown. No. Your query excludes all a=true first and then applies the x=5 filter on the remaining. The intended query required only those with a=true and x!=5. The difference being that all those with a=true and x=5 are also filtered out. Feb 15, 2018 at 12:58

17 Answers 17

923

You can use Q objects for this. They can be negated with the ~ operator and combined much like normal Python expressions:

from myapp.models import Entry
from django.db.models import Q

Entry.objects.filter(~Q(id=3))

will return all entries except the one(s) with 3 as their ID:

[<Entry: Entry object>, <Entry: Entry object>, <Entry: Entry object>, ...]
4
  • 20
    Is there any reason to do Entry.objects.filter(~Q(id=3)) rather than Entry.objects.exclude(id=3)? Dec 14, 2020 at 11:46
  • 1
    I suppose its use is conditional on the scenario, but Q objects allow for more complex queries. For example, you could string together the ~Q query with other ones as well. docs.djangoproject.com/en/3.2/topics/db/queries/… Sep 4, 2021 at 5:42
  • 1
    @BobWhitelock: It's just a simple snippet for the question. In real world, much cases we have to use this. For example: EXCLUDE(A=1 and B__not=2); use extra .exclude is not right.
    – anhtran
    Sep 22, 2021 at 9:48
  • This makes sense... If you were to do a filtering in an annotation or aggregation, this would come in handy.
    – Chymdy
    Dec 3, 2021 at 8:51
735

Your query appears to have a double negative, you want to exclude all rows where x is not 5, so in other words you want to include all rows where x is 5. I believe this will do the trick:

results = Model.objects.filter(x=5).exclude(a=True)

To answer your specific question, there is no "not equal to" field lookup but that's probably because Django has both filter and exclude methods available so you can always just switch the logic around to get the desired result.

8
  • 2
    @d4nt: I may be wrong, but I think the query should be results = Model.objects.filter(a=true).exclude(x=5)
    – Taranjeet
    Sep 2, 2015 at 14:59
  • 1
    @Taranjeet: I think you misread the original query. d4nt's version is correct, because OP wanted to exclude(a=True) and negate the exclusion of x=5 (i.e. include it).
    – Chuck
    Sep 16, 2015 at 20:55
  • 5
    I think this is wrong because an instance (x=4, a=false) would be wrongly excluded. Nov 17, 2015 at 14:14
  • 4
    @danigosa That doesn't seem right. I just tried this myself, and the order of exclude and filter calls didn't make any meaningful difference. The order of the conditions in the WHERE clause changes, but how does that matter? Apr 7, 2016 at 19:51
  • 4
    @danigosa order of exclude and filter doesn't matter.
    – EralpB
    Sep 5, 2017 at 7:29
151

the field=value syntax in queries is a shorthand for field__exact=value. That is to say that Django puts query operators on query fields in the identifiers. Django supports the following operators:

exact
iexact
contains
icontains
in
gt
gte
lt
lte
startswith
istartswith
endswith
iendswith
range

date
year
iso_year
month
day
week
week_day
iso_week_day
quarter
time
hour
minute
second

isnull
regex
iregex

I'm sure by combining these with the Q objects as Dave Vogt suggests and using filter() or exclude() as Jason Baker suggests you'll get exactly what you need for just about any possible query.

4
  • thanks this is awesome . i used some thing like this tg=Tag.objects.filter(user=request.user).exclude(name__regex=r'^(public|url)$') and it works.
    – suhailvs
    Sep 11, 2013 at 7:12
  • 1
    @suhail, please mind that not all databases support that regex syntax :)
    – Anoyz
    Jul 12, 2016 at 10:44
  • 3
    i in icontains, iexact and similar stands for "ignore case sensitivity". It is not for "inverse". May 6, 2017 at 14:59
  • It is worth noting that when you are using exclude() with multiple terms, you may want to compose the proposition with the OR operator, e.g. exclude(Q(field1__queryop1=value1) | Q(field2__queryop2=value2)) in order to exclude the results under both conditions.
    – clapas
    Aug 25, 2017 at 10:30
131

There are three options:

  1. Chain exclude and filter

    results = Model.objects.exclude(a=True).filter(x=5)
    
  2. Use Q() objects and the ~ operator

    from django.db.models import Q
    object_list = QuerySet.filter(~Q(a=True), x=5)
    
  3. Register a custom lookup function

    from django.db.models import Lookup
    from django.db.models import Field
    
    @Field.register_lookup
    class NotEqual(Lookup):
        lookup_name = 'ne'
    
        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 <> %s' % (lhs, rhs), params
    

    Which can the be used as usual:

    results = Model.objects.exclude(a=True, x__ne=5)
    
4
  • 1
    object_list = QuerySet.filter(~Q(a=True), x=5) : Remember to keep all the other conditions not containing Q after those containing Q. Jun 6, 2017 at 12:28
  • 1
    @MichaelHoffmann : A)you will then filter on a smaller set of data after exclusion using ~Q so is more efficient. B) probably the sequencing the other way around does not work .. dun know .. dun remember! Feb 28, 2019 at 8:48
  • 2
    wonder if there is a performance difference in 1 vs 2
    – Anupam
    Aug 12, 2020 at 23:17
  • 1
    NOTE: the exclude will add something to the WHERE clause, so it can be pretty efficient. See docs.djangoproject.com/en/3.2/ref/models/querysets/#exclude. @Anupam
    – dfrankow
    Jun 14, 2021 at 17:50
114

It's easy to create a custom lookup, there's an __ne lookup example in Django's official documentation.

You need to create the lookup itself first:

from django.db.models import Lookup

class NotEqual(Lookup):
    lookup_name = 'ne'

    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 <> %s' % (lhs, rhs), params

Then you need to register it:

from django.db.models import Field
Field.register_lookup(NotEqual)

And now you can use the __ne lookup in your queries like this:

results = Model.objects.exclude(a=True, x__ne=5)
0
50

While you can filter Models with =, __gt, __gte, __lt, __lte, you cannot use ne or !=. However, you can achieve better filtering using the Q object.

You can avoid chaining QuerySet.filter() and QuerySet.exclude(), and use this:

from django.db.models import Q
object_list = QuerySet.filter(~Q(field='not wanted'), field='wanted')
0
27

Pending design decision. Meanwhile, use exclude()

The Django issue tracker has the remarkable entry #5763, titled "Queryset doesn't have a "not equal" filter operator". It is remarkable because (as of April 2016) it was "opened 9 years ago" (in the Django stone age), "closed 4 years ago", and "last changed 5 months ago".

Read through the discussion, it is interesting. Basically, some people argue __ne should be added while others say exclude() is clearer and hence __ne should not be added.

(I agree with the former, because the latter argument is roughly equivalent to saying Python should not have != because it has == and not already...)

1
  • 1
    It's not a pending design decision, they decided to not do this 8 years ago.
    – Boris
    Nov 13, 2020 at 16:36
25

Using exclude and filter

results = Model.objects.filter(x=5).exclude(a=true)
1
16

You should use filter and exclude like this

results = Model.objects.exclude(a=true).filter(x=5)
1
  • 1
    How's this different from @d4nt's answer made 5 years before yours?
    – Boris
    Nov 12, 2020 at 7:10
8

Django-model-values (disclosure: author) provides an implementation of the NotEqual lookup, as in this answer. It also provides syntactic support for it:

from model_values import F
Model.objects.exclude(F.x != 5, a=True)
8

This will give your desired result.

from django.db.models import Q
results = Model.objects.exclude(Q(a=True) & ~Q(x=5))

for not equal you can use ~ on an equal query. obviously, Q can be used to reach the equal query.

1
7

The last bit of code will exclude all objects where x!=5 and a is True. Try this:

results = Model.objects.filter(a=False, x=5)

Remember, the = sign in the above line is assigning False to the parameter a and the number 5 to the parameter x. It's not checking for equality. Thus, there isn't really any way to use the != symbol in a query call.

3
  • 3
    That isn't 100% the same thing since there could also be Null values for those fields.
    – MikeN
    Jul 20, 2009 at 18:18
  • This returns in only those items that have a=False and x=5, but in the question an instance (a=false, x=4) would be included. Nov 17, 2015 at 14:16
  • 3
    results = Model.objects.filter(a__in=[False,None],x=5)
    – Jeremy
    Mar 3, 2016 at 20:48
7

What you are looking for are all objects that have either a=false or x=5. In Django, | serves as OR operator between querysets:

results = Model.objects.filter(a=false)|Model.objects.filter(x=5)
0
7

results = Model.objects.filter(a = True).exclude(x = 5)
Generetes this sql:
select * from tablex where a != 0 and x !=5
The sql depends on how your True/False field is represented, and the database engine. The django code is all you need though.

0
2

Watch out for lots of incorrect answers to this question!

Gerard's logic is correct, though it will return a list rather than a queryset (which might not matter).

If you need a queryset, use Q:

from django.db.models import Q
results = Model.objects.filter(Q(a=false) | Q(x=5))
1
  • "Gerard's [...] will return a list rather than a queryset" - that's not true. it returns a queryset. And your answer is the same as the accepted answer.
    – Boris
    Nov 12, 2020 at 6:57
1

If we need to exclude/negate based on the sub queryset we can use,

Conditional filter:

When a conditional expression returns a boolean value, it is possible to use it directly in filters. Here non_unique_account_type returns a boolean value. But, still, we can use it in the filter.

>>> non_unique_account_type = Client.objects.filter(
...     account_type=OuterRef('account_type'),
... ).exclude(pk=OuterRef('pk')).values('pk')
>>> Client.objects.filter(~Exists(non_unique_account_type))

In the SQL terms, it evaluates to:

SELECT * FROM client c0
WHERE NOT EXISTS (
  SELECT c1.id
  FROM client c1
  WHERE c1.account_type = c0.account_type AND NOT c1.id = c0.id
)
0

This should work

results = Model.objects.filter(x=5).exclude(a=True)

Not the answer you're looking for? Browse other questions tagged or ask your own question.