If Else in Order by Clause in DB API of Drupal

Question

I have Drupal 7 site. I am using dbApi of Drupal. Now I have a query where I need to order the records.

Table Structure:-

• AlbumId
• Album Name
• Album Created Date
• Album Release Date

Now my requirement is if Album Release Date is not NULL then sort by it, else use the Album Created Date for sorting.

$query = db_select('node', 'n');
$query->condition('n.type', 'albums', '=')
        ->condition('status', 1) //Published.
        ->fields('n', array('nid'))
        ->orderBy('field_album_release_date_value', 'DESC')
        ->execute();
$result = $query->execute();

Any help highly appreciated.

Answer 1

Yes it is possible. You should be using addExpresion($expression).

$query = db_select('node', 'n');
$query->condition('n.type', 'albums', '=')
    ->condition('status', 1) //Published.
    ->fields('n', array('nid'));
$query->addExpression('IF(n.field_album_release_date_value is null,'
    . 'n.field_album_release_date_value, n.field_album_create_date_value)'
    . ' as available_date');
$query->orderBy('available_date', 'DESC');
$result = $query->execute();

Didn't test this, so you might need to tweak it, but fact is you can use addExpression to insert sql functions like min max or in your case if.