How to translate SQL CASE statement into Yii2 ActiveRecord query

Data and Databases
1

I’m struggling to convert this SQL query into Yii2 ActiveRecord format using andWhere conditions. The query involves a CASE statement that checks different ID fields based on user type.

SELECT * FROM `employees` 
WHERE `status` = 2
AND (CASE employees.category WHEN 'supervisor' THEN employees.supervisor_id ELSE employees.staff_id END IN ('12', '45', '78', '23', '89', '56', '34', '67', '91', '28', '73', '15', '44', '66', '82', '37', '19', '58', '76', '93', '41', '84', '62', '27', '95', '38', '71', '49', '86', '33', '64', '18', '52', '79', '46', '21', '87', '59', '74', '42', '68', '96', '31', '85', '53'))

I’ve tried different approaches with ActiveRecord but can’t get the CASE logic to work properly. Any suggestions on how to handle conditional field selection in Yii2 queries?

2

Interesting challenge! Two questions though - won’t that many IDs in the IN clause hurt performance? And what’s your plan when you need more employee categories? I’m wondering if there’s a way to make this dynamic instead of hardcoding all the case logic.

3

using Expression is the way to go for raw sql in ActiveRecord. try Employee::find()->where(['status' => 2])->andWhere(new Expression('(CASE category WHEN "supervisor" THEN supervisor_id ELSE staff_id END) IN (' . implode(',', $your_ids_array) . ')'))->all() – that should fix the issue.

4

You can split this into separate OR clauses using ActiveRecord’s built-in methods instead. Try Employee::find()->where(['status' => 2])->andWhere(['or', ['and', ['category' => 'supervisor'], ['supervisor_id' => $ids_array]], ['and', ['!=', 'category', 'supervisor'], ['staff_id' => $ids_array]]])->all(). This skips raw SQL completely and makes your query way more readable. I’ve done this tons of times with conditional field logic - it performs great and you keep all the query builder perks like parameter binding and cross-database compatibility.