Translate Mysql Syntax To Yii's Cdbcriteria

Yii 1.1 General Discussion
1

Hello,

I have this mysql query code that works Ok:


SELECT

tbl_date.*, 

tbl_item.product_id 

FROM tbl_date

LEFT JOIN 

tbl_item 

ON 

tbl_date.item_id = tbl_item.item_id

AND

tbl_item.product_id = 2;

But when executing that like this:


				$criteria = new CDbCriteria();

		$criteria->select = '*';

		$criteria->join ='INNER JOIN tbl_item ON tbl_date.item_id = tbl_item.item_id AND tbl_item.product_id = 2';

		$products=Date::model()->findAll($criteria);

It gives to me the following error:


CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tbl_date.item_id' in 'on clause'. The SQL statement executed was: SELECT `t`.`date_id`, `t`.`type_date`, `t`.`date`, `t`.`date_end`, `t`.`type_time`, `t`.`time`, `t`.`time_end`, `t`.`item_id` FROM `tbl_date` `t` INNER JOIN tbl_item ON tbl_date.item_id = tbl_item.item_id AND tbl_item.product_id = 2

I don’t understand where is my fault, I’m sure that this table exists,…

Thank you very much!

2

this is because ActiveRecord uses aliases instead of plain table names. Main table has default alias "t". This means you should type:




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

$criteria->join ='INNER JOIN tbl_item ON t.item_id = tbl_item.item_id AND tbl_item.product_id = 2';
3

Thanks!