4

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.

5

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
  • Awesome, I had completely forgotten about that method. Thanks. – Whiskey Apr 22 '13 at 11:57
  • Yeah - thanks for this - took me a while to find this answer... – Felix Eve Oct 17 '13 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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