ActiveRecord where and order on via-table [英] ActiveRecord where and order on via-table

问题描述

我有三个数据库表:

产品(id，名称)

product_has_adv(产品、优势、排序、重要)

product_has_adv (product,advantage,sort,important)

优势(id，文本)

在 ProductModel 中我定义了这个:

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.

但现在我需要添加一个 where product_has_advantage.important = 1 子句，并通过 product_has_advantage-table 中的 sort-columen 对优势进行排序.

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?

推荐答案

对关系使用 via 和 viaTable 方法将导致两个单独的查询.

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

important 的过滤器将被应用，但排序不会，因为它发生在第一个查询中.因此，IN 语句中的 id 顺序将被更改.

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.

根据您的数据库逻辑，最好将 important 和 sort 列移动到 advantage 表.

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

这篇关于ActiveRecord where and order on via-table的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！