Yii2 order items of many-to-many relation

问题:

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?

回答1:

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');
}

回答2:

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