Order items of many-to-many relation

Yii 2.0 General Discussions
1

Hi all,

I’ve 2 tables (sliders, images) related with junction (sliders_images) table, the relation work fine but I nedd to get related data with specific order, the attribute that define the right order is in the junction table, the relation is defined as:


public function getImages(){

    return $this->hasMany(Images::className(), ['id' => 'image_id'])

        ->viaTable('sliders_images', ['slider_id' => 'id'], function($query){

            $query->orderBy('sliders_images.display_order ASC');

        });

}

when i call


$model->images

I receive the correct images but the wrong order, using foreach the images was ordered by id, how i can get the images ordered by other attribute?

2

This might work:


public function getImages(){

    return $this->hasMany(Images::className(), ['id' => 'image_id'])

        ->viaTable('sliders_images', ['slider_id' => 'id'])

        ->orderBy('sliders_images.display_order ASC');

}
3

Hi, thanks for the help!!!

This way don’t work, the error is:


SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sliders_images.display_order' in 'order clause'

The SQL being executed was: SELECT * FROM `images` WHERE `id` IN ('36', '35', '38', '37') ORDER BY `sliders_images`.`display_order`

I think because the query for get images was performed on images table directly and display_order is an attribute of junction table, I can suppose that the many to many relation is a result of multiple querys the last is directly on related table and by this way the attributes of junction table are lost but, at the same time, i consider this behavior strange

4

With the help of stackoverflow (the full question) I’ve found the right way to resolve my problem, the code:




public function getImages(){

        return $this->hasMany(Images::className(), ['id' => 'image_id'])

        ->via('slidersImages')

        ->joinWith('slidersImages SI')

        ->orderBy('SI.display_order ASC');

}

Thanks for the support.