如何在Yii2 Active Record中使用JOIN建立关系模型? [英] How to use JOIN in Yii2 Active Record for relational model?
问题描述
我有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();
这篇关于如何在Yii2 Active Record中使用JOIN建立关系模型?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!