CDetailView Queries

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

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.

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?

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