I recently started to work with yii and long story short i ran into a problem.
Here is the SQL statement that i wanted to rework with the createcommand function :
SELECT
first_name, last_name
FROM
`visitors` v
JOIN
users u ON v.user_id = u.iduser
WHERE
v.user_id IN (SELECT
IF(friend_one = 3,
friend_two,
friend_one) AS baratok
FROM
`friends`
WHERE
`status` = '1'
AND (friend_one = 3 OR friend_two = 3))
AND v.last_activity > DATE_SUB(now(), INTERVAL 5 MINUTE)
And here is the actual script that i wanted to run:
SELECT `u`.`first_name`, `u`.`last_name` FROM `visitors` `v` LEFT JOIN `users` `u` ON u.iduser = v.user_id WHERE (v.user_id IN (SELECT SELECT IF(f.friend_one = , f.friend_two, f.friend_one) FROM `friend` `f` WHERE (f.status = 1) AND ((f.friend_one = OR f.friend_two = )))) AND (v.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE))
Yesterday i tryed some modifications too and got the same code as you now but:
-the statement runs but dosnt give back anything
There are 2 extra () in the querry and i dont know why.
Here i paste the correct one so that u can see whats different
SELECT `u`.`first_name`, `u`.`last_name`
FROM `visitors` `v`
LEFT JOIN `users` `u` ON u.iduser = v.user_id
WHERE (v.user_id IN (SELECT IF(f.friend_one = 3, f.friend_two, f.friend_one)
FROM `friends` `f`
WHERE (f.status = 1)
AND friend_one = 3 OR friend_two = 3))
AND (v.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE))
As you can see the second row from the bottom
AND friend_one = 3 OR friend_two = 3))
and i dont know how to get those out from the create command part