Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-581

Unable to use single value association with IN expression

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0-BETA4
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None

      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.

        Activity

        Hide
        Roman S. Borschel added a comment -

        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.

        Show
        Roman S. Borschel added a comment - 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.
        Hide
        Roman S. Borschel added a comment -

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

        Show
        Roman S. Borschel added a comment - Unscheduling as this enhancement is questionable and not really necessary (see my last comment).
        Hide
        Guilherme Blanco added a comment -
        Show
        Guilherme Blanco added a comment - Fixed. Implemented this support in: http://github.com/doctrine/doctrine2/commit/496a34a4d246f7b3e628e37963d8950e43540733

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Ivan Voskoboynyk
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: