CODEWITHSUNDEEP
doctrine-ormdql
SnailCoil
SnailCoil

Reputation: 808

How to order by a computed value in DQL

I'm trying to order the results of my query by whether or not they match my original entity on a property. I could do this easily in mySQL with the following query:

SELECT * FROM table
ORDER BY prop = 'value' DESC;

However, in Doctrine, when I attempt the following:

// $qb is an instance of query builder
$qb->select('e')
   ->from('Entity', 'e')
   ->orderBy('e.prop = :value', 'DESC')
   ->setParameter('value', 'value');
// grab values

I get a Doctrine syntax error, 'end of string'. I looked into creating a custom function, but that seems like overkill. I'm fairly new to Doctrine, is there a better way to do this?

Upvotes: 19

Views: 16569

Answers (2)

totas
totas

Reputation: 10780

As I struggeled a while to figure out how to create that query using php syntax here's what I came up with:

$value = 'my-value';    
$qb->select('e')
    ->from('Entity', 'e')
    ->addSelect('CASE WHEN e.prop = :value THEN 1 ELSE 0 END AS HIDDEN sortCondition')
    ->setParameter('value', $value)
    ->addOrderBy('sortCondition', 'DESC');

Upvotes: 23

Ocramius
Ocramius

Reputation: 25431

Since Doctrine ORM 2.2, you can use the HIDDEN keyword and select additional fields, in this case with a CASE expression:

SELECT
    e,
    CASE WHEN e.prop = :value THEN 1 ELSE 0 END AS HIDDEN sortCondition
FROM
    Entity e
ORDER BY
    sortCondition DESC

Upvotes: 47

Related Questions