DDC-581: Unable to use single value association with IN expression #714

New Issue

2026-01-22T12:47:44+01:00

admin commented

2026-01-22 12:47:44 +01:00

Originally created by @doctrinebot on GitHub (May 11, 2010).

Originally assigned to: @guilhermeblanco on GitHub.

Jira issue originally created by user 7heaven:

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.}

Originally created by @doctrinebot on GitHub (May 11, 2010). Originally assigned to: @guilhermeblanco on GitHub. Jira issue originally created by user 7heaven: 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. <sub>Tried to find similar issues or feature requests but no results. So I posted it here.</sub>

admin added the Bug label 2026-01-22 12:47:44 +01:00

admin referenced this issue

2026-01-22 16:00:43 +01:00

[PR #714] [CLOSED] Cache results for extra lazy get #8600

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#714