QuerySet.__and__ does not work for joined queries

Patch for or tests to find this bug

I also confirm the bug. It is quite annoying.

This is a hard bug! If you use UNION, then you can't ORDER BY correctly, and I can't see that UNION would fix it actually. One possibility is converting the joins to joins inside sub selects (the sub selects being part of the WHERE clause), and then you should be able to combine them. But, 1) the current code is a long way from making this easy, 2) you would want to avoid sub selects where they aren't needed, making it even harder, 3) I'm not sure which backends support sub selects.

Hmmm.

UNION still allows to specify an ordering on the result. At least for postgresql and mysql:

• http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-UNION
• http://dev.mysql.com/doc/refman/4.1/en/union.html

mysql requires that you put both UNION subselects into parenthesis to make the ordering work. And you need mysql 4.0 or later, before 4.0 UNION is not supported at all.

(This is only from the docs)

sqlite docs don't make a statement about it.

• http://www.sqlite.org/lang_select.html

How about this approach:

• check whether both sides contain a join to the same table
• in this case, use a UNION

regarding the question, how UNION would help:

(select reporter.* from reporter, article where reporter.id=article.reporter and article.id=:n1)
UNION
(select reporter.* from reporter, article where reporter.id=article.reporter and article.id=:n2)

A different approach were to alias the tables to get an SQL expression like this:

select reporter.* from reporter, article a1, article a2 
where reporter.id = a1.reporter
and a1.id=:n1
and reporter.id = a2.reporter
and a2.id=:n2

The second approach looks somehow cleaner to me, since that's how I'd usually do it by hand. On the other side, it's probably harder to implement.

What's the real difference between both approaches, anyway?

Is this related to #2080?

I've marked #2080 as a duplicate. This is a hard bug indeed.

-> Accepted, based on Adrian's statement.

Please look at #3691 for a patch to Q objects that allows this. Although, it's not as pretty as a queryset refactor would be...

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658