Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-284

Function 'in' of class Expr does not work with an array of strings as parameter

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-ALPHA4
    • Fix Version/s: 2.0-BETA1
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      OS X 10.5, Apache 2.2.12, PHP 5.3.0, Doctrine 2 Alpha 4

      Description

      Function in of Expr seems not to work properly.
      I tried to fetch a result using a query with the function in() of Expr as described in the doctirne 2.0 documentation (querying an object Offer that contains a column named 'state' of type string)):

      $qb->select('ofr')
      ->from('Offer', 'ofr')
      >where($qb>expr()->in('ofr.state', array('test','second value','third value')));
      $query = $qb->getQuery();
      $result = $query->getResult();

      Then the following error occurs:
      [Syntax Error] line 0, col 139: Error: Expected Literal, got 'test'
      in ...Doctrine/ORM/Query/QueryException.php, on line 41

      Stack:
      Doctrine\ORM\Query\QueryException::syntaxError( string ) in ...Doctrine/ORM/Query/Parser.php on line 346
      Doctrine\ORM\Query\Parser->syntaxError( string ) in ...Doctrine/ORM/Query/Parser.php on line 2032
      Doctrine\ORM\Query\Parser->Literal( ) in ...Doctrine/ORM/Query/Parser.php on line 2047
      Doctrine\ORM\Query\Parser->InParameter( ) in ...Doctrine/ORM/Query/Parser.php on line 2423
      Doctrine\ORM\Query\Parser->InExpression( ) in ...Doctrine/ORM/Query/Parser.php on line 1928
      Doctrine\ORM\Query\Parser->SimpleConditionalExpression( ) in ...Doctrine/ORM/Query/Parser.php on line 1858
      Doctrine\ORM\Query\Parser->ConditionalPrimary( ) in ...Doctrine/ORM/Query/Parser.php on line 1815
      Doctrine\ORM\Query\Parser->ConditionalFactor( ) in ...Doctrine/ORM/Query/Parser.php on line 1791
      Doctrine\ORM\Query\Parser->ConditionalTerm( ) in ...Doctrine/ORM/Query/Parser.php on line 1773
      Doctrine\ORM\Query\Parser->ConditionalExpression( ) in ...Doctrine/ORM/Query/Parser.php on line 1854
      Doctrine\ORM\Query\Parser->ConditionalPrimary( ) in ...Doctrine/ORM/Query/Parser.php on line 1815
      Doctrine\ORM\Query\Parser->ConditionalFactor( ) in ...Doctrine/ORM/Query/Parser.php on line 1795
      Doctrine\ORM\Query\Parser->ConditionalTerm( ) in ...Doctrine/ORM/Query/Parser.php on line 1773
      Doctrine\ORM\Query\Parser->ConditionalExpression( ) in ...Doctrine/ORM/Query/Parser.php on line 1273
      Doctrine\ORM\Query\Parser->WhereClause( ) in ...Doctrine/ORM/Query/Parser.php on line 810
      Doctrine\ORM\Query\Parser->SelectStatement( ) in ...Doctrine/ORM/Query/Parser.php on line 780
      Doctrine\ORM\Query\Parser->QueryLanguage( ) in ...Doctrine/ORM/Query/Parser.php on line 275
      Doctrine\ORM\Query\Parser->parse( ) in ...Doctrine/ORM/Query.php on line 159
      Doctrine\ORM\Query->_parse( ) in ...Doctrine/ORM/Query.php on line 193
      Doctrine\ORM\Query->_doExecute( array ) in ...Doctrine/ORM/AbstractQuery.php on line 511
      Doctrine\ORM\AbstractQuery->execute( array, integer ) in ...Doctrine/ORM/AbstractQuery.php on line 349
      Doctrine\ORM\AbstractQuery->getResult( )

        Activity

        Hide
        Roman S. Borschel added a comment -

        This works perfect for me in my local copy. Here is a test I wrote:

            public function testWhereInWithStringLiterals()
            {
                $qb = $this->_em->createQueryBuilder();
                $qb->select('u')
                   ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u')
                   ->where($qb->expr()->in('u.name', array('one', 'two', 'three')));
                   
                $this->assertValidQueryBuilder($qb, "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IN('one', 'two', 'three')");
                
                $qb->where($qb->expr()->in('u.name', array("O'Reilly", "O'Neil", 'Smith')));
                   
                $this->assertValidQueryBuilder($qb, "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IN('O''Reilly', 'O''Neil', 'Smith')");
            }
        

        And subqueries still work, too.

        Show
        Roman S. Borschel added a comment - This works perfect for me in my local copy. Here is a test I wrote: public function testWhereInWithStringLiterals() { $qb = $ this ->_em->createQueryBuilder(); $qb->select('u') ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u') ->where($qb->expr()->in('u.name', array('one', 'two', 'three'))); $ this ->assertValidQueryBuilder($qb, "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IN('one', 'two', 'three')" ); $qb->where($qb->expr()->in('u.name', array( "O'Reilly" , "O'Neil" , 'Smith'))); $ this ->assertValidQueryBuilder($qb, "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IN('O''Reilly', 'O''Neil', 'Smith')" ); } And subqueries still work, too.
        Hide
        Guilherme Blanco added a comment -

        @romanb that would work, of course.

        BUT... It would break BC in the case I do something like:

        Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
        $qb->where($qb>expr()->in('ofr.state', array(
            $qb->expr()->literal('test'),
            $qb->expr()->literal('second value'),
            $qb->expr()->literal('third value')
        )));
        

        That would be the same as array("'test'", "'second value'", "'third value'"), which would double the quote and give even more headaches.
        I think we can consider this BC break now... what do you think?

        Show
        Guilherme Blanco added a comment - @romanb that would work, of course. BUT... It would break BC in the case I do something like: Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml $qb->where($qb>expr()->in('ofr.state', array( $qb->expr()->literal('test'), $qb->expr()->literal('second value'), $qb->expr()->literal('third value') ))); That would be the same as array("'test'", "'second value'", "'third value'"), which would double the quote and give even more headaches. I think we can consider this BC break now... what do you think?
        Hide
        Roman S. Borschel added a comment -

        @Guilherme: Good point. That is a problem, because it is not correct.

        After all, I start to think what we have right now is still best (literals should always be passed through ->literal()). Literals in queries are not that frequent anyway, since you mostly use bound parameters.

        Need to sleep over this.

        Show
        Roman S. Borschel added a comment - @Guilherme: Good point. That is a problem, because it is not correct. After all, I start to think what we have right now is still best (literals should always be passed through ->literal()). Literals in queries are not that frequent anyway, since you mostly use bound parameters. Need to sleep over this.
        Hide
        Guilherme Blanco added a comment -

        @romanb I think since we're in the last alpha, it's still acceptable that we break BC...

        I disagree with you that it's not used frequently. I think the opposite! While we still do not support IN ?, we're still struggled in the expr IN support, which is the exactly issue we're facing here.

        SO... We need to agree if we break BC or of we add overhead by adding a Literal class.
        Which one you think it'll be the best one to be merged?

        I'll merge one of them... just need some thoughts from you. =)

        Cheers,

        Show
        Guilherme Blanco added a comment - @romanb I think since we're in the last alpha, it's still acceptable that we break BC... I disagree with you that it's not used frequently. I think the opposite! While we still do not support IN ?, we're still struggled in the expr IN support, which is the exactly issue we're facing here. SO... We need to agree if we break BC or of we add overhead by adding a Literal class. Which one you think it'll be the best one to be merged? I'll merge one of them... just need some thoughts from you. =) Cheers,
        Hide
        Julien Ferment added a comment -

        Thank you all for your advices.
        This works for me :

        $string = "'string1','string2','string3'";

        >add('where',$qb>expr()->in('d.reference', $string))

        As you said, each string should be surrounded by single quotes.

        Julien

        Show
        Julien Ferment added a comment - Thank you all for your advices. This works for me : $string = "'string1','string2','string3'"; >add('where',$qb >expr()->in('d.reference', $string)) As you said, each string should be surrounded by single quotes. Julien

          People

          • Assignee:
            Roman S. Borschel
            Reporter:
            Ulrich Baltzer
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: