Createcommand

Yii 1.1 General Discussion
1

Hello guys!

Sorry for my bad english beforehand :)

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:




$subQuery = Yii::app()->db->createCommand()

    ->select('SELECT IF(f.friend_one = '.Yii::app()->user->id.', f.friend_two, f.friend_one)')

    ->from('friend f')

    ->where('f.status = 1')

    ->andWhere('(f.friend_one = '.Yii::app()->user->id.' OR f.friend_two = '.Yii::app()->user->id.')')

    ->text;


$users = Yii::app()->db->createCommand()

    ->select(array ('u.first_name', 'u.last_name'))

    ->from('visitors v')

    ->leftJoin('users u', 'u.iduser = v.user_id')

    ->where('v.user_id IN ('.$subQuery.')')

    ->andWhere('v.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE)')

    ->queryAll();


CVarDumper::dump($users, 4, true);

Yii::app()->end();

I simply cant see the problem here the code gives me a syntax error msg (sql syntax)

just to be sure i paste it to:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

Any ideas?

2

dump the second query and see what it outputs


$sql = Yii::app()->db->createCommand()

    ->select(array ('u.first_name', 'u.last_name'))

    ->from('visitors v')

    ->leftJoin('users u', 'u.iduser = v.user_id')

    ->where('v.user_id IN ('.$subQuery.')')

    ->andWhere('v.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE)')

    ->getText();




echo $sql;

paste the output here might help to debug

3

Thenk you for the fast reply here is the output?


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))
4

I see you have an extra SELECT and some EXTRA parentheses try the following




$subQuery = Yii::app()->db->createCommand()

			->select('IF(f.friend_one = '.Yii::app()->user->id.', f.friend_two, f.friend_one)')

			->from('friend f')

			->where('f.status = 1')

			->andWhere('f.friend_one = '.Yii::app()->user->id.' OR f.friend_two = '.Yii::app()->user->id)

			->getText();


		$users = Yii::app()->db->createCommand()

			->select(array('u.first_name', 'u.last_name'))

			->from('visitors v')

			->leftJoin('users u', 'u.iduser = v.user_id')

			->where('v.user_id IN ('.$subQuery.')')

			->andWhere('v.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE)')

			->getText();

		echo "<pre>";

		echo $users;

		die;


// this is what it outputs

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 = , f.friend_two, f.friend_one)

FROM 'friend' 'f'

WHERE (f.status = 1) AND (f.friend_one = 1 OR f.friend_two = 1))) AND (v.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE))
5

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

6

it looks right to me, its likely you don’t have any results for the given criteria