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.
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_optiont WHERE attribute_id=1
SELECT * FROM attribute_optiont WHERE attribute_id=2
SELECT * FROM attribute_optiont 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?