Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2204

Order by With Equals is not supported

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Invalid
    • Affects Version/s: 2.3
    • Fix Version/s: None
    • Component/s: DQL
    • Labels:
    • Environment:
      SQL construct tested on postgres 9.0, mysql 5.5, and sqlite 3.

      Description

      The sample query (I want to bring a specific item to the top of the list).
      mysql> select * from Language order by name='English' desc, name asc limit 5;
      ------------+

      id name

      ------------+

      82 English
      73 Albanian
      74 Arabic
      75 Armenian
      76 Bengali

      ------------+
      5 rows in set (0.00 sec)

      In theory, the code below should generate the same query.
      $repository->createQueryBuilder('p')
      ->addOrderBy("p.name='english'", 'desc')
      ->addOrderBy('p.name', 'asc');

      In practice, an exception is thrown.
      Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 67: Error: Expected end of string, got '=' (uncaught exception) at /vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 44

      Attached, SQL dump for the table & data

        Activity

        Ilya Biryukov created issue -
        Ilya Biryukov made changes -
        Field Original Value New Value
        Description The sample query (I want to bring a specific item to the top of the list).
        mysql> select * from Language order by name='English' desc, name asc limit 5;
        +----+----------+
        | id | name |
        +----+----------+
        | 82 | English |
        | 73 | Albanian |
        | 74 | Arabic |
        | 75 | Armenian |
        | 76 | Bengali |
        +----+----------+
        5 rows in set (0.00 sec)

        In theory, the code below should generate the same query.
        $repository->createQueryBuilder('p')
        ->addOrderBy("p.name='english'", 'desc')
        ->addOrderBy('p.name', 'asc');

        In practice, an exception is thrown.
        Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 67: Error: Expected end of string, got '=' (uncaught exception) at /home/ilya/work/Wanawork/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 44

        Attached, SQL dump for the table & data
        The sample query (I want to bring a specific item to the top of the list).
        mysql> select * from Language order by name='English' desc, name asc limit 5;
        +----+----------+
        | id | name |
        +----+----------+
        | 82 | English |
        | 73 | Albanian |
        | 74 | Arabic |
        | 75 | Armenian |
        | 76 | Bengali |
        +----+----------+
        5 rows in set (0.00 sec)

        In theory, the code below should generate the same query.
        $repository->createQueryBuilder('p')
        ->addOrderBy("p.name='english'", 'desc')
        ->addOrderBy('p.name', 'asc');

        In practice, an exception is thrown.
        Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 67: Error: Expected end of string, got '=' (uncaught exception) at /vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 44

        Attached, SQL dump for the table & data
        Hide
        Benjamin Eberlei added a comment -

        Its supported by including the condition in the SELECT clause, aliasing it, then using it. You might need to use "AS HIDDEN name" to prevent it from appearing in the result

        Show
        Benjamin Eberlei added a comment - Its supported by including the condition in the SELECT clause, aliasing it, then using it. You might need to use "AS HIDDEN name" to prevent it from appearing in the result
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2204, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Ilya Biryukov
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: