Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-2214

extra single quotation in sql when using EntityRepository::findBy

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.4
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      I'm using symfony 2.1 with mysql.

      I have following code:

      $related = 
      $this->getDoctrine()->getRepository('MyWebBundle:LineRelated')
      ->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
      

      that generate the sql like this:

      SELECT *
      FROM line_related t0 
      WHERE t0.line_id IN ('6059', 126352, '5677', '6058') 
      ORDER BY t0.count DESC 
      LIMIT 20
      

      please notice that the sql has extra single quotation around the number 6059,5677 and 6058. which make the sql very slow.

      I did a test, when using single quotation,the sql takes 300ms,when using without single quotation,the sql takes 1 ms.

        Activity

        scourgen scourgen created issue -
        scourgen scourgen made changes -
        Field Original Value New Value
        Description I'm using symfony 2.1 with mysql.

        I have following code:

        {code}
        $related = $this->getDoctrine()->getRepository('ZuoWebBundle:LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
        {code}

        that generate the sql like this:

        {code:sql}
        SELECT SQL_NO_CACHE * FROM line_related t0 WHERE t0.line_id IN ('6059', 126352, '5677', '6058') ORDER BY t0.count DESC LIMIT 20
        {code}

        please notice that the sql has extra single quotation around the number 6059,5677 and 6058. which make the sql very slow.

        I did a test, when using single quotation,the sql takes 300ms,when using without single quotation,the sql takes 1 ms.



        I'm using symfony 2.1 with mysql.

        I have following code:

        {code}
        $related = $this->getDoctrine()->getRepository('MyWebBundle:LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
        {code}

        that generate the sql like this:

        {code:sql}
        SELECT SQL_NO_CACHE * FROM line_related t0 WHERE t0.line_id IN ('6059', 126352, '5677', '6058') ORDER BY t0.count DESC LIMIT 20
        {code}

        please notice that the sql has extra single quotation around the number 6059,5677 and 6058. which make the sql very slow.

        I did a test, when using single quotation,the sql takes 300ms,when using without single quotation,the sql takes 1 ms.



        scourgen scourgen made changes -
        Description I'm using symfony 2.1 with mysql.

        I have following code:

        {code}
        $related = $this->getDoctrine()->getRepository('MyWebBundle:LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
        {code}

        that generate the sql like this:

        {code:sql}
        SELECT SQL_NO_CACHE * FROM line_related t0 WHERE t0.line_id IN ('6059', 126352, '5677', '6058') ORDER BY t0.count DESC LIMIT 20
        {code}

        please notice that the sql has extra single quotation around the number 6059,5677 and 6058. which make the sql very slow.

        I did a test, when using single quotation,the sql takes 300ms,when using without single quotation,the sql takes 1 ms.



        I'm using symfony 2.1 with mysql.

        I have following code:

        {code}
        $related =
        $this->getDoctrine()->getRepository('MyWebBundle:LineRelated')
        ->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
        {code}

        that generate the sql like this:

        {code:sql}
        SELECT *
        FROM line_related t0
        WHERE t0.line_id IN ('6059', 126352, '5677', '6058')
        ORDER BY t0.count DESC
        LIMIT 20
        {code}

        please notice that the sql has extra single quotation around the number 6059,5677 and 6058. which make the sql very slow.

        I did a test, when using single quotation,the sql takes 300ms,when using without single quotation,the sql takes 1 ms.



        fabio.bat.silva Fabio B. Silva made changes -
        Attachment DDC2214Test.php [ 11366 ]
        ocramius Marco Pivetta made changes -
        Assignee Benjamin Eberlei [ beberlei ] Marco Pivetta [ ocramius ]
        ocramius Marco Pivetta made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        ocramius Marco Pivetta made changes -
        Resolution Invalid [ 6 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        ocramius Marco Pivetta made changes -
        Status Reopened [ 4 ] Awaiting Feedback [ 10000 ]
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.3.3 [ 10329 ]
        Fix Version/s 2.3.2 [ 10324 ]
        ocramius Marco Pivetta made changes -
        Status Awaiting Feedback [ 10000 ] In Progress [ 3 ]
        beberlei Benjamin Eberlei made changes -
        Fix Version/s 2.4 [ 10321 ]
        Fix Version/s 2.3.3 [ 10329 ]
        ocramius Marco Pivetta made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]

          People

          • Assignee:
            ocramius Marco Pivetta
            Reporter:
            scourgen scourgen
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: