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

Question

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

I want to search books and sort them by Reviews.

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

My SQL query is like following:

 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();

It's giving me following error message:

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

What am I missing here?

score 10 · Answer 1 · edited Feb 24 '16 at 09:39

10

Use joinWith. For more see

For example, for your case code like that:

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

EDIT: I find better solution.

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

edited Feb 24 '16 at 09:39

robsch

9,358
9
63
104

answered Apr 04 '15 at 17:41

vitalik_74

4,573
2
19
27

Thanks. But here, I need to count total reviews for each book and then sort the book list based on the reviews number. – The Coder Apr 04 '15 at 18:16
Hi, Thanks. I have updated the question. Would you please have a look into the updated one. Thanks. – The Coder Apr 12 '15 at 19:33

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

1 Answers1

Linked