Multiple Models In Cgridview (Indirectly Related)

Yii 1.1 General Discussion
1

Hello!

I have three models, - name, city, population - related in series

name.id_city -> city.city_name

and

city.id_population -> population->pop_amount

So i need table view with id(name), name, city and population

The trouble is - i need to use cgridview with several indirectly related models.

Now i can show data from name and city, or from city and population, but not all together.

Here is my code:

Model ‘name’




// models/name.php

class name extends CActiveRecord

{

    public static function model($className=__CLASS__)

    {

        return parent::model($className);

    }


    public function tableName()

    {

        return 'name';

    }


    public function rules()

    {

        return array(

            array('id, name_person, nameCity', 'safe', 'on'=>'search'),

        );

    }


    public function attributeLabels()

    {

        return array(

            'id' => 'Id',

            'name_person' => 'Name',

            'nameCity' => 'City',

        );

    }


    public function relations()

    {

        return array(

            'nameCity'=>array(self::BELONGS_TO, 'city', 'id_city'),

        );

    }


    public function search()

    {

        $criteria=new CDbCriteria;

        $criteria->compare('t.id',$this->id);

        $criteria->compare('name_person',$this->name_person,true);


        $criteria->with=array('nameCity');

        $criteria->compare('nameCity.name_city',$this->nameCity,true);


        return new CActiveDataProvider('name', array(

            'criteria'=>$criteria,

            'sort'=>array('attributes'=>array(

                'nameCity'=>array(

                    'asc' => $expr='nameCity.name_city',

                    'desc' => $expr.' DESC',

                ),

                'id'=>array(

                    'asc' => $expr='t.id',

                    'desc' => $expr.' DESC',

                ),

                'name_person',

            )),

            'pagination'=>array(

                'pageSize'=>40,

            ),

        ));

    }

}

Model ‘city’




<?php

// models/city.php

class city extends CActiveRecord

{

    public static function model($className=__CLASS__)

    {

        return parent::model($className);

    }


    public function tableName()

    {

        return 'city';

    }

    public function attributeLabels()

    {

        return array(

            'id' => 'ID',

            'name_city' => 'City',

            'populationCity' => 'Population'

        );

    }


    public function relations()

    {

        return array(

            'populationCity'=>array(self::BELONGS_TO, 'population', 'id_population'),

        );

    }


    public function search()

    {

        $criteria=new CDbCriteria;

        $criteria->compare('t.id',$this->id);

        $criteria->compare('name_city',$this->name_city,true);


        $criteria->with=array('populationCity');

        $criteria->compare('populationCity.pop_amount',$this->populationCity,true);


        return new CActiveDataProvider('city', array(

            'criteria'=>$criteria,

            'sort'=>array('attributes'=>array(

                'populationCity'=>array( 

                    'asc' => $expr='populationCity.pop_amount',

                    'desc' => $expr.' DESC',

                ),

                'id'=>array( // сортировка по id

                    'asc' => $expr='t.id', 

                    'desc' => $expr.' DESC',

                ),

                'name_city',

            )),

            'pagination'=>array(

                'pageSize'=>40,

            ),

        ));

    }

}

Model ‘population’




// models/population.php

class population extends CActiveRecord

{


    public static function model($className=__CLASS__)

    {

        return parent::model($className);

    }


    public function tableName()

    {

        return 'population';

    }

}

Function for it i’ve put in ‘SiteController’




public function actionView()

    {

        $model_name=new name('search');

        if(isset($_GET['name']))

            $model_name->attributes=$_GET['name'];

        $model_city=new city('search');

        if(isset($_GET['city']))

            $model_city->attributes=$_GET['city'];


        $this->render('view',array(

            'model_name'=>$model_name,

            'model_city'=>$model_city,

        ));

    }

And ‘view’




<?php $this->widget('zii.widgets.grid.CGridView', array(

    'id'=>'name-grid',

    'dataProvider'=>$model_city->search(),

    'filter'=>$model_city,

    'columns'=>array(

        'id',

        /*'name_person',

        array(

            'name'=>'nameCity',

            'value'=>'$data->nameCity->name_city',

            'sortable'=>true,

        ),*/

        'name_city',

        array(

            'name'=>'populationCity',

            'value'=>'$data->populationCity->pop_amount',

            'sortable'=>true,

        ),

        array(

            'class'=>'CButtonColumn',

        ),

    ),

)); ?>

So, is there an option to use several data providers?

Or, maybe, there are better ways for this - just show me where to go, please)

2

Here is an example of dataprovider from multiple model which i am using to create a cgrid view.




$criteria=new CDbCriteria;

	    $criteria->condition="t.targetlist_id=:targetlistId";

	    //$criteria->condition="targetlist_id=:targetlistId AND contactInfo.dont_Call!=1";

	    $criteria->params=array(':targetlistId'=>$id);

	    $criteria->with=array('contactInfo','callInfo');

	    $criteria->order="t.order ASC";

	    $criteria->together=true;

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

		                   'criteria'=>$criteria,

                           'pagination'=>array(

			      			  'pageSize'=>1000,

			  			 ),

		));

And here is a code for view file where i am showing a crgid view.




 <?php 

						$this->widget('zii.widgets.grid.CGridView', array(

						    'dataProvider'=>$dataprovider,

						    'enableSorting'=>false,

						    'emptyText'=>'There is no Contact added yet for this TargetList',

						   'id'=>'targetContacts',

						    'selectableRows'=>2, // multiple rows can be selected

						    //'summaryText' => '{count} records(s) found.',

						     'summaryText' => '',

						  //  'template'=>"{pager}\n{items}\n{pager}", //To show pager on top

						   // 'template'=>"{pager}\n{items}", //To show pager on top

						    'columns'=>array(

					       array('name'=>'cnt_name','value'=>'$data->contactInfo->salutation." ".$data->contactInfo->first_name." ".$data->contactInfo->last_name'),  

						   array('name'=>'date_modified','value'=>'$data->date_modified'),

					       array('name'=>'cnt_title','value'=>'$data->contactInfo->title'),

					       array('name'=>'cnt_state','value'=>'$data->contactInfo->primary_address_state'),

						   array('name'=>'cnt_email','value'=>'$data->contactInfo->email'),

						   array('name'=>'cnt_phone','value'=>'$data->contactInfo->phone_work'),

						  //array('name'=>'note','value'=>'$data->callInfo->notes'),

						  array('name'=>'note','value'=>array($this,'getNote')),

						  array('type'=>'raw','name'=>'comment','value'=>array($this,'getComment')),

						  array('type'=>'raw','name'=>'Call Status','value'=>array($this,'getCallStatus')),

						   array( // display a column with "view", "update" and "delete" buttons

            					'class'=>'CButtonColumn',

						         'template'=>'{targetlist}',

						         'buttons'=>array

								    (

								        'targetlist' => array

								        (

								            'label'=>'Call Now',

								            'imageUrl'=>$tUrl.'/images/call.png',

								           // 'url'=> '"javascript:openWindow(\"".$data->order."\",\"".$data->contactInfo->id."\",\"".$data->targetlist_id."\");"',

								            'url'=> '"javascript:openWindow(\"".$data->order."\",\"".$data->targetlist_id."\");"',

											'visible'=>'($data->contactInfo->dont_call!=1)?true:false;'

								        ),

								    ),

        						),            

						  ), 	 

						));

And make sure you have define appropriate relations in model classes for it.

3

Thank you for answer! If i understand you right, i have to make table ‘name’ related with two others.

Is it possible to show grid without relation between tables ‘name’ and ‘population’?

This is the structure of tables:

table ‘name’:

  • id

  • name_person

  • id_city - belongs to city.id

table ‘city’

  • id

  • name_city

  • id_population - belongs to population.id

table ‘population’:

  • id

  • pop_amount

As you can see, there is no relation between them - is it possible at all? And how i can show data without relations?

Maybe i should make an array, write there all data from all the tables and give it as a data provider?

Can DAO help me with it?

4

On SQL it’s like:


SELECT n.id, n.name_person, c.name_city, p.pop_amount FROM name as n, city as c, population as p 

WHERE n.id_city = c.id AND c.id_population = p.id;

But i don’t know, how to make it with yii code.

5

Hi kinos,

Just concatenate the relations.

Name BELONGS_TO City, and City BELONGS_TO Population … then you can easily access the population from the name.




// fetch Name

$name = Name::model()->findByPk($id);

// $name's City

$city = $name->city;

// $city's Population

$population = $city->population;

// $population's amount

$amount = $population->amount;


// in short

$amount = $name->city->population->amount;

And in the search function of Name, you can join the population table like the following:




$criteria->with = array('city', 'city.population');

$criteria->compare('t.name', $person_name, true);

$criteria->compare('city.name', $city_name, true);

$criteria->compare('population.amount, $population_amount);

...
6

I’ve solved it with DAO.

In class ‘name’ i’ve added function ‘collectData’:




public function collectData(){

    $connection=Yii::app()->db;

    $sql = "SELECT n.id, n.name_person, c.name_city, p.pop_amount FROM name as n, city as c, population as p WHERE n.id_city = c.id AND c.id_population = p.id;";

    $command=$connection->createCommand($sql);

    $dataReader = $command->query();

    return $dataReader->readAll();

}

In controller i’ve called it with this:




$model = new name();

$itemsProvider = new CArrayDataProvider($model->collectData());


$this->render('view',array(

            'itemsProvider'=>$itemsProvider

            ));

And in ‘view’ i’ve shown the grid with this:




$this->widget('zii.widgets.grid.CGridView', array(

    'id' => 'itemGrid',

    'dataProvider' => $itemsProvider,

    'columns' => array(

        'id',

        'name_person',

        'name_city',

        'pop_amount'

    ),

));

Result:

7

Hi

Check out my RelatedSearchBehavior extension. In the demo you can see that you can link a lot of tables together with ease.