Opened 16 years ago

Closed 12 years ago

Last modified 22 months ago

#5763 closed New feature (wontfix)

Queryset doesn't have a "not equal" filter operator

Reported by: jdetaeye Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: qs-rf
Cc: German M. Bravo, carsten.fuchs@…, unai@… Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

Querysets can be filter for 'gt', 'gte', 'lt', 'lte', 'exact'.
The ability to filter for inequality / 'ne' is not in the list.

Using 'exclude' it is possible to negate a filter, but it's not as clear and clean as a direct filter. Including an inequality operator isn't hard and difficult, I believe.

In case this feature is accepted to be included in Django, feel free to assign it to me: I'm willing to take on creating the patch, its documentation and testing.

Attachments (3)

#ne_notin.diff (10.7 KB ) - added by German M. Bravo 12 years ago.
This adds ne and notin filters
#5763-ne_notin-1.4.diff (10.4 KB ) - added by German M. Bravo 12 years ago.
django 1.4 compatible patch
django_5763_plus_contains.diff (10.4 KB ) - added by PhiR_42 10 years ago.
patch updated for 1.5, plus handles negative contains and icontains

Download all attachments as: .zip

Change History (30)

comment:1 by Chris Beaven, 16 years ago

Triage Stage: UnreviewedDesign decision needed

comment:2 by jdetaeye, 16 years ago

Along the same lines, an 'isnotnull' operator could also come in handy...

comment:3 by durdinator, 16 years ago

@jdetaeye: Doesn't field__isnull=False fulfil that role?

comment:4 by jdetaeye, 16 years ago

@durdinator: it sure does. I was too quick with my additional comment...

comment:5 by Dan Watson, 16 years ago

Along the same lines, if this enhancement is accepted (and I hope it is), there should probably be a "is distinct from" operator to do null-safe comparisons.

comment:6 by James Bennett, 16 years ago

Resolution: wontfix
Status: newclosed

This is pretty much what exclude is for; there used to be an ne lookup type pre-magic-removal, but then exclude was introduced and covered both the original use case for ne plus many others. In the spirit of not needlessly offering multiple ways to do the same thing, I'm going to wontfix this.

comment:7 by Greg Brown, 15 years ago

Resolution: wontfix
Status: closedreopened

Unfortunately, while exclude is supposed to do everything a "not equal" operator would, it doesn't in practice - see http://code.djangoproject.com/ticket/5763 for details. This is technically a problem with exclude(), but adding a "not equal" operator would negate the problem.

comment:8 by Greg Brown, 15 years ago

Resolution: wontfix
Status: reopenedclosed

Sorry, I've just realised I can negate my problem in http://code.djangoproject.com/ticket/5763 using isnull instead, so ignore the above comment.

comment:9 by Jacques Mattheij <j@…>, 15 years ago

In case somebody else runs in to this, non-emtpy text fields evaluate to > a blank field.

So you can do xxx_gt= to exclude rows with blank fields.

comment:10 by Adrian Holovaty, 13 years ago

Resolution: wontfix
Status: closedreopened

comment:11 by anonymous, 13 years ago

I would love to see this. Case in point:

        return self.filter(
            group__sites=Site.objects.get_current(),
            user=user
        ).exclude(
            group__status=GROUP_REMOVED,
            status=MEMBER_STATUS_BANNED,
            subscription=SUBSCRIPTION_NEVER
        )

Generates:

   SELECT ... 
   INNER JOIN ...
   WHERE (
      "groups_group_sites"."site_id" = 1  AND 
      "groups_membership"."user_id" = 17  AND NOT (
         "groups_membership"."status" = 99  AND 
         "groups_group"."status" = 3  AND (
            "groups_membership"."subscription" = 0  AND 
            "groups_membership"."subscription" IS NOT NULL
         )
      )
   ) ORDER BY "groups_membership"."status" DESC

Would like to see instead:

"groups_group_sites"."site_id" = 1  AND 
"groups_membership"."user_id" = 17  AND 
"groups_membership"."status" != 99  AND 
"groups_group"."status" != 3  AND 
"groups_membership"."subscription" != 0  AND 
"groups_membership"."subscription" IS NOT NULL

comment:12 by Gabriel Hurley, 13 years ago

Severity: Normal
Type: New feature

comment:13 by Carl Meyer, 13 years ago

Easy pickings: unset
Resolution: wontfix
Status: reopenedclosed
UI/UX: unset

Based on discussion with Alex and Andrew at the sprint, closing this wontfix on the basis that it doesn't make sense to have __ne without any other negated queries, when we already have .exclude() to express negatives.

Alex promises that he really will fix the SQL generation to not be stupid with .exclude().

comment:14 by asmoore82, 12 years ago

Easy pickings: set
Resolution: wontfix
Status: closedreopened

Apologies for the re-open - but I wasn't sure what would be worse, a re-open or a new ticket for the same old discussion...

I'm going to have to take the position that the absence of a __ne operator still
represents a functional hole in the querying API, even with consideration of exclude()

it doesn't make sense to have __ne without any other negated queries,

Ah but there are indeed other negated queries.

filter(__gte) and exclude(__lt) are almost equivalent.

Similarly, you can almost approximate __ne with a hokey Q(__lt) | Q(__gt) contraption.

But the real issue lies within that "almost" -- this is what could be cause for a separate ticket, but a quick and dirty __ne would head the issue off altogether ;). Chaining multiple filter() and exclude() calls on multi-valued relationships yields not-so-surprising but nonetheless undesired results. To quote from the Django docs:

Django has a consistent way of processing filter() and exclude() calls. Everything inside a single filter() call is applied simultaneously to filter out items matching all those requirements. Successive filter() calls further restrict the set of objects, but for multi-valued relations, they apply to any object linked to the primary model, not necessarily those objects that were selected by an earlier filter() call.

This consistency is a good thing but it combines with the lack of __ne to form a problem.

Say you have blogs with entries with tags and author_counts

If you want to get all entries that are tagged 'django' with more than 2 co-authors:

Entry.objects.filter(tag__name='django', author_count__gt=2)

It is similarly easy to get blogs with entries with the above criteria:

Blog.objects.filter(entry__tag__name='django', entry__author_count__gt=2)

But what if you want entries tagged 'django' that are not co-authored by 2:

Entry.objects.filter(tag__name='django').exclude(author_count=2)

But if you want the blogs with those entries, it can't easily be done:

Blog.objects.filter(entry__tag__name='django').exclude(entry__author_count=2)

is not the equivalent of the imaginary query:

Blog.objects.filter(entry__tag__name='django', entry__author_count__ne=2)

which can currently be approximated with:

Blog.objects.filter(Q(entry__author_count__lt=2) | Q(entry__author_count__gt=2), entry__tag__name='django')

You can also get the desired results with a .extra() call but let's not go there :P.

This brings us to the most surprising aspect, using the negation operator ~ on Q() objects that select on multi-valued relations is technically possible but can yield the undesired results on the unsuspecting, which actually runs sort of contrary to the Documentation quote above.

Bad Surprise!!:

Blog.objects.filter(~Q(entry__author_count=2), entry__tag__name='django')

^The more I look at this, the more I think it is cause for a ticket in its own right. The fix for this would be to smarten up the Q() objects so that a NOT operator on __gt becomes __lte, a NOT on __lt becomes __gte, and so on. But you will ultimately be missing the __ne and other NOT primitives to fall back on.

In other words, this ticket is a "could-go-either-way" blocker for ^that more important ticket. I'll do some research on that and make a ticket if it hasn't already been addressed.

Thanks to all who make Django awesome! I'm a database newbie and loving it!

~Adam sM

comment:15 by Chris Beaven, 12 years ago

Resolution: wontfix
Status: reopenedclosed

Thanks for your thoughts, Adam, but reopening a ticket closed by a core developer is a no-no (as mentioned in the contributing documentation).

Try bringing this up in the django-developers mailing list.

by German M. Bravo, 12 years ago

Attachment: #ne_notin.diff added

This adds ne and notin filters

comment:16 by German M. Bravo, 12 years ago

Cc: German M. Bravo added

by German M. Bravo, 12 years ago

Attachment: #5763-ne_notin-1.4.diff added

django 1.4 compatible patch

by PhiR_42, 10 years ago

patch updated for 1.5, plus handles negative contains and icontains

comment:17 by PhiR_42, 10 years ago

Just for the record, this is very important for a specific use case: when you want to add extra select columns from the joined tables. I have an ORM-based search form and using exclude will break in many cases because it uses more subqueries. Using filter(relatedfieldne = something) will work as expected while exclude(relatedfield = something) will generate a subquery and remove the possibility of selecting tables.

comment:18 by Marc Tamlyn, 10 years ago

It is worth noting that in 1.7 this can be written as a custom lookup if you need it. I think it might even be the example in the docs.

comment:19 by Anssi Kääriäinen, 10 years ago

Be warned - not using subqueries might actually produce incorrect results. Specifically, .exclude() guarantees that you get the complement of what you get with .filter(). There are cases where field__ne will not produce the complement of field__exact. This happens for example if the field has NULL values. Both __exact and __ne will miss such rows, as in SQL terms both 'val = NULL' and 'val != NULL' have unknown result.

As mjtamlyn noted a __ne lookup will be fairly straightforward to implement in 1.7. Check https://docs.djangoproject.com/en/dev/ref/models/custom-lookups/ for details.

comment:20 by PhiR_42, 10 years ago

Thanks for that clarification.
In my case it seems the patch mitigates #14645 though, so I will probably take my chances in production.

comment:21 by Amichai Schreiber, 8 years ago

Since this is an issue that keeps coming up for me, and I'm seeing it in discussions on the web as well as on this ticket, and its usefulness has been demonstrated by asmoore82 (and I can give more examples) --

is there an explanation why this shouldn't be implemented? Is there some consideration by which this ticket isn't a good idea?

comment:22 by Tim Graham, 8 years ago

Please first read through the ticket history as well as the mailing list threads linked from the the ticket. If you disagree with the conclusions that have been reached so far, you are welcome to argue your case on the DevelopersMailingList.

comment:23 by Carsten Fuchs, 8 years ago

Cc: carsten.fuchs@… added

comment:24 by Unai Zalakain, 8 years ago

Cc: unai@… added

comment:25 by cpbotha, 6 years ago

Is there a way to work around the absence of the ne operator when one wants to do a get_or_create() or an update_or_create() where one wants to select records with a non-blank text field value?

In other words, how would I do for example Blog.objects.update_or_create(text_contents__ne='', other_field_indicating_sort=42, defaults={yet_another_field: True}) ?

comment:26 by Simon Charette, 6 years ago

You should be able to use exclude for that.

Blog.objects.exclude(
    text_contents='',
).update_or_create(
    other_field_indicating_sort=42,
    defaults={yet_another_field: True},
)

comment:27 by dennisvang, 22 months ago

The "... spirit of not needlessly offering multiple ways to do the same thing ..." makes a lot of sense to me.

However, there's this question on StackOverflow:

How do I do a not equal in Django queryset filtering?

Current stats:

  • 555246 views
  • 859 upvotes
  • bookmarked 179 times

Just to be clear, this is a really high view count: top 0.02% (top 5500 out of 23 million questions, according to https://data.stackexchange.com/stackoverflow)

Apparently, the alternatives to an __ne lookup are not that obvious to many people.

Perhaps it's worth re-opening this issue and, for example, implementing the ne from the custom lookup example?

Note: See TracTickets for help on using tickets.
Back to Top