Yii2.如何按联结表中的属性对结果排序?

问题描述

我有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.如何按联结表中的属性对结果排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！