Yii2使用ActiveQuery，该ActiveQuery在同一模型上使用子查询

Question

我有一个Comments表，该表具有指向自身的parent_id外键，以启用线程注释。

Comments
id INT UNSIGNED NOT NULL
parent_id INT UNSIGNED NULL
comment TEXT NOT NULL
created_time DATETIME NOT NULL

原来的ActiveQuery就像这个

class CommentActiveQuery extends yiidbActiveQuery {
    public function andWhereIsNotRemoved() {
        return $this->andWhere(['isRemoved' => Comment::STATUS_IS_NOT_REMOVED]);
    }
    public function andWhereParentIdIs($parentId) {
        return $this->andWhere(['parentId' => $parentId]);
    }
    public function orderByNewestCreatedTime() {
        return $this->orderBy(['createdTime' => SORT_DESC]);
    } 
}

现在我想根据最新的活动回复对评论进行排序。

查询基本上与类似

SELECT `*`, `last_reply_time` 
    FROM `Comments` 
    WHERE `parent_id` IS NULL 
    ORDER BY `last_reply_time` DESC;

我认为last_reply_time是一个子查询

SELECT MAX(created_time) 
   FROM `Comments` 
   WHERE `parent_id` = :something

如何使用上面的CommentActiveQuery构建它。我能得到的最远的就是这个

public function orderByNewestActiveChildCreatedTime() {
    return $this->addSelect([
            'last_reply_time' => $subQuery
           ])->orderBy(['last_reply_time' => SORT_DESC]);
} 

我应该用什么来替换上面的$subQuery变量？或者有更好的方法吗？

Answer 1

$subQuery = new yiidbQuery();
$subQuery->select(["max(subQ.created_time)"]);
$subQuery-> from('comments subQ');
$subQuery->where([
    'parent_id' => $parentId
]);
$query = new yiidbQuery();
$query->select([
    'C.*',
    $subQuery
]);
$query->from('Comments C');
$query->andWhere(
    'parent_id is Null'
);

$command = $query->createCommand();
 /*
   Printing the command will result following sql  with $parentId = 1     
   SELECT
        `C`.*, (
         SELECT
            max(subQ.created_time)
         FROM
            `comments` `subQ`
         WHERE
             `parent_id` =: qp0
         ) AS `1`
    FROM
         `Comments` `C`
    WHERE
         parent_id IS NULL
*/
print_r($command->sql);

我不确定按last_reply_time DESC订购；假设last_reply_time是数据库表属性，并且与子查询不同。您可以简单地通过执行$query->orderBy('last_reply_time DESC'(在查询中添加orderBy

我希望这对你有帮助。干杯：(