Translate Mysql Syntax To Yii's Cdbcriteria

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!

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';



Thanks!