convert traditional query to yii CDbCriteria


i’m realize i’m still new bie.

Let say, how to convert this traditional query to yii new CDbCriteria.

SELECT menu . * , privileges . * 

FROM `menu` , `privileges` 

WHERE menu.ID_Menu = privileges.ID_Menu

thanks a lot.

You can achive the same result with relations, I think.

Anyway this is the solution:

$criteria=new CDBCriteria;

$criteria->select= 'menu . * , privileges . *';

$criteria->join= 'JOIN privileges ON menu.ID_Menu = privileges.ID_Menu';


Thanks #zaccaria for your help.

i think this line must use alias ‘t’ or it wll got error…

assume im menu model

$criteria->select= 'menu . * , privileges . *';

change to

$criteria->select= ' t. * , privileges . *';

but I need one more table to join.

this is traditional query and its work in direct sql.


privileges.*, t.*, employer.* 



LEFT JOIN t ON privileges.ID_Menu  = t.ID_Menu

LEFT JOIN employer ON privileges.ID_User = employer.ID_Employer

this is yii cdbcriteria query and its throw an error.

$criteria=new CDbCriteria;

$criteria->select = "privileges.*, t.*, employer.* ";

$criteria->join = 'LEFT JOIN t ON privileges.ID_Menu = t.ID_Menu LEFT JOIN employer ON privileges.ID_User = employer.ID_Employer';

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL 

server version for the right syntax to use near 'privileges LEFT JOIN t ON privileges.ID_Menu' at line 1

please give me some hint

oh yeah, how to echo the query in yii…??

Assuming your model is menu.

$criteria=new CDbCriteria;

$criteria->select = "*"; // or "t.*, p.*"

$criteria->join = "LEFT JOIN privileges p ON t.ID_Menu=p.ID_Menu

    LEFT JOIN employer e ON p.ID_User=e.ID_User";

In SQL you can either select from multiple tables (comma separate table names in the FROM-part) or join the tables in the JOIN-part. There is no correct way, it always depends on what kind of result you want to get.

Thanks for your reply @Chris83,

I experiment on it, with various weird think and somehow its works.

at least, i got the result what i want.