Maybe this helps a bit to find the problem. The method _preQuery() of Doctrine_Query_Abstract runs through the array returned by getQueryComponents() and make the following callbacks:

Foo
callback preDqlSelect
Bar
Foo
callback preDqlSelect
Foo
callback preDqlSelect
Bar

Roman, can you review the patch I have attached here? What are your thoughts on the issue? Basically the execution of the query can add parameters so if we embed the result of a subquery in to another query and it contains param placeholders, then those values need to be merged back to the main query.

In r5001 this issue was fixed. Thanks for the excellent ticket.

I'm seeing a similar issue as this ticket describes, but I think the difference is that I'm using a m2m relationship for the subquery.

Users can have many Groups, and Groups can have many Users.

I want to list all of the groups that a user DOES NOT belong to:

Doctrine_Query::create()
->from('Group g')
->where('g.id NOT IN (SELECT g2.id FROM Group g2 INNER JOIN g2.Users u WHERE u.id = ?', $user_id)
->execute();

Note, I had to use 'g2' in the subquery. If I use 'g' (like shown in the manual) I get a "duplicate alias 'g'" exception thrown.

With soft delete enabled on both the User and Group objects (but not on the refClass), Doctrine makes the following query:

SELECT g.id AS g__id, g.name AS g__name FROM groups g WHERE g.id NOT IN (SELECT g2.id AS g2__id FROM groups g2 INNER JOIN groups_users g4 ON g2.id = g4.group_id INNER JOIN users g3 ON g3.id = g4.user_id WHERE g3.id = ? AND g.deleted = ? AND g2.deleted = ? AND g3.deleted = ?) AND g.deleted = ? AND g2.deleted = ? AND g3.deleted = ?

Which causes the issue with there being too many tokens, and not enough bound parameters.

If I disable soft delete on both of the objects in question, I get the following query:

SELECT g.id AS g__id, g.name AS g__name FROM groups g WHERE g.id NOT IN (SELECT g2.id AS g2__id FROM groups g2 INNER JOIN groups_users g4 ON g2.id = g4.group_id INNER JOIN users g3 ON g3.id = c4.user_id WHERE g3.id = ?)

Which works fantastically. ;)

Using Doctrine revision 5067 (1.0 branch).

Can you create a different ticket for this issue? and a test case if you can.

Can you merge this into the 1.1 branch?

The changes that ended up fixing this issue are in 1.1 as well. They are not linked anywhere in the comments here. If any other issues exist please create a new ticket.

(In [5134]) [1.1] fixes #1567, #1480 Unable to merge into 1.0 branch due to r5001 dependency. Updated test case to fix wrong aliasing in subquery. Fixed wrong condition being added in subquery. It shoould be added in subquery only if it's the one that defined the object My patch also fixed the ticket #1567... no explainable reason, but it worked.