Yii2. How to sort results by attributes in junction table?

Question

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
• enabled
• sorting_order

I want to get all messages for specific page sorting by sp_messages_pages.sorting_order using Yii2 framework.

I try this code in pages class:

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

This give me all messages sorted by sp_message.id. The generated SQL is

SELECT * FROM sp_messages WHERE id IN ('2', '3', '1', '4', '5')

IN condition is sorted as I want, but how to sort sp_messages?

Answer 1

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

almost the the same question here: Yii2 order items of many-to-many relation

Other way -- using 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]);

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.