如何用cakephp-3方式编写此查询？

Question

原始sql查询：

SELECT *
FROM
  (SELECT p.id,
          p.title,
          p.mark,

     (SELECT max(created)
      FROM comments c
      WHERE c.post_id=p.id
        AND c.mark=1) AS latest_at
   FROM posts p) AS Post
WHERE Post.latest_at IS NOT NULL
ORDER BY latest_at DESC LIMIT 10

我有两张桌子：

$postsTable = TableRegistry::get('Posts');
$comments = TableRegistry::get('Comments');

我已经用cakephp-3方式进行了内部查询：

SELECT p.id,
       p.title,
       p.mark,
       (SELECT max(created)
          FROM comments c
          WHERE c.post_id=p.id AND c.mark=1) AS latest_at
       FROM posts p

Cakephp-3内部sql与cakephp-3 querybuilder(下面的查询正确吗？)：

        $subquery = $comments->find();
        $subquery->select([$subquery->func()->max('created')])
                ->from(['c'=>'comments'])
                ->where(['c.post_id=p.id','mark=1']);

        $pquery = $postsTable->find()
        ->select(['p.id','p.title','p.mark','latest_at'=>$subquery])
        ->from(['p'=>'posts']);

如何用cakephp-3方式编写外部选择查询？(从(派生表)中选择*)

提前谢谢。如有任何答复，将不胜感激。

Answer 1

要直接回答您的问题，以下是如何在另一个查询的from部分中使用子查询：

$query = $this->MyTable->connection()->newQuery();
$query->select('*')->from(['Post' => $subqueryObject])->fetchAll('assoc');

这将返回结果数组，如果您知道它应该水化到哪种类型的实体，那么执行以下操作：

$query = $this->Posts
    ->find()
    ->select($listOfFields)
    ->from(['Posts' => $subqueryObject], true)
    ->toArray();

Answer 2

对于一个非常简单的问题，这是一个非常复杂的解决方案(即用最新的评论获得帖子)。

更好的查询是：

 select posts.* from posts join comments on (comments.post_id = posts.id) group by posts.id order by comments.created desc limit 1

执行此查询的ORM方法是：

$post = $postsTable
    ->find()
    ->select(['Posts.id', 'Posts.title', 'Posts.mark', 'Comments.created'])
    ->leftJoinWith('Comments', function ($q) {
        return $q->where(['Comments.mark' => true]);
    }))
    ->group(['Posts.id'])
    ->order(['Comments.created' => 'desc'])
    ->first();

Answer 3

对于特殊情况，cakephp允许您编写原始SQL并保持cakephp方式。只需在您的模型中创建一个函数，您就可以通过将其放在$this->query("...")中，以其方式编写该查询。

示例(在模型中)：

public function getPosts(){
      $this->query("SELECT * FROM Posts AS Post"); //put complext query here
}

然后在主计长：

$posts = $this->Post->getPosts();