[SOLVED] Converting SQL where CASE query to Yii2 Active Record

Converting SQL where CASE query to Yii2 Active Record

I have been trying to convert the below SQL query to a yii based active records query with andWhere but no luck

SELECT * FROM `users` 
WHERE `role` = 1
AND (CASE users.type WHEN 'admin' THEN users.admin_id ELSE users.member_id END IN ('94', '56', '173', '172', '50', '67', '57', '86', '53', '93', '39', '40', '171', '175', '136', '160', '145', '177', '156', '88', '125', '176', '157', '120', '165', '116', '103', '107', '149', '134', '146', '92', '164', '82', '148', '111', '78', '77', '151', '87', '129',, '29', '62'))

Solution

If users table is represented by User class model:

User::find()
    ->where(['role' => 1])
    ->andWhere("(CASE users.type WHEN 'admin' THEN users.admin_id ELSE users.member_id END IN ('94', '56', '173', '172', '50', '67', '57', '86', '53', '93', '39', '40', '171', '175', '136', '160', '145', '177', '156', '88', '125', '176', '157', '120', '165', '116', '103', '107', '149', '134', '146', '92', '164', '82', '148', '111', '78', '77', '151', '87', '129',, '29', '62'))")
    ->all();