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.

1 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');


SELECT m.*, ifnull(max(case when mission = 1 then score end),0) AS mission_1
FROM {game_sessions} m
  • Awesome, I had completely forgotten about that method. Thanks.
    – Whiskey
    Commented Apr 22, 2013 at 11:57
  • Yeah - thanks for this - took me a while to find this answer...
    – Felix Eve
    Commented Oct 17, 2013 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.