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

Closed
doctrinebot opened this Issue May 11, 2010 · 4 comments

2 participants

@doctrinebot

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.

@doctrinebot

Comment created by romanb:

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:

SELECT u FROM Entities\User u WHERE u.address.street LIKE 'Test Street%' 

or more explicit

SELECT u FROM Entities\User u JOIN u.address a WHERE a.street LIKE 'Test Street%' 

The performance of join vs subquery should be just as good on most systems, on mysql joins are often even faster than subqueries.

@doctrinebot

Comment created by romanb:

Unscheduling as this enhancement is questionable and not really necessary (see my last comment).

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.0-BETA4 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment