How to use JOIN in YII2 Active Record for relational model?

Question

I have 2 tables called Books and Reviews. The table of books has a one-to-many relationship with Reviews.

I want to find books and sort them by Reviews.

For example, if 10 books are available and the books are reviewed in Reviews, I want to find all the books using the WHERE clause and counting the reviews there, and then I will order all the books based on the review number.

My SQL query is as follows:

Books::find() ->where([ 'and', ['like', 'books.bookName', $bookName], ['like', 'books.status', 'Enabled'] ]) ->joinWith(['reviews' => function ($q){ $q->select(['COUNT(*) as cnt']); }]) ->orderBy(['cnt' => 'DESC']) ->all(); 

This gives me the following error message:

SQLSTATE [42S22]: Column not found: 1054 Unknown column 'cnt' in 'order clause'

What am I missing here?

Answer 1

Use joinWith . For more see

For example, for your case code:

 Books::find() ->joinWith(['reviews' => function ($q) { $q->select(['COUNT(*) as cnt']); }]) ->orderBy(['cnt' => 'DESC']) ->all(); 

EDIT: I find the best solution.

 Books::find() ->joinWith(['reviews']) ->select(['*', 'COUNT(reviews.*) as cnt']) ->groupBy('RELATION_FIELD(Example: reviews.book_id)') ->orderBy(['cnt' => 'DESC']) ->all();