CODEWITHSUNDEEP
mysqldoctrine-ormdoctrine
luqita
luqita

Reputation: 4077

Doctrine ORDER BY with conditions

I have the following in Doctrine:

$query = $this->_em
              ->createQuery("SELECT f
              FROM models\Food f 
              WHERE f.name LIKE :query 
              OR f.country LIKE :query
              OR f.code = :query_full
              ORDER BY f.code != :query_full, f.rank ASC")
              ->setParameters(
                  array(
                     "query" => "%$query%",
                      "query_full" => $query
               ));

The query works in MySQL Workbench, but Doctrine throws the exception:

 [Syntax Error] line 0, col 359: Error: Expected end of string, got '!'

How can I write that query in Doctrine? The idea is to always have f.code on top of the results, if the query matches it.

Upvotes: 0

Views: 473

Answers (1)

acontell
acontell

Reputation: 6922

You can try several approaches: using SQL, QueryBuilder, DQL... Except the one with native SQL, the rest will need to use the CASE statement on the select part of the query.

Following your approach, your query would be:

$query = $this->_em
              ->createQuery("SELECT f, (CASE WHEN (f.id != :query_full) THEN 1 ELSE 0 END) as myOrderBy
              FROM models\Food f 
              WHERE f.name LIKE :query 
              OR f.country LIKE :query
              OR f.code = :query_full
              ORDER BY myOrderBy ASC, f.rank ASC")
              ->setParameters(
                  array(
                     "query" => "%$query%",
                      "query_full" => $query
               ));

You must set ASC or DESC and the case to the values that would suit you.

It should work. Hope it helps.

Upvotes: 2

Related Questions