Yii2 Activerecord从联结表中获取字段并根据它们进行排序 [英] Yii2 Activerecord get fields from junction table and order by according to them
问题描述
我有个项目和个单位表，它们之间存在多对多的关系。换句话说，该项目具有许多单位，而该单位具有许多项目。我通过 junction 表 item_units 管理该关系。联结表除了 item_id 和 unit_id 还有一些额外的字段，即它具有价格和重量（它是一个整数，以管理用于显示目的的每个项目的单位顺序。）
I have items and units table that have many to many relationship. In other words, the item has many units and the unit has many items. I managed the relation through a junction table item_units. The junction table has some extra field more than item_id and unit_id, i.e it has price, and weight (it is an integer to manage the order of units for each item for display purposes).
我按以下方式管理模型中的关系：
I managed the relations in the models as follows:
//In Items model
/**
* @return \yii\db\ActiveQuery
*/
public function getItemUnits()
{
return $this->hasMany(ItemUnits::className(), ['item_id' => 'id'])->orderBy(['item_units.weight' => SORT_DESC]);
}
public function getUnits()
{
return $this->hasMany(Units::className(), ['id'=> 'unit_id'])->select(['id','title'])->via('itemUnits');
}
//
//In Units model
public function getItemUnits()
{
return $this->hasMany(ItemUnits::className(), ['unit_id' => 'id'])->orderBy(['price' => SORT_DESC]);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getItems()
{
return $this->hasMany(Items::className(), ['id' => 'item_id'])->via('itemUnits');
}
//
//In ItemUnits model
public function getItem()
{
return $this->hasOne(Items::className(), ['id' => 'item_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getUnit()
{
return $this->hasOne(Units::className(), ['id' => 'unit_id']);
}
在控制器中，我能够获取所有相关单位的数据
In the controller I'm able to get the data of all related units to an item by something like the following:
$item = Items::findOne($id);
return Json::encode($item->units);
以下是获得的JSON对象的演示：
The following is a demo of the JSON object obtained:
[{"id":"4","title":"قرص"},{"id":"5","title":"شريط 10"},{"id":"6","title":"علبة 2 شريط"}]
但是，我无法根据 item_units 表中的 weight 字段对结果进行排序，而且我无法能够在-JSON Object-上方的演示结果中包含 price 字段。
However, I could not able to order the results according to the weight field in item_units table and also I could not able to include the price field there in the demo result above -JSON Object-.
我只能得到 item_units 中的数据作为单独结果，如下所示：
I only able to get data in item_units as a separate result like the following:
return Json::encode($item->itemUnits);
更新
根据两个答案（@АлександрШалаев& @ Onedev.Link），我在 Units 模型中覆盖了 fields 方法，如下所示：
Update
According to the two answers (@Александр Шалаев & @Onedev.Link) , I have overridden the fields method in Units model as follows:
public function fields() {
parent::fields();
return [
'price' => function($model){
return $model->id; //Here I could not able to get the corresponding price field value from item_units -junction table-
},
'id',
'title',
];
}
但是，我无法获得的价格联接表中的字段值（临时），我将其设置为当前模型ID，以防止产生错误。另外，我仍然没有任何办法通过使用该联结表中的 weight 字段设置订单。
However, I could not able to get the price field value from the junction table, temporary, I set it to current model id to prevent error generation. Also, I still has no any mean to set order by using weight field in that junction table.
换句话说，Yii2 Activerecords如何执行以下SQL查询：
SELECT units.id UnitID, units.title Unit, iu.weight, iu.price
FROM units
Left JOIN item_units AS iu
ON iu.item_id = 1 AND iu.unit_id = units.id
WHERE
units.id = iu.unit_id
ORDER BY iu.weight;
推荐答案
最后我找到了解决方案。它取决于 findBySql 方法。我将使用在更新2 -中考虑的上述SQL查询，只是我删除了一些适合于当前任务的选定字段-。
Finally I have found a solution. It depends on findBySql method. I'm going to use the above SQL query regarded in Update 2 -just I have removed some selected fields to be suitable for my current task-.
public function actionUnitsJson($id){
$sql = 'SELECT units.id, units.title
FROM units
Left JOIN item_units AS iu
ON iu.item_id = :id AND iu.unit_id = units.id
WHERE
units.id = iu.unit_id
ORDER BY iu.weight DESC;';
$units = \common\models\Units::findBySql($sql,[':id' => $id])->asArray()->all();
return Json::encode($units);
}
这篇关于Yii2 Activerecord从联结表中获取字段并根据它们进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
