Yet another __ne not equal discussion

[Adam Moore]

Greetings! Thanks to everyone for Django!

Long story short, I believe certain queries that are straightforward
and easy in plain SQL are not directly achievable in Django at all.
Obviously, Django is flexible enough that there is a workaround - but
a workaround shouldn't be necessary in a framework that is otherwise
so well rounded.

Let's start in the shallow end - It's been said that it doesn't make
sense to have a __ne "not equal" lookup when the other lookup types
don't have a negative variant. In light of the evidence below, I would
say that every lookup type should have a negative variant. But even
this would only go so far. For example, you wouldn't need a __ngt "not
greater than" lookup because it's the same thing as __lte "less than
or equal to."

But on to something of more substance - It's been said that exclude()
makes any negative lookup unnecessary. I'm relatively new to
databasing, but I believe that this is not true because of the way
multi-valued relationships are handled. To quote from the
documentation:

[quote]
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.
[/quote]

This means that by omitting any negative filter() lookups in favor of
successive exclude() calls, you are losing the ability to guarantee
that you are sticking to the correct instance of relationships.
Another possible solution to this is a new type of filter() and
exclude() variant that stick to old relationships or, in other words,
refrain from introducing "joins" if at all possible. This "other"
solution is a little insane, if I do say so myself; so the use of
negative lookups is simply more logical and truer to the underlying
SQL.

Examples: a little simplified and contrived, say you have blogs with
entries with tags and author_counts.

If you want to retrieve entries tagged 'django' with exactly 2 co-
authors:
Entry.objects.filter(tag__name='django', author_count=2)

If you want entries tagged 'django' that do not have exactly 2 co-
authors:
Entry.objects.filter(tag__name='django').exclude(author_count=2)

The exclude() solution works great for the above, but what if you want
blogs that contain those entries?

Blog.objects.filter(entry__tag__name='django', entry__author_count=2)
^This works as expected for the regular lookup, but not for the
negative lookup:
Blog.objects.filter(entry__tag__name='django').exclude(entry__author_count=2)
^This does not guarantee that the exact entries selected by the
filter() are the same ones being used for the exclude(). In other
words, I believe it joins in entries twice when it is needed only
once.

What you need to get this perfect is this imaginary lookup:
Blog.objects.filter(entry__tag__name='django',
entry__author_count__ne=2)

The workaround that gives the desired results is:
Blog.objects.filter(Q(entry__author_count__lt=2) |
Q(entry__author_count__gt=2), entry__tag__name='django')
^It's a blast from the past: the old <> "less than or greater than"
operator instead of != "not equal."

It's also worth noting that Q() objects permit the unary negation
operator, but this also yields the undesired results of the exclude()
call:
Blog.objects.filter(~Q(entry__author_count=2),
entry__tag__name='django')
^I believe this touches on a whole separate issue that needs a ticket.
The solution would be for negated Q() objects to flip-flop __gt, __lt,
__gte, __lte lookups to their logical opposites; but for the rest of
the lookup types, it would require some negative lookup primitives to
be implemented.

Of course, I must ask if I'm completely overlooking something here.

Adam sM

[Kääriäinen Anssi]

Quote:

"""
It's also worth noting that Q() objects permit the unary negation
operator, but this also yields the undesired results of the exclude()
call:
Blog.objects.filter(~Q(entry__author_count=2),
entry__tag__name='django')
"""

As far as I understand, this is exactly the query you want. The filters
are treated as single call, that is, they should target the same row,
not possibly different rows of the multijoin. It is another matter if it
actually works in current ORM implementation. IIRC something like
filter(~Q(pk=1)) and .exclude(Q(pk=1)) can produce different results.
But they _should_ produce the same result, and if they do not,
introducing negated lookups isn't the way to fix this - the correct
thing to do is fixing the ORM.

- Anssi

[Adam Moore]

On Oct 27, 6:26 am, Kääriäinen Anssi <anssi.kaariai...@thl.fi> wrote:

>
> Adam Moore wrote:
> > It's also worth noting that Q() objects permit the unary negation
> > operator, but this also yields the undesired results of the exclude()
> > call:
> > Blog.objects.filter(~Q(entry__author_count=2),
> > entry__tag__name='django')
>
> As far as I understand, this is exactly the query you want. The filters
> are treated as single call, that is, they should target the same row,
> not possibly different rows of the multijoin.
>

I'm back at work now, so I can show the real world examples:

I've got a school system database with schools, students, staff,
classes, etc. Students have a many-to-many relationship with schools
through attendance relations. Attendance relations themselves have
begin_date and end_date attributes, so the database can track
everywhere the students have been over time. As a rule of thumb, any
given student should only have one attendance relation at any given
time whose end_date is None. Staff have a very similar relationship
with schools but through an employment relation. Even if they
physically move between multiple schools, they can only be in payroll
at one.

All of this is with django fresh from svn this morning:

Here's the total number of Staff in the database:
>>> Staff.objects.all().count()
3019

^This is all staff that have ever been in our district. Here is
current, active staff only:
>>> Staff.objects.filter(employment__end_date=None).count()
2671

Make sure I'm not violating my own rule of thumb, staff should only
have exactly 1 active employment relation:
>>> Staff.objects.filter(employment__end_date=None).distinct().count()
2671

Now how many staff are at 1 specific school whose call letters are
'RAE':
>>> Staff.objects.filter(employment__end_date=None, employment__school__site_name='RAE').distinct().count()
94

How many active staff are not at that school? We have enough info to
know the correct answer:
>>> 2671 - 94
2577

But how do we pull this from the database. It can easily be done on
the employment table itself:
>>> Employment.objects.filter(end_date=None).exclude(school__site_name='RAE').count()
2577

It can't easily be done directly on staff:
>>> Staff.objects.filter(employment__end_date=None).exclude(employment__school__site_name='RAE').distinct().count()
2564

^This is excluding all staff who have ever been at that school.
Negating a Q() object has the same problem:
>>> Staff.objects.filter(~Q(employment__school__site_name='RAE'), employment__end_date=None).distinct().count()
2564

On Oct 27, 6:26 am, Kääriäinen Anssi wrote:
> But they _should_ produce the same result, and if they do not,
> introducing negated lookups isn't the way to fix this - the correct
> thing to do is fixing the ORM.

The exclude() option in its current form is unworkable on multi-valued
relations. I'd like to repeat that for emphasis: exclude() can *never*
obsolete direct negative lookups for multi-value relations.

Fixing up negated Q() objects while refusing direct negated lookups is
a little ridiculous. It's akin to saying you shouldn't support __lte
and __gte when ~Q(__gt) and ~Q(__lt) _should_ do the same thing. Even
better, who needs __gt when we've got (~Q(__lt) & ~Q(__exact)) :P.
Back on a serious note, it's worth repeating here that you can
approximate __ne with (Q(__lt) | Q(__gt)) - why make users take their
shoes off in order to put on their hats? Just give them __ne and that
cascades into the natural fix for ~Q(__exact).

Deciding which one of these has a more promising future is left up to
the experts:
>>> print Staff.objects.filter(
~Q(employment__school__site_name='RAE'),
employment__end_date=None,
).distinct().order_by().values('id').query
SELECT DISTINCT `data_staff`.`id`
FROM `data_staff`
LEFT OUTER JOIN `data_employment` ON (`data_staff`.`id` =
`data_employment`.`staff_id`)
WHERE (
NOT (
(`data_staff`.`id`
IN (
SELECT U1.`staff_id`
FROM `data_employment` U1
INNER JOIN `data_school` U2 ON (U1.`school_id` =
U2.`id`)
WHERE (
U2.`site_name` = RAE
AND U1.`staff_id` IS NOT NULL
)
)
AND `data_staff`.`id` IS NOT NULL
)
)
AND `data_employment`.`end_date` IS NULL
)

>>> print Staff.objects.filter(
employment__end_date=None,
employment__school__isnull=False, #force the join
).extra(where=['`data_school`.`site_name` != RAE']
).distinct().order_by().values('id').query
SELECT DISTINCT `data_staff`.`id`
FROM `data_staff`
LEFT OUTER JOIN `data_employment` ON (`data_staff`.`id` =
`data_employment`.`staff_id`)
INNER JOIN `data_school` ON (`data_employment`.`school_id` =
`data_school`.`id`)
WHERE (
`data_employment`.`end_date` IS NULL
AND `data_school`.`id` IS NOT NULL
AND `data_school`.`site_name` != RAE
)

~Adam sM

[Adam Moore]

Opps! ...

>     ).extra(where=['`data_school`.`site_name` != RAE']

should be
).extra(where=['`data_school`.`site_name` != %s'], params=['RAE']

which is case-in-point for why helping me avoid extra() is a good
thing!

~Adam sM

[Kääriäinen Anssi]

"""
The exclude() option in its current form is unworkable on multi-valued
relations. I'd like to repeat that for emphasis: exclude() can *never*
obsolete direct negative lookups for multi-value relations.
"""

I do see a problem here: the equality ~Q(a=1) <-> Q(a__lt=1)|Q(a__gt=1) is
not correct in m2m situations: the first is asking for rows where a must not
be 1, most of all, if there is no a, it is a match. The other is asking for rows
where there must be an A value, and it must be < 1 or > 1, most of all, if
there is no value at all, it is NOT a match. So:
filter(~Q(a=1), a__isnull=False) <-> Q(a__lt=1)|Q(a__gt=1).
The ORM is not able to handle the first version correctly. The interpretation
would be that there is at least one 'a' row, and its value is not 1.

I am strongly against the idea that Q(a__neq=1) would have different
interpretation of ~Q(a__eq=1). If they would have different interpretation, then
there would be basis for negative lookups. Although AFAICS you could still get
the same results using ~Q(a__eq=1, a__isnull=False) so the API would still work
without negative lookups.

I am basing the following discussion on the assumption that a__neq and ~a__eq
should be the same thing.

From ORM API standpoint, the claim that .exclude() can never obsolete
direct negative lookups is wrong as far as I understand the problem.
Reason:

.filter(Q(__neq=val)) <-> .filter(~Q(__exact=val)) <->
.exclude(Q(__exact=val))

Another way to see this is that Django should return same results for
the queries:
filter(~Q(employment__school__site_name='RAE'), employment__end_date=None)
and
filter(employment__school__site_name__neq='RAE', employment__end_date=None)

However, I do not think your issue is due to the above equality between
the two different ways of writing ~__eq problem, it is due to a bug in ORM
implementation. The second filter condition is not pushed down to the
subquery generated by the negated Q condition, and thus it generates
another join and potentially targets different rows. I think this is the main
problem in your situation. This is reinforce by this snippet from your query:
WHERE (
NOT
`data_staff`.`id`
IN ( subquery data_employment U1)
-- different data_employment reference from the subquery

AND `data_employment`.`end_date` IS NULL
)

That is, you have the data_employment table two times in the query, and
thus the filters are targeting potentially different rows. Note that this is a
bug. The conditions are in the same .filter() call, and thus they should target
the same row!

IMHO There are two underlying problems in the ORM related to this matter,
one is detecting when to use a subquery for the filter condition. The logic
for that is easily fooled. Another problem is that if you do a subquery,
other conditions that should go into the subquery WHERE are sometimes
not correctly pushed down to the subquery clause. This is similar to HAVING
clause pushdown problem.

I must say the m2m handling is very complicated, it took some time to see
the ~__eq=1 <-> __lt=1|__gt=1 difference for example... Thus, it is likely
that I am missing something else, too.

- Anssi

[Tom Evans]

On Fri, Nov 30, 2012 at 1:29 PM, Marek Brzóska <brzosk...@gmail.com> wrote:
> Has the matter been completely put away?
>
> I would like to bring it up again.
>
> I have Articles and Categories. An Article belongs to Categories:
>
> class Category(model):
> pass
> class Article(model):
> category = ManyToManyField(Category)
> status = CharField(max_length=10)
>
> Now I want all categories with articles that have status different than
> "archived".
>
> I cannot do this with django's ORM!
>
> Category.objects.filter(~Q(article_status="archived"))

What precisely is wrong with:

Category.objects.exclude(article_status='archived')

>
> will result in a query like this (simplified for readibility):
> SELECT * FROM
> category LEFT OUTER JOIN articlecategory ON category.id =
> articlecategory.category_id
> WHERE NOT
> "category"."id" IN (
> SELECT U1."category_id"
> FROM "articlecategory" U1 INNER JOIN "article" U2 ON (U1."article_id" =

> U2."id")
> WHERE
> (

> U2."status" = 'Archived'
> AND U1."category_id" IS NOT NULL
> )
> )
>
> The key problem here is that the ~Q() part negates whole big part of SQL
> query, and instead of getting all categories with at least one not archived
> article, I get all categories that have no archived articles! You must
> agree, that it is not very unlikely that someone wants to query for sth like
> this. I cannot use Q(__lt)|Q(__gt) becouse this is not integer. If status
> was enumerated, I could select all values that are different than
> "archived". But in this case I cannot do anything (except custom sql which
> seems like overkill to such simple need).
>
> Adding not equal operator (say __ne) would allow to do this really simply:
> Category.objects.filter(article_status__ne="archived")
>
> potentially resulting with query like this:
> SELECT * FROM
> category LEFT OUTER JOIN articlecategory ON category.id =
> articlecategory.category_id
> WHERE
> "category"."id" IN (
> SELECT U1."category_id"
> FROM "articlecategory" U1 INNER JOIN "article" U2 ON (U1."article_id" =

> U2."id")
> WHERE
> (

> U2."status" != 'Archived'
> AND U1."category_id" IS NOT NULL
> )
> )
>
> My question is why deny user such obvious usage of ORM?
>
> And btw, "not_in" would be really usefull too, argument the same, but I want
> to exclude three statuses ("archived", "deleted" and "draft").

Category.objects.exclude(article_status__in=[...])


Cheers

Tom

[Marek Brzóska]

2012/11/30 Tom Evans <teva...@googlemail.com>

On Fri, Nov 30, 2012 at 1:29 PM, Marek Brzóska <brzosk...@gmail.com> wrote:
> Has the matter been completely put away?
>
> I would like to bring it up again.
>
> I have Articles and Categories. An Article belongs to Categories:
>
> class Category(model):
>   pass
> class Article(model):
>   category = ManyToManyField(Category)
>   status = CharField(max_length=10)
>
> Now I want all categories with articles that have status different than
> "archived".
>
> I cannot do this with django's ORM!
>
> Category.objects.filter(~Q(article_status="archived"))

What precisely is wrong with:

Category.objects.exclude(article_status='archived')

It excludes all categories that have at least one archived article.

And I want categories that have at least one NOT archived article.

Example: I have one category: politics. I have two articles in this category: "Vote Obama!" which archived and "U.S wars" which is not archived.

Category.objects.exclude(article_status='archived')

will show no categories, while I want my only category to show, because there is one not archived article, "U.S. wars".

 

--
You received this message because you are subscribed to the Google Groups "Django developers" group.

To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

--
pozdrawiam,
Marek Brzóska

brzosk...@gmail.com

[Tom Evans]

That's a very different question:

Category.objects.exclude(article__status='archived').annotate(num_articles=Count('article')).filter(num_articles__gt=0)

Still answerable via the ORM.

Cheers

Tom

[Marek Brzóska]

>> What precisely is wrong with:
>>
>> Category.objects.exclude(article_status='archived')
>
> It excludes all categories that have at least one archived article.
>
> And I want categories that have at least one NOT archived article.
>
> Example: I have one category: politics. I have two articles in this
> category: "Vote Obama!" which archived and "U.S wars" which is not archived.
> Category.objects.exclude(article_status='archived')
> will show no categories, while I want my only category to show, because
> there is one not archived article, "U.S. wars".

That's a very different question:

Category.objects.exclude(article__status='archived').annotate(num_articles=Count('article')).filter(num_articles__gt=0)

Still answerable via the ORM.

 

No!  The moment you wrote exclude(article__status='archived') you excluded my category, because it has an article with status "archived". Please reread my original message, you seem to miss my point completely.

Marek Brzóska
brzosk...@gmail.com

[Alex Ogier]

To be fair, the query you describe is significantly more expensive than Marek's query.

Cheers

Tom

[Shai Berger]

On Friday 30 November 2012, Marek Brzóska wrote:
> Has the matter been completely put away?
>
> I would like to bring it up again.
>
> I have Articles and Categories. An Article belongs to Categories:
>
> class Category(model):
> pass
> class Article(model):
> category = ManyToManyField(Category)
> status = CharField(max_length=10)
>
> Now I want all categories with articles that have status different than
> "archived".
>
> I cannot do this with django's ORM!
>

live_articles = Article.objects.exclude(status="archived")
live_cats = Category.objects.filter(article__in=live_articles)

Doesn't this do the trick for you?

[Florian Apolloner]

On Friday, November 30, 2012 6:12:43 PM UTC+1, Shai Berger wrote:

live_articles = Article.objects.exclude(status="archived")
live_cats = Category.objects.filter(article__in=live_articles)

That works and it has the positive side-effect to kill any mysql server in a matter of seconds :)

[Anssi Kääriäinen]

On 30 marras, 15:29, Marek Brzóska <brzoskama...@gmail.com> wrote:
> Has the matter been completely put away?
>
> I would like to bring it up again.
>
> I have Articles and Categories. An Article belongs to Categories:
>
> class Category(model):
>   pass
> class Article(model):
>   category = ManyToManyField(Category)
>   status = CharField(max_length=10)
>
> Now I want all categories with articles that have status different than
> "archived".
>
> I cannot do this with django's ORM!
>

> Category.objects.filter(~Q(article_status="archived"))

>
> will result in a query like this (simplified for readibility):
> SELECT * FROM
> category LEFT OUTER JOIN articlecategory ON category.id =
> articlecategory.category_id
> WHERE NOT
> "category"."id" IN (
>   SELECT U1."category_id"
>   FROM "articlecategory" U1 INNER JOIN "article" U2 ON (U1."article_id" =
> U2."id")
>   WHERE
>   (
>       U2."status" = 'Archived'
>       AND U1."category_id" IS NOT NULL
>    )
> )
>
> The key problem here is that the ~Q() part negates whole big part of SQL
> query, and instead of getting all categories with at least one not archived
> article, I get all categories that have no archived articles! You must
> agree, that it is not very unlikely that someone wants to query for sth
> like this. I cannot use Q(__lt)|Q(__gt) becouse this is not integer. If
> status was enumerated, I could select all values that are different than
> "archived". But in this case I cannot do anything (except custom sql which
> seems like overkill to such simple need).

Hmmh, it would be nice if there was a way to get the wanted query
using the ORM.

Still, if ~Q() is different than __ne that is going to be somewhat
surprising for those users who do not know the ORM well.

For other solutions, one way is custom lookups. They might be reality
for 1.6, see: #16187. Using custom lookups you could have __not_in and
__ne if you so wish.

Another solution might be some sort of aliasing support:
qs.alias(articles1='article',
subquery=False).exclude(articles1__category='archived')
in general I would really like to have aliasing support, it could
allow for some fancy things, like aliasing raw SQL into the query...

All in all I think it is better to have some way to get the wanted
query out of the ORM than no way at all. Even if that way is a bit non-
obvious.

- Anssi

[Anssi Kääriäinen]

Is there any change in performance if EXISTS was used instead of IN?
Or is it just that MySQL and subqueries do not mix well?

- Anssi

[Marek Brzóska]

2012/11/30 Shai Berger <sh...@platonix.com>

It does. But I have to write two ORM queries instead of one, even though I mean one DB operation. So it is kinda "trick" as you named it. Not very bad, but still - could be better without so much cost, I believe.

--
Marek Brzóska

brzosk...@gmail.com