query - python django filter not null




How do I do a not equal in Django queryset filtering? (9)

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...)

In Django model QuerySets, I see that there is a __gt and __lt for comparitive values, but is there a __ne/!=/<> (not equals?)

I want to filter out using a not equals:

Example:

Model:
    bool a;
    int x;

I want

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

The != is not correct syntax. I tried __ne, <>.

I ended up using:

results = Model.objects.exclude(a=true, x__lt=5).exclude(a=true, x__gt=5)

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.

In Django 1.9/1.10 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.fields 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
    

    The register_lookup decorator was added in Django 1.8 and enables custom lookup as usual:

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

It's easy to create a custom lookup with Django 1.7. There's an __ne lookup example in Django 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, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return '%s <> %s' % (lhs, rhs), params

Then you need to register it:

from django.db.models.fields 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)

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.


Using exclude and filter

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

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

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

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

You should use filter and exclude like this

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

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
year
month
day
week_day
isnull
search
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.





django-queryset