#1480: SubQuery and SoftDelete doesn't work well together
------------------------+---------------------------------------------------
Reporter: enrico | Owner: romanb
Type: defect | Status: reopened
Priority: critical | Milestone: 1.0.3
Component: Listeners | Version: 1.0.2
Resolution: | Keywords:
Has_test: 0 | Mystatus: Pending Core Response
Has_patch: 0 |
------------------------+---------------------------------------------------
Changes (by Jay.Klehr):
* priority: blocker => critical
* status: closed => reopened
* version: 1.0.0 => 1.0.2
* resolution: fixed =>
Comment:
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
[http://www.doctrine-project.org/documentation/manual/1_0/?one-page#dql-
doctrine-query-language:conditional-expressions:subqueries 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).
--
Ticket URL: <http://trac.phpdoctrine.org/ticket/1480#comment:4>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
