How to display number of rows from linked table by key value?

Question

Yii

There is a table book and author. Linked through the id (author) and author_id (for the book) columns. You need to display the number of books for each author. Here is the code I wrote:

class AuthorController extends Controller {
    public function actionIndex() {
        $author = Author::find()->all();
        $id = Author::find()->select('id');
        $book = Book::find()->select(['author_id' => $id])->count();
        return $this->render('index', ['author' => $author, 'book' => $book, 'id' => $id]);   
    }
}

If in the Book::find() request I specify a specific value of the author's id instead of the $id variable, then I get the correct result, the actual number of author's books with this id in the database. But it doesn't work with a variable. In this form, as I indicated above, I get the total number of books in the database, and not the number of books by a particular author. Tried using foreach in the same controller:

foreach ($book as $author_id) {
$book = Book::find()->select(['author_id' => $author_id])->count();
}

At the output I get 1,1,1,1,.... - as many units as there are books in the database. I tried a bunch of other variations of using foreach - the result is the same: either an error, or a column of ones, or zeros, or the total number of books in the database as a column. I've already broken my head trying to figure out how to do it and what's wrong.
I put it in the view like this:

<?php if(!empty($author)); ?>
<?php foreach($author as $author): ?>
        <h3><a href="#"> <?=$author->name?> </a></h3>
        <h3><a href="#"> <?=$author->id?> </a></h3>
        <h3><a href="#"> <?=$book?> </a></h3>
<?php endforeach; ?>

author id and name are displayed correctly. I tried to display the number of books for the experiment in a separate cycle, the effect is the same.
Help out!

Answer 1

Try like this:

class AuthorController extends Controller {
    public function actionIndex() {
        $authors = Author::find()->select(['author.id', 'author.name', 'books' => 'count(*)'])
            ->leftJoin('book', 'author.id = book.author_id')
            ->groupBy('author.id')->asArray()->all();
        return $this->render('index', ['authors' => $authors]);   
    }
}

view.php

foreach($authors as $author){
  echo '<h2>'.$author['id'].') '.$author['name'].'</h2>'; // 1) Иван И.И.
  echo '<p>Книг: '.$author['books'].'</p>'; // Книг: 17
}