php - Yii2-在GridView中按一对多关系进行排序和筛选

我有3个模型:PurchaseOrders，PurchaseOrderItems和Vendors。 PurchaseOrders可以将许多PurchaseOrderItem绑定(bind)在一​​起，并且每个PurchaseOrderItem可以具有一个与之关联的供应商。我的困境是，我不仅需要在PurchaseOrder GridView中显示供应商，还需要使该列能够被过滤和排序。我已经弄清楚了要获取与PurchaseOrder相关联的Vendors的MySQL查询，但是将其与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

PurchaseOrder模型:

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绑定(bind)到Vendors，以便像在MySQL查询中一样显示与之关联的Vendors？

编辑
忘记添加到原始问题，我在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), ", ");
    }
}

PurchaseOrder搜索模型:

<?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;
    }
}

PurchaseOrder Controller :

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

最后配置网格 View ，

<?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]) ?>

搜索 View :_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>