ActiveRecord where and order on via-table
Just for reference https://github.com/yiisoft/yii2/issues/10174
It's near impossible to ORDER BY viaTable()
columns.
For Yii 2.0.7 it returns set of ID's from viaTable()
query,
and final/top query IN()
clause ignores the order.
Using via
and viaTable
methods with relations will cause two separate queries.
You can specify callable in third parameter like this:
public function getAdvantages()
{
return $this->hasMany(AdvantageModel::className(), ['id' => 'advantage'])
->viaTable('product_has_advantage', ['product' => 'id'], function ($query) {
/* @var $query \yii\db\ActiveQuery */
$query->andWhere(['important' => 1])
->orderBy(['sort' => SORT_DESC]);
});
}
The filter by important
will be applied, but the sort won't since it happens in first query. As a result the order of ids in IN
statement will be changed.
Depending on your database logic maybe it's better to move important
and sort
columns to advantage
table.
Then just add condition and sort to the existing method chain:
public function getAdvantages()
{
return $this->hasMany(AdvantageModel::className(), ['id' => 'advantage'])
->viaTable('product_has_advantage', ['product' => 'id'])
->andWhere(['important' => 1])
->orderBy(['sort' => SORT_DESC]);
}
Using viaTable
methods with relations will cause two separate queries, but if you don't need link()
method you can use innerJoin in the following way to sort by product_has_advantage table:
public function getAdvantages()
{
$query = AdvantageModel::find();
$query->multiple = true;
$query->innerJoin('product_has_advantage','product_has_advantage.advantage = advantage.id');
$query->andWhere(['product_has_advantage.product' => $this->id, 'product_has_advantage.important' => 1]);
$query->orderBy(['product_has_advantage.sort' => SORT_DESC]);
return $query;
}
Note than $query->multiple = true
allows you to use this method as Yii2 hasMany relation.