Can db_select cope with mysql "case when" structures?

Question

I have a complex mysql query with "case when" to combine values across multiple rows in columns. I'm currenty using db_query, but I would like to use the TableSort and Pager extenders. But (correct me if I'm wrong) those only work with db_select.

This is the query:

SELECT gsid, created_by_uid, difficulty, gametime, max(submitted_on), score, mission, name, 
 ifnull(max(case when mission = 1 then score end),0) as mission_1,
 ifnull(max(case when mission = 2 then score end),0) as mission_2,
 ifnull(max(case when mission = 3 then score end),0) as mission_3,
 ifnull(max(case when mission = 4 then score end),0) as mission_4,
 ifnull(max(case when mission = 5 then score end),0) as mission_5,
 ifnull(max(case when mission = 6 then score end),0) as mission_6
FROM `game_sessions` m
INNER JOIN `users` u ON m.created_by_uid = u.uid
GROUP BY created_by_uid

The game_sessions table contains a row for each game mission that a user played. The game mission can be 1-6. This query gives me an overview with the maximum score for each mission per user (each row is a user).

Is it possible to rewrite this query using db_select? I'm using Drupal 7.

Answer 1

SelectQuery has the addExpression() method for adding arbitrary expressions. e.g.

$query = db_select('game_sessions', 'm')->fields('m');
$query->addExpression('ifnull(max(case when mission = 1 then score end),0)', 'mission_1');

Yields:

SELECT m.*, ifnull(max(case when mission = 1 then score end),0) AS mission_1
FROM {game_sessions} m