How to convert mysql query to yii code?

Question

Yii

Who can help me convert mysql query to yii code

SELECT author.name as author, count(*) AS quantity
FROM book 
INNER JOIN author on book.id_author = author.id_author
GROUP BY author.id_author

Book Controller

<?php
namespace app\controllers;
use yii\web\Controller;
use yii\data\Pagination;
use app\models\Book;
use app\models\Author;

class SortController extends Controller {
  public function actionSort() {
    $years = Book::find()
      ->where('year>=1901')
      ->all();
      
    $query = "SELECT author.name as author, count(*) AS quantity
              FROM book 
              INNER JOIN author on book.id_author = author.id_author
              GROUP BY author.id_author";
    $books = Author::findBySql($query)->all();

    return $this->render('sort', [
      'years' => $years,
      'books' => $books,
    ]);
  }
}

Model Author

<?php
namespace app\models;
use yii\db\ActiveRecord;

class Author extends ActiveRecord {
  public function getGenre() {
    return $this->hasOne(Genre::className(), ['id_genre' => 'id_genre']);
  }

  public function getBooks() {
    return $this->hasMany(Book::className(), ['id_author' => 'id_author']);
  }
}

Book Model

<?php
namespace app\models;
use yii\db\ActiveRecord;

class Book extends ActiveRecord {
  public function getAuthors() {
    return $this->hasOne(Author::className(), ['id_genre' => 'id_genre']);
  }
}

База Данных 

<img src="https://habrastorage.org/webt/5e/a6/ef/5ea6efddd8925155899839.jpeg" alt="image"/>

Answer 1

I got it like this

<?php
namespace app\controllers;
use yii\web\Controller;
use yii\data\Pagination;
use app\models\Book;
use app\models\Author;

class SortController extends Controller {
  public function actionSort() {
    $years = Book::find()
      ->where('year>=1901')
      ->all();
      
    $books = Book::find()->all();

    $books = $books->select('author.name as author, COUNT(*) AS quantity')
      ->form('book')
      ->joinWith('author on book.id_author = author.id_author')
      ->groupBy('author.id_author');

    var_dump($books);

    return $this->render('sort', [
      'years' => $years,
      'books' => $books,
    ]);
  }
}

But it gives an error "all to a member function select() on array" in this line

$books = $books->select('author.name as author, COUNT(*) AS quantity')