yii2 - ActiveRecord where and order on via-table

Question

I have three database table:

product (id, name)

product_has_adv (product,advantage,sort,important)

advantage (id, text)

In ProductModel I defined this:

public function getAdvantages()
    {
        return $this->hasMany(AdvantageModel::className(), ['id' => 'advantage'])
            ->viaTable('product_has_advantage', ['product' => 'id']);
    }

I get the advantages without any problems.

But now I need to add a where product_has_advantage.important = 1 clausel and also sort the advantages by the sort-columen in the product_has_advantage-table.

How and where I have to realize it?

Answer 1

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 yiidbActiveQuery */

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