Details
Description
DQL doesn't allow to use Single Value Association with IN expression.
According to documentation:
InExpression ::= StateFieldPathExpression ["NOT"] "IN" "(" (InParameter {"," InParameter}* | Subselect) ")"
I think it should be like that:
InExpression ::= SingleValuedPathExpression ["NOT"] "IN" "(" (InParameter {"," InParameter}* | Subselect) ")"
Suggested solution:
Edit Parser::inExpression method to accept SingleValuedPathExpressions:
public function InExpression() { $inExpression = new AST\InExpression( /* Added this: */ $this->SingleValuedPathExpression() ); ... }
Test case:
$address1 = new \Entities\Address(); $address1->setStreet("Test Street #1"); $em->persist($address1); $userA = new \Entities\User(); $userA->setName('user A'); $userA->setTest('test A'); $userA->setAddress( $address1 ); $em->persist($userA); $userB = new \Entities\User(); $userB->setName('user B'); $userB->setTest('test B'); $userB->setAddress( $address1 ); $em->persist($userB); $em->flush(); $em->clear(); $dql = "SELECT u FROM Entities\User u WHERE u.address IN (SELECT a FROM Entities\Address a WHERE a.street LIKE 'Test Street%' ) "; $query = $em->createQuery($dql); $result = $query->getResult(); foreach ($result as $user) { echo "<pre>"; \Doctrine\Common\Util\Debug::dump($user); echo "</pre>"; $em->remove($user); } $em->flush();
Without this change It's impossible to select entities having specified association by a subquery with no additional join.
Now this could be done with this DQL:
SELECT u FROM Entities\User u WHERE u.address.id IN (SELECT a.id FROM Entities\Address a WHERE a.street LIKE 'Test Street%' )
But it requires to join addresses table for u.address.id field, which in fact is redundant and ineffective.
Tried to find similar issues or feature requests but no results. So I posted it here.
This would only work in one direction though ... in the other direction you need a join anyway and it will also not work with composite keys...
Besides that, you can do this:
or more explicit
The performance of join vs subquery should be just as good on most systems, on mysql joins are often even faster than subqueries.