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

我有 2 个表，名为 Books 和 Reviews.Books 表与 Reviews 是一对多的关系.

我想搜索书籍并按评论对它们进行排序.

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

我的 SQL 查询如下:

 Books::find()->哪里(['和'，['like', 'books.bookName', $bookName],['like', 'books.status', 'Enabled']])->joinWith(['reviews' => function ($q){$q->select(['COUNT(*) as cnt']);}])-> orderBy(['cnt' => 'DESC'])-> 全部();

它给了我以下错误消息:

<块引用>

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

我在这里遗漏了什么?

解决方案

使用 joinWith.更多见

例如，对于这样的案例代码:

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

我找到了更好的解决方案.

书籍::find()->joinWith(['评论'])->select(['*', 'COUNT(reviews.*) as cnt'])->groupBy('RELATION_FIELD(例如:reviews.book_id)')-> orderBy(['cnt' => 'DESC'])-> 全部();

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?

解决方案

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