问题描述
我有3个表:sp_pages
,sp_messages
和sp_messages_pages
(用于多对多关系).sp_messages_pages
表具有5列:
I have 3 tables: sp_pages
, sp_messages
and sp_messages_pages
(for many-to-many relations).The sp_messages_pages
table have 5 columns:
- id
- page_id
- message_id
- 已启用
- sorting_order
我想使用Yii2框架通过sp_messages_pages.sorting_order
获取用于特定页面排序的所有消息.
I want to get all messages for specific page sorting by sp_messages_pages.sorting_order
using Yii2 framework.
我在pages
类中尝试此代码:
public function getMessages() {
return $this->hasMany(Messages::className(), ['id' => 'id_messages'])
->viaTable('sp_messages_pages', ['id_pages' => 'id'], function ($query) {
$query->andWhere(['enabled' => 'Yes'])
->orderBy(['sp_messages_pages.sorting' => SORT_ASC]);
});
}
我在控制器中使用以下代码:
I use this code in my controller:
$this->findModel(['slug' => $slug])->getMessages();
这给我所有按sp_message.id
排序的消息.生成的SQL是
This give me all messages sorted by sp_message.id
. The generated SQL is
IN
条件根据需要进行排序,但是如何对sp_messages
进行排序?
IN
condition is sorted as I want, but how to sort sp_messages
?
推荐答案
第一种方法-将关系表连接到已选择的messages
First way -- joining relation table to already selected messages
$this->hasMany(Messages::className(), ['id' => 'id_messages'])
->viaTable('sp_messages_pages', ['id_pages' => 'id'],
function ($query) {
$query->andWhere(['enabled' => 'Yes']);
}
)
->leftJoin('sp_messages_pages MP', 'MP.id_messages = sp_messages.id')
->orderBy(['MP.sorting' => SORT_ASC]);
此处几乎是相同的问题: Yii2的订单项多对多关系
almost the the same question here: Yii2 order items of many-to-many relation
其他方法-使用findBySql
$sql = "
SELECT m.* FROM sp_messages AS m
JOIN WITH sp_messages_pages AS mp ON mp.id_messages = m.id
JOIN WITH sp_pages AS p ON p.id = mp.id_pages
WHERE mp.enabled = 'Yes' AND p.id = :page_id
ORDER BY mp.sorting ASC
";
return Messages::findBySql($sql, [':page_id' => $this->id]);
,但这不是Yii therms中的关系,只是一个返回ActiveQuery
进行进一步工作的方法.例如,您可以使用$page->getMessages()->all()
这样的方法.
but this is will be not a relation in Yii therms, just an method which returns ActiveQuery
for further work. You can use such method as $page->getMessages()->all()
for example.
这篇关于Yii2.如何按联结表中的属性对结果排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!