[solved] distinct and join 4 tables

hi all, i wanna ask something :

i have a mysql query like this :

select distinct b.execution_date,b.topik,m.name as id_media_seller,a.name as id_advertiser,p.name as id_product_category,bt.name as group_id,b.price 


from tbl_batch b,tbl_media_seller m,tbl_advertiser a,tbl_product_category p,tbl_bts_group bt 


where  m.id=b.id_media_seller and a.id=b.id_advertiser and p.id=b.id_product_category and bt.id=group_id and (b.execution_date >= '2011-07-05' and b.execution_date <= '2012-08-30')

and i want to convert into cdbcriteria like this but it’s not working :

$criteria = new CDbCriteria;

$criteria->distinct = true;

$criteria->select='execution_date,topik,tbl_media_seller.name,tbl_advertiser.name,tbl_product_category.name,group_id,price';

			$criteria->join='inner join tbl_media_seller on id_media_seller = tbl_media_seller.id';

			$criteria->join.='inner join tbl_advertiser on id_advertiser = tbl_advertiser.id';

			$criteria->join.='inner join tbl_product_category on id_product_category = tbl_product_category.id';

$criteria->addCondition('execution_date >= :to AND execution_date <= :from');

				


$criteria->addCondition('tbl_media_seller.id = :prm');

$criteria->addCondition('tbl_advertiser.id = :prm2');

$criteria->addCondition('tbl_product_category.id = :prm3');


$criteria->params=array(

		':to'=>$date[0],

		':from'=>$date[1],

		':prm'=>$media,

		':prm2'=>$adv,

		':prm3'=>$prod,

		);

how can i convert this? did i miss something?

thanks

I think you are missing:

$criteria->from('tbl_batch b');

Enable logs, so you can see on your screen queries that are executed, and you will be able to debug easier your queries.

hi ivica, i use this

$criteria->from('tbl_batch b,tbl_advertiser a,tbl_media_seller m,tbl_product_category p');

and th error said CDbCriteria does not have a method named "from".

yes, because from not defained in CDbCriteria Class.

you can call:

//Refer to tbl_batch  table

class Tbatch extends CAciveRecord {


}

Tbatch::model()->findAll($criteria);

thanks hermans, thanks all for all your help, it solve now.

here the codes(hope it will help people who has the same problem):

$criteria->select='execution_date,topik,m.name as id_media_seller,a.name as id_advertiser,p.name as id_product_category,bt.name as group_id,price';

			

$criteria->join='JOIN tbl_bts_group bt ON t.group_id = bt.id';

$criteria->join.=' JOIN tbl_media_seller m ON t.id_media_seller = m.id';

$criteria->join.=' JOIN tbl_advertiser a ON t.id_advertiser = a.id';

$criteria->join.=' JOIN tbl_product_category p ON t.id_product_category = p.id';

			

$criteria->addCondition('m.id=t.id_media_seller and a.id=t.id_advertiser and p.id=t.id_product_category and bt.id=t.group_id');

$criteria->addCondition('execution_date >= :to AND execution_date <= :from');