CDetailView Queries

Yii 1.1 General Discussion
1

Perhaps I am doing this incorrectly, but I’ve inspected the SQL queries being generated for my CDetailView and there seems to be lots of additional queries which I think could be reduced. This is what I have:

Tables:


User

====

id

name

etc...




Physical

========

id

user_id

height

weight

hair_colour

eye_colour

skin_colour

etc...




Attribute_Option

================

id

attribute_id

label

value

In the Physical table only an integer value is stored. Each field in the Physical table corresponds to an attribute_id in the Attribute_Option table.

User Model relation:


'physical'=>array(self::HAS_ONE, 'Physical', 'user_id'),

Physical Model functions:


public function getHeightOptions()

{

	return CHtml::listData(AttributeOption::model()->findAll(array('condition'=>'attribute_id=1')), 'value', 'label');

}

	

public function getHeight()

{

	return array_key_exists($this->height, $this->HeightOptions) ? $this->HeightOptions[$this->height] : $this->height=null;

}

CDetailView:


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

	'data'=>$user->physical,

	'attributes'=>array(

		array(

			'name'=>'height',

			'value'=>$user->physical->Height,

		),

	),

)); ?>

Due to the ‘value’ option being specified in CDetailView it seems to generate two additional queries:

SELECT * FROM attribute_option t WHERE attribute_id=1

SELECT * FROM attribute_option t WHERE attribute_id=1

2

You should use a singleton for avoid double querying.

Define a property in the model




private $_heightOptions= null;

And change the get function:




public function getHeightOptions()

{

     if ($this->_heightOptions===null)

        $this->_heightOptions =CHtml::listData(AttributeOption::model()->findAll(array('condition'=>'attribute_id=1')), 'value', 'label');

     return $this->_heightOptions;

}

This will make so the query will be fired only once, and not all time that you call the function.

3

Thanks man. I’ve never used that before - I suppose you learn something new all the time.

Now following on, ‘height’ is not the only field - there are about 15 fields in the Physical table. This means there are 15 similar queries being generated for each field, for example:

SELECT * FROM attribute_option t WHERE attribute_id=1

SELECT * FROM attribute_option t WHERE attribute_id=2

SELECT * FROM attribute_option t WHERE attribute_id=3

In addition the following parent query is generated:


SELECT `physical`.`id` AS `t1_c0`,

`physical`.`user_id` AS `t1_c1`,

`physical`.`height` AS `t1_c2`,

`physical`.`weight` AS `t1_c3`,

`physical`.`hair_colour` AS `t1_c4`,

`physical`.`eye_colour` AS `t1_c6`,

`physical`.`skin_colour` AS `t1_c7`

FROM `physical` `physical`

WHERE (`physical`.`user_id`=:ypl0).

Bound with :ypl0='1'

Any way all this can be combined so there is just one big query?

4

Well, you used this stuff before, is how works the load model in the controller.

About your question, maybe you can reformulate the function in order to have this signature:




public function getOption($attributeId)

But I am not sure is easy to do with your db structure