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

Order by With Equals is not supported

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: 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

        ibiryukov Ilya Biryukov created issue -
        ibiryukov 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
        beberlei 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
        beberlei 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
        beberlei Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        Show
        kosov Alexey Kosov added a comment - It does not actually work. http://stackoverflow.com/questions/25761989/doctrine-select-statement-using-equals-not-accepted

        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={expand=changesets[0:20].revisions[0:29],reviews, query=DDC-2204}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated:
              Resolved: