Join Query In Yii Framework

Yii 1.1 General Discussion
1

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 lead.name=$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.

2

Hi see example…


 $criteria = new CDbCriteria;

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

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

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

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

I hope it’s some help.

3 



$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




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

you can join any number of tables like this

4

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.

5

Dear i need to use CActiveDataProvider.

6

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.*,city.city, 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 = 't.name = :name AND t.leadStageId = :stage AND t.leadStatusId = :status';


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


print_r($criteria);	


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


'criteria'=>$criteria,


'pagination'=>array(


    'pageSize'=>20,


),

));

7

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




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

    	'criteria'=>$criteria,

    	'pagination'=>array(

             	'pageSize'=>20,

      	),

   	)); 						 						


$datas = $dataProvider->getData();

foreach ($datas as $data){

  	echo $data->city;

...

}

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

8

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. ????

9

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