Proper Way To Related Models And Preform Db Queries

Yii 1.1 General Discussion
1

Hello all,

I’m pretty confused on what I am doing wrong.

I have a view duplicating a db query every record.

How could I get this to stop doing this?

Also, is there a way I could join the first 2 calls into one?

here is the log

0.00028 ms system.db.CDbCommand.query(SELECT COUNT(*) FROM companies t)

0.00041 ms system.db.CDbCommand.query(SELECT * FROM companies t ORDER BY name ASC LIMIT 50)

[size="5"]

The next one repeats/duplicates for every record[/size]

0.00030 ms system.db.CDbCommand.query(SELECT country0.id AS t1_c0, country0.name AS t1_c1, country0.abbreviation AS t1_c2 FROM country country0 WHERE (country0.id=:ypl0). Bound with :ypl0=‘1’)

Model




	public function relations()

	{ 

		return array(

                        .......

			'country0' => array(self::BELONGS_TO, 'Country', 'country'),

		);

	}


	

	public function search()

	{

		$criteria=new CDbCriteria;

                 .....

		$criteria->compare('country',$this->country);

			return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'pagination'=>array(

                        'PageSize'=>50),

			'sort'=>array(

				'defaultOrder'=>'name ASC',

			),

		));

	}

view




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

	.......

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

        'filter'=>$model,

	'columns'=>array(		

		....

		array(

			'name'=>'country',

			'type'=>'raw',

			'value' => '$data->country0->abbreviation', 

		),

		

	),

)); ?>

Controller





<?php


class CompaniesController extends Controller

{

	//renders the view

	public function actionList()

	{

		$model = new Companies;

		

		$dataProvider=new CActiveDataProvider('Companies');

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

			'dataProvider'=>$dataProvider,

			'model' => $model,

		));

	}


	public function loadModel($id)

	{

		$model=Companies::model()->findByPk($id);

		if($model===null)

			throw new CHttpException(404,'The requested page does not exist.');

		return $model;

	}
2

That’s because of lazy loading.

Use eager loading instead.

You can read about it here

and here

3

The first part is normal, Yii first performs a query to determine the total number of rows in the grid view, so it knows how many page links to create. The second query is the data.

To fix the other problem, try adding the following into your search() method:




        public function search()

        {

                $criteria=new CDbCriteria;

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

                $criteria->together = true;

                 .....

        }

That will eagerly load the country0 relation. I’m not certain if the search() method is the recommended place to do this, but it’s what I tend to do.

4

You can also use my RelatedSearchBehavior extension which implicitally proposes the ‘KeenActiveDataProvider’ extension. The latter allows “eager” loading of selected columns. Just 3 queries per CGridView if properly configured: count, fetch main records, fetch related records. The first just makes the use of relations in gridviews easier.

5

Thanks ORey, Keith, and le_top

this worked…

however, I got this error…

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘name’ in order clause is ambiguous.

The SQL statement executed was: SELECT t.filename AS t0_c0, …more calls… country0.abbreviation AS t1_c2 FROM companies t LEFT OUTER JOIN country country0 ON (t.country=country0.id) ORDER BY name ASC LIMIT 50

Here is the whole model search function




	public function search()

	{

		$criteria=new CDbCriteria;

		....others....

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

		....others....

		$criteria->compare('country',$this->country);

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

                $criteria->together = true;

			return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'pagination'=>array(

                        'PageSize'=>50),

			'sort'=>array(

				'defaultOrder'=>'name ASC',

			),

		));

	}

I got this error because you need to fully qualify id because companies and products both have a column called id. So I needed to change name to t.name. So it should be like this…




	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


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

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

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

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

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

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

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

		$criteria->compare('zip',$this->zip);

		$criteria->compare('country',$this->country);

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

                $criteria->together = true;

			return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'pagination'=>array(

                        'PageSize'=>50),

			'sort'=>array(

				'defaultOrder'=>'t.name ASC',

			),

		));

	}

This stopped all my duplicating calls for the column country. No I have 2 db calls vs… well a freaking lot.

For anyone new or who didn’t know like me :) thanks again…guys.

I have one more question!!!

I have read a lot on cache and I would like to know how I could cache the country name so it doesn’t load it every time because most likely it will never change in this case (good example bc the codes up there already).

in my main config i have enabled this portion…




'cache' => array(

                    'class' => 'system.caching.CApcCache',

                ),

		'db'=>array(

			....

                        'schemaCachingDuration' => 86400,

		),

How would I add the cache to the products page or just to the product relation?