How to properly style a subquery in YII2?

Question

Yii

I can't figure out how to format the subquery correctly.

public function actionIndex() {
    
    $query =  new \yii\db\Query();
    $subQuery = new \yii\db\Query();
    
    $comments_count = $subQuery->select('comments.deadline_id ')
      ->where(['comments.deadline_id' =>  'deadline.id'])
      ->from('comments')
      ->count();
    
    $task = $query->select(['deadline.id','deadline.text','deadline.status','deadline.deadline_date','comments_count' => $comments_count])
      ->where(['deadline.status' =>  1])
      ->from('deadline')
      ->all(); 
    
  //  $task = $query->createCommand()->sql;var_dump($task);die();
    
    $taskUncheked = $query->select(['deadline.id','deadline.text','deadline.status','deadline.deadline_date','comments_count' => $comments_count])
      ->where(['deadline.status' =>  '0'])
      ->from('deadline')
      ->orderBy(['deadline.deadline_date' => SORT_ASC])
      ->all();
        
    return    $task + $taskUncheked;
  }

Unfortunately comments_count returns zero. Though in a DB simply the request fulfills normally. Most likely deadline.id is not visible from the subrequest.

Answer 1

$task = $query1->select(['deadline.id', 'deadline.text', 'deadline.status', 'deadline.deadline_date', 'COUNT(`comments`.`deadline_id`) AS comments_count'])
                ->from('deadline')
                ->join('LEFT JOIN', 'comments', '`deadline`.`id` = `comments`.`deadline_id`')
                ->where('`deadline`.`status` = 1')
                ->groupBy('deadline.id')
                ->all();

Answer 2

$comments_count = $subQuery->select('comments.deadline_id ')
      ->where(['comments.deadline_id' =>  'deadline.id'])
      ->from('comments')
      ->count();

deadline.id is the name of a column from another table, if you use another table in the query to the comments table, then you must connect it using JOIN
in the same implementation. You tell the database "count the number of comments by deadline.id" but do not tell it what is deadline.id