query performance in relations

I have 2 tables :

table Location and table LocationSub

Location :




	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'pkLocSub' => array(self::HAS_MANY, 'LocationSub', 'loc_no'),

		);

	}



LocationSub




	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'fkLoc' => array(self::BELONGS_TO, 'Location', 'loc_no'),

		);

	}



in CGridview :




<?php 

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

	'id'=>'locsub-grid',

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

	'filter'=>$model,

	'columns'=>array(

		array(

			'header'=>'No.',

			'value'=>'$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize + $row+1',       //  row is zero based

			'htmlOptions'=>array('width'=>'25'),

    ),

    

		'fkLoc.loc_name',


	),

)); ?>



When I trace in DB Query, I got query as I attached

is it Normal? I think it should be one query simple Inner Join?

Anyone Idea?

More details (such as how you construct your dataprovider, and your database structure) might help.

I assume that you have the normal database structure with a location table, and a sublocation table that contains a foreign key referencing the location table. I also assume, given the parameters you are using, that your $model is created from the LocationSub model, so all those extra queries you are seeing are a result of listing sublocations, then lazy loading the appropriate location for each one in order to output the name.

In this case, as far as I know there is no standard way to use eager loading instead, since you are using the model search method in order to filter, but it should be fairly easy to modify the model search method either to hard-code eager loading for certain attributes when searching, or to add an optional CDbCriteria parameter to be passed to the search method, which is then merged with the search parameters. You could also of course modify the relationship in the LocationSub model to add a ‘with’ criteria to the relationship, but this would mean that the parent Location would be appended to LocationSub in whatever context it is used in, even if not needed.

I would suggest you modify your search method, which probably looks a bit like this:


    /**

     * Retrieves a list of models based on the current search/filter conditions.

     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

     */

    public function search()

    {

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

        // should not be searched.


        $criteria=new CDbCriteria;


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


        return new CActiveDataProvider(get_class($this), array(

            'criteria'=>$criteria,

        ));

    }

to something like this:


    /**

     * Retrieves a list of models based on the current search/filter conditions.

     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

     */

    public function search($extraCriteria = new CDbCriteria)

    {

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

        // should not be searched.


        $criteria=new CDbCriteria;


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


        $criteria->mergeWith($extraCriteria);


        return new CActiveDataProvider(get_class($this), array(

            'criteria'=>$criteria,

        ));

    }

This will allow you to pass extra criteria to your search, so in this case, in order to eager load the parent Locations rather than lazy load, you would change your dataProvider for the CGridView to

‘dataProvider’ => $model->search(new CDbCriteria(array(‘with’ => ‘fkLoc’))),

Hopefully this should help you out…

Thanks,

in function search, I add :




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



After that, cost query became lower dan when I trace DB query show 1 query :




Querying SQL: SELECT `t`.`loc_no` AS `t0_c0`, `t`.`loc_sub_no` AS `t0_c1`, `t`.`loc_sub_name` AS `t0_c2`, `t`.`loc_sub_address` AS `t0_c3`, `t`.`loc_sub_volume` AS `t0_c4`, `t`.`uom_code` AS `t0_c5`, `t`.`loc_sub_picture` AS `t0_c6`, `t`.`loc_sub_lat` AS `t0_c7`, `t`.`loc_sub_long` AS `t0_c8`, `t`.`created_by` AS `t0_c9`, `t`.`created_date` AS `t0_c10`, `t`.`lastupdated_by` AS `t0_c11`, `t`.`lastupdated_date` AS `t0_c12`, `fkLoc`.`loc_no` AS `t1_c0`, `fkLoc`.`loc_name` AS `t1_c1`, `fkLoc`.`loc_address` AS `t1_c2`, `fkLoc`.`loc_picture` AS `t1_c3`, `fkLoc`.`loc_lat` AS `t1_c4`, `fkLoc`.`loc_long` AS `t1_c5`, `fkLoc`.`created_by` AS `t1_c6`, `fkLoc`.`created_date` AS `t1_c7`, `fkLoc`.`lastupdated_by` AS `t1_c8`, `fkLoc`.`lastupdated_date` AS `t1_c9` FROM `location_sub` `t` LEFT OUTER JOIN `location` `fkLoc` ON (`t`.`loc_no`=`fkLoc`.`loc_no`) LIMIT 50