How to make a nested query in yii2?

Question

MySQL

On Habré, I found such an example of how you can insert a subquery inside a query

$subQuery = (new Query)->select('COUNT(*)')->from('user');
$query = (new Query)->select(['id', 'count' => $subQuery])->from('post');
// $query represents the following SQL:
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`

I can't figure out how to add a subquery to get

SELECT `id`, (SELECT COUNT(*) FROM `user` WHERE `user`.`id`=`post`.`autor_id`) AS `count` FROM `post`

Answer 1

Why do you need a nested query here?
Do a JOIN:

SELECT `post`.`id`, COUNT(`user`.`id`) AS `count`
FROM `post`
  LEFT JOIN `user` ON `user`.`id` = `post`.`autor_id`
GROUP BY `post`.`id`

In theory, in YII it should be like this:

$query = (new Query)
  ->select(['post.id', 'count' => 'COUNT(user.id)'])
  ->from('post')
  ->leftJoin('user', 'user.id = post.autor_id');
  ->groupBy(['post.id']);