我有三个模型：PurchaseOrders、PurchaseOrderItems和Vendor。PurchaseOrder可以有多个PurchaseOrderItem绑定到每个PurchaseOrderItem，每个Purchase OrderItem可以有一个与其关联的Vendor。我的困境是，我不仅需要在PurchaseOrder GridView中显示Vendor，还需要使该列能够被筛选和排序。我已经找到了MySQL查询来获取与PurchaseOrder相关联的Vendor，但无法将其与Yii2捆绑在一起。
查询：
SELECT pos.id, pos.notes, group_concat(distinct(vend.name) order by vend.name ASC SEPARATOR ', ' ) as vendorNames
FROM purchase_orders as pos
JOIN purchase_order_items as PO_item
ON pos.id = PO_item.purchase_order_id
JOIN vendors as vend
ON PO_item.vendor_id = vend.id group by pos.id
采购订单模型：
class PurchaseOrders extends 'yii'db'ActiveRecord
{
public static function tableName()
{
return 'intra_purchase_orders';
}
public function getPurchaseOrderItems() {
return $this->hasMany(PurchaseOrderItems::className(['purchase_order_id' => 'id']);
}
}
PurchaseOrderItems模型：
class PurchaseOrderItems extends 'yii'db'ActiveRecord
{
public function getVendor()
{
return $this->hasOne(Vendors::className(), ['id' => 'vendor_id']);
}
}
供应商型号：
class Vendors extends 'yii'db'ActiveRecord
{
public function getPurchaseOrderItems()
{
return $this->hasMany(PurchaseOrderItems::className(), ['vendor_id' => 'id']);
}
}
有没有一种方法可以将PurchaseOrders与Vendor联系起来，这样我就可以像在MySQL查询中那样显示与之相关的Vendor？
编辑忘记添加到最初的问题中，我在PurchaseOrder模型中创建了一个函数来列出供应商；它们不能以这种方式在GridView中进行排序。
public function getVendors() {
$vendor_arry = [];
foreach ($this->purchaseOrderItems as $key => $item) {
array_push($vendor_arry, $item->vendor->name);
}
sort($vendor_arry);
return implode(array_unique($vendor_arry, SORT_STRING), ", ");
}
经过以下修改，它应该可以正常工作。
采购订单模型：
<?php
class PurchaseOrders extends 'yii'db'ActiveRecord
{
public $vendor_name; //This is the variable that will be used for filtering
public function rules()
{
return [
[['vendor_name'], 'string'] //Specify the variable as string
];
}
public static function tableName()
{
return 'intra_purchase_orders';
}
public function getPurchaseOrderItems() {
return $this->hasMany(PurchaseOrderItems::className(['purchase_order_id' => 'id']);
}
public function getVendors() {
$vendor_arry = [];
foreach ($this->purchaseOrderItems as $key => $item) {
array_push($vendor_arry, $item->vendor->name);
}
sort($vendor_arry);
return implode(array_unique($vendor_arry, SORT_STRING), ", ");
}
}
PurchaseOrderSearch模型：
<?php
namespace {your_namespace};
use Yii;
use yii'base'Model;
use yii'data'ActiveDataProvider;
use app'models'PurchaseOrder;
class PurchaseOrderSearch extends PurchaseOrder
{
public function rules()
{
return [
[['vendor_name'], 'safe'],
];
}
public function scenarios()
{
return Model::scenarios();
}
public function search($params)
{
$query = PurchaseOrder::find()->joinWith(['purchaseOrderItems.vendor']);
$dataProvider = new ActiveDataProvider([
'query' => $query
]);
$dataProvider->sort->attributes['vendor_name'] = [
'asc' => ['vendor.name' => SORT_ASC],
'desc' => ['vendor.name' => SORT_DESC],
];
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
$query->andFilterWhere([
'id' => $this->id
]);
$query->andFilterWhere(['like', 'vendor.name', $this->vendor_name]);
return $dataProvider;
}
}
采购订单控制器：
class PurchaseOrderController extends Controller
{
public function actionIndex()
{
$searchModel = new PurchaseOrderSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);
return $this->render('index', [
'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
}
}
最后配置网格视图，
<?php
use yii'helpers'Html;
use app'extended'GridView;
?>
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
[
'header' => 'Vendors',
'attribute'=>'vendor_name',
'value' => function ($model, $key, $index) {
return $model->vendors;
},
]
],
]); ?>
<?= $this->render('_search', ['model'=>$searchModel]) ?>
搜索视图：_Search.php
<?php
use yii'helpers'Html;
use yii'widgets'ActiveForm;
?>
<div class="search-form">
<?php $form = ActiveForm::begin([
'action' => ['index'],
'method' => 'get',
]); ?>
<?= $form->field($model, 'vendor_name') ?>
<div class="form-group actions">
<?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
</div>
<?php ActiveForm::end(); ?>
</div>