Yii2 order items of many-to-many relation

Question

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?

score 3 · Answer 1 · edited Jul 14 '16 at 08:29

3

The right way is to add a join with junction table ->joinWith('slidersImages') for get the junction table attributes and then order by one of those. The full code:

public function getImages(){
        return $this->hasMany(Images::className(), ['id' => 'image_id'])
        ->via('slidersImages')
        ->joinWith('slidersImages SI')
        ->orderBy('SI.display_order ASC');
}

edited Jul 14 '16 at 08:29

oakymax

1,454
1
14
21

answered Jul 14 '16 at 08:04

MarBer

535
1
5
22

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

In this case orderBy will be applied to the first query (SELECT * FROM sliders_images ...), so if you'll check generated sql:

echo $model->getImages()->createCommad()->rawSql;

then you will see something like this

SELECT * FROM images WHERE id IN (4, 2, 3, 1)

and here will be correct order. But in resulting set you got order by id (1, 2, 3, 4).

So to get right order you should add desired orderBy to your hasMany:

public function getImages(){
  return $this->hasMany(Images::className(), ['id' => 'image_id'])
    ->viaTable('sliders_images', ['slider_id' => 'id'])
    ->orderBy(['images.display_order' => SORT_ASC]);
}

Related topic: ActiveRecord where and order on via-table

edited May 23 '17 at 11:58

Community

1
1

answered Jul 14 '16 at 07:17

oakymax

1,454
1
14
21

Korshunow thanks for replay, your suggestion isn't the right way but open my mind to find the solution. The table images has no display_order attribute, this is a junction table attribute. For have the junction table attribute in the query I need a `joinWith()` statement. Follows the right answer. – MarBer Jul 14 '16 at 08:00
1

Oh, missed that ) You right. Actually i always prefer to declare separated AR entities for every table and deal with `via` and `joinWith` instead of direct table references in relations and queries. This approach leads to more clean, flexible and sustainable code IMHO. So, definitely, you on the right way )) – oakymax Jul 14 '16 at 08:05

Yii2 order items of many-to-many relation

2 Answers2

Linked