I have a fairly large and complex query being generated by Yii. To generate this query we're utilizing CDbCritera::with to eagerly load multiple related models, and we're using multiple scopes to limit the records returned. The query being generated is roughly 700 lines long, but looks something like this:
SELECT `t`.`column1` as `t0_c0`,
`t`.`column2` as `t0_c1`,
`related1`.`column1` as `t1_c0`,
...
`related9`.`column5` as `t9_c4`
FROM `model` `t`
LEFT OUTER JOIN `other_model` `related1`
ON ( `t`.`other_model_id` = `related1`.`id` )
...
LEFT OUTER JOIN `more_models` `related9`
ON ( `t`.`more_models_id` = `related9`.`id` )
WHERE
...big long WHERE clause using all of related1 - related9 to filter model...
LIMIT 10
Our database has a not insignificant amount of data, but not obscene, either. In this case the model table has about 126000 rows, every "related" model is a BELONGS_TO relationship and there is an index on t.XXX_id so the join is fairly trivial. The problem is the complexity of the WHERE clause, possessing multiple COALESCE and IF and CASE clauses. Performing the filter on our 126000 rows is taking 2.6 seconds -- far longer than we would like for an API endpoint.
The WHERE clause is divided into multiple different sections like so:
WHERE
( ... part 1 ... )
AND
( ... part 2 ... )
AND
( ... part 3 ... )
With each part corresponding to one of the scopes, and each part using one or more related models
One of the scopes filters on only a single related model, and in doing so filters our table down from 126000 rows to about 2000 rows. I found experimentally (in MySQL Workbench) that I could get our query from 2.6 seconds to 0.2 seconds by simply doing this:
SELECT `t`.`column1` as `t0_c0`,
`t`.`column2` as `t0_c1`,
`related1`.`column1` as `t1_c0`,
...
`related9`.`column5` as `t9_c4`
FROM
(
SELECT `model`.*
FROM `model`
LEFT OUTER JOIN `other_model`
ON ( `t`.`other_model_id` = `other_model`.`id` )
WHERE
( ... part 1 ... )
) `t`
LEFT OUTER JOIN `other_model` `related1`
ON ( `t`.`other_model_id` = `related1`.`id` )
...
LEFT OUTER JOIN `more_models` `related9`
ON ( `t`.`more_models_id` = `related9`.`id` )
WHERE
( ... part 2 ... )
AND
( ... part 3 ... )
LIMIT 10
This way instead of performing the very complex WHERE clause on all 126000 rows of the original model table, we perform the much simpler (and index-enhanced) WHERE clause on these 126000 rows and then perform the complex WHERE clause on only the 2000 relevant rows. The results of the two queries are identical, but using a subquery in the FROM clause causes it to run 13x faster.
The problem is, I have no idea how to do this in Yii. I know that I can use CDbCommand to build a query and even pass in raw SQL, but what I'll get back is an array of "rows" -- they won't be understood by Yii and properly converted to the right models.
Does Yii's ActiveRecord system have a way to say something like the following?
$criteria = new CDbCriteria;
$criteria->scopes = array("part1");
$subQuery = Model::model()->buildQuery($criteria);
$criteria = new CDbCriteria;
$criteria->scopes = array("part2", "part3");
$fullQuery = $subQuery->findAll($criteria);
Although not a perfect solution, I did find something that's almost as good. Break the original query into two:
id in (...)If anyone is interested I'll hunt down the code I wrote for this to post in the answer as an example, but so far this question has gotten very little attention and once I found a pseudo-decent solution I've moved on.