Join Query In Yii Framework

Following is my simple sql query.

select lead.*,city.cityName,state.stateName,leadSourceMaster.leadSource,leadStageMaster.leadStage,leadStatusMaster.leadStatus FROM lead left join city on (city.cityId=lead.cityId) left Join state on (state.stateId=lead.stateId)left join leadSourceMaster on (leadSourceMaster.leadSourceId=lead.leadSourceId) left join leadStageMaster on (leadStageMaster.leadStageId=lead.leadStageId)left join leadStatusMaster on (leadStatusMaster.leadStatusId=lead.leadStatusId) where$name AND lead.leadStageId=$stage AND lead.leadStatusId=$status;

Now is need to convert this query into yii framework style.

Funda is that. User will enter filter conditions like name,leadstage,leadstatus all 3 or can any one among 3.

I need to fetch records using CActiveDataProvider. Can any body help me to do this. Help will be appreciable.

Hi see example…

 $criteria = new CDbCriteria;

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

            $criteria->join = ' LEFT JOIN `user_crew` AS `tu` ON = tu.user_id';

            $criteria->addCondition("display_name LIKE '%a%' and blocked_by='76'");

            $resultSet    =    Customer::model()->findAll($criteria);


I hope it’s some help.

$criteria = new CDbCriteria;

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

        $criteria->join ='LEFT JOIN products_categories ON products_categories.products_id = t.products_id';

        $criteria->condition = 'products_categories.categories_id = :value';

        $criteria->params = array(":value" => "C1");

The code above will work just fine if you call


you can join any number of tables like this

Thanks to reply Balu .

Can you please write the full query for me. actually i am new to yii and after lots of efforts i cant write this in correct way. please write full query with CActiveDataProvider. But i can’t use findAll(). Must use CActiveDataProvider.

Dear i need to use CActiveDataProvider.

Here my optimized code as you guid me. Is this correct.?? it running perfect but while i am printing the $dataProvider i am not getting any output. it printing only table schemas.

$criteria = new CDbCriteria;

    $criteria->select = 't.*,, state.state, leadSourceMaster.leadSourceTitle, leadStageMaster.leadStage, leadStatus.leadStatus';

    $criteria->join ='LEFT JOIN city ON city.cityId = t.cityId';

	$criteria->join ='LEFT JOIN state ON state.stateId = t.stateId,LEFT JOIN leadSourceMaster ON leadSourceMaster.leadSourceId = t.leadSourceId,LEFT JOIN leadStageMaster ON leadStageMaster.leadStageId = t.leadStageId,LEFT JOIN leadStatus ON leadStatus.leadStatusId  = t.leadStatusId';

	$criteria->condition = ' = :name AND t.leadStageId = :stage AND t.leadStatusId = :status';

    $criteria->params = array(":name" => $name,":stage" => $stage,":status" => $status,);


$dataProvider=new CActiveDataProvider('LeadReport', array(






if you want to print a CActiveDataProvider object you should do:

$dataProvider=new CActiveDataProvider('LeadReport', array(






$datas = $dataProvider->getData();

foreach ($datas as $data){

  	echo $data->city;



or send that $dataProvider to the view file and use it in a CListView.

Hii to all…

i need to remain show the value in text box after clicking on submit button. As we generally do in HTML text box like : <input type=“text” name=“name” value="<?php echo @$_POST[‘name’];?>">.

I want to do the same in yii. I tried to do this as :

<?php echo $form->textField($model,‘name’,array(‘class’=>‘txtfield_new’,‘id’=>‘name’, ‘autocomplete’=>‘off’,‘value’=>$model->name)); ?>

But i did’t get banifit.

Can any body help me. ????

Oh your answer just save my life thanks.Live long and keep helping others.