如何在Yii2 Active Record中使用JOIN建立关系模型?

本文介绍了如何在Yii2 Active Record中使用JOIN建立关系模型?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有2个表，分别称为书籍和评论".图书表与评论"具有一对多关系.

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

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

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

它给我以下错误消息:

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

我在这里想念什么?

解决方案

使用joinWith.有关更多信息，请请参见

例如，对于您的案例代码:

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

我找到更好的解决方案.

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

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