php 如何在 Yii2 Active Record 中将 JOIN 用于关系模型？

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

提问by The Coder

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

我有 2 张表，称为 Books 和 Reviews。Books 表与 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.

例如，如果有 10 本书可用并且书在评论中有评论，那么我想使用 WHERE 子句查找所有书籍并计算评论，然后根据评论编号对所有书籍进行排序。

My SQL query is like following:

我的 SQL 查询如下所示：

 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'

SQLSTATE[42S22]：未找到列：1054 'order 子句'中的未知列 'cnt'

What am I missing here?

我在这里缺少什么？

回答by vitalik_74

Use joinWith. For more see

使用joinWith. 更多信息请参见

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