I am trying to setup an advanced list sorting query that lists a few people by a specific field value and the rest by name .
By the info supplied on the configuration page for the advanced sorting you just need to supply a valid ORDER BY statement.
When I put in a ORDER BY statement that has been confirmed to work in mysql I just get the sql error "1064 You have an error in your SQL syntax" when going to list in the front end.
Below is the order by statement I put into the advanced sort configuration.
Code:
ORDER BY CASE
WHEN ue.cb_position LIKE "President%" THEN 1
WHEN ue.cb_position LIKE "Vice President%" THEN 2
WHEN ue.cb_position LIKE "Secretary%" THEN 3
WHEN ue.cb_position LIKE "Tresurer%" THEN 4
WHEN ue.cb_position LIKE "Ordinary Committee Member%" THEN 5
ELSE 6
END, u.name
This is the full query that works in mysql
Code:
SELECT DISTINCT ue.*, u.*, '' AS 'NA'
FROM j25_users u JOIN j25_user_usergroup_map g ON g.`user_id` = u.`id` JOIN j25_comprofiler ue ON ue.`id` = u.`id`
WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND g.group_id IN (2, 3) AND ( `cb_committeemember` = '1' )
ORDER BY CASE
WHEN ue.cb_position LIKE "President%" THEN 1
WHEN ue.cb_position LIKE "Vice President%" THEN 2
WHEN ue.cb_position LIKE "Secretary%" THEN 3
WHEN ue.cb_position LIKE "Tresurer%" THEN 4
WHEN ue.cb_position LIKE "Ordinary Committee Member%" THEN 5
ELSE 6
END, u.name
Am I doing it wrong?
krileon
