Drupal db_select PDO Statement: ORDER BY CASE

Question

Hi I have written a MySQL statement that has a conditional order by clause which you can see in the example below.

MySQL Example:

SELECT 
    title, 
    description
FROM books
WHERE 
    title LIKE "%keyword%" OR description LIKE "%keyword%"
ORDER BY 
    CASE 
        WHEN title LIKE "%keyword%"
        THEN 1
        ELSE 2
     END

I am now trying to recreate this statement in Drupal using its PDO style db_select() function. But I have got stuck when writing the ORDER BY clause.

Drupal Example:

 $node_select = db_select('node', 'n');
 $node_select->fields('n', array('title', 'description'));
 $node_select->condition(
     db_or()->condition('n.title', '%'.$keyword.'%', 'LIKE')
            ->condition('n.description', '%'.$keyword.'%', 'LIKE')
 );
 $node_select->order();

Can anyone point me in the right direction on how you would write the ORDER BY section?

Answer 1

I think you can do this by adding an expression in the select statement, then adding that field as the order by field. This will only work if you are ok having the extra field in the select list. This would work something like this:

$query->addExpression('CASE WHEN title LIKE "%keyword%" THEN 1 ELSE 2 END', 'order_col');
$query->orderBy('order_col', 'ASC');

I don't think you can specifically use expressions in the orderBy clause, but this should work for you.