时间戳

首页 > 解决方案 > 如何使用 Yii 1.1 在 FROM 子句中执行子查询？

activerecord - 如何使用 Yii 1.1 在 FROM 子句中执行子查询？

问题描述

Yii 生成了一个相当大且复杂的查询。为了生成这个查询，我们利用CDbCritera::with热切加载多个相关模型，并且我们使用多个范围来限制返回的记录。生成的查询大约有 700 行长，但看起来像这样：

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

我们的数据库有大量的数据，但也不是淫秽的。在这种情况下，model表有大约 126000 行，每个“相关”模型都是一个BELONGS_TO关系，并且有一个索引，t.XXX_id因此连接相当简单。问题是 WHERE 子句的复杂性，拥有多个andCOALESCE子句。对我们的 126000 行执行过滤需要 2.6 秒——比我们希望的 API 端点长得多。IFCASE

WHERE 子句分为多个不同的部分，如下所示：

WHERE
    ( ... part 1 ... )
    AND
    ( ... part 2 ... )
    AND
    ( ... part 3 ... )

每个部分对应一个范围，每个部分使用一个或多个相关模型

其中一个作用域只过滤一个相关模型，这样做会将我们的表从 126000 行过滤到大约 2000 行。我通过实验（在 MySQL Workbench 中）发现，只需执行以下操作，我就可以将查询从 2.6 秒缩短到 0.2 秒：

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

这种方式不是对WHERE原始表的所有 126000 行执行非常复杂的子句，而是对这 126000 行model执行更简单（和索引增强）的子句，然后仅对 2000 相关行执行复杂子句。两个查询的结果是相同的，但是在子句中使用子查询会使其运行速度提高 13 倍。WHEREWHEREFROM

问题是，我不知道如何在 Yii 中做到这一点。我知道我可以使用它CDbCommand来构建查询，甚至可以传入原始 SQL，但我会得到一个“行”数组——Yii 不会理解它们并正确地转换为正确的模型。

Yii 的 ActiveRecord 系统有没有办法说类似下面的话？

$criteria = new CDbCriteria;
$criteria->scopes = array("part1");
$subQuery = Model::model()->buildQuery($criteria);

$criteria = new CDbCriteria;
$criteria->scopes = array("part2", "part3");
$fullQuery = $subQuery->findAll($criteria);

标签： activerecordyiiyii1.x

解决方案

虽然不是一个完美的解决方案，但我确实找到了几乎一样好的东西。将原始查询分成两部分：

  1. 获取您希望在 FROM 子查询中选择的 ID 或模型
  2. 将 WHERE 附加到外部查询id in (...)

如果有人有兴趣，我会寻找我为此编写的代码以作为示例发布在答案中，但到目前为止，这个问题很少受到关注，一旦我找到了一个伪体面的解决方案，我就会继续前进。

推荐阅读