Yii2 - Sort and filter by one to many to one relation in GridView

Question

I have 3 models: PurchaseOrders, PurchaseOrderItems, and Vendors. PurchaseOrders can have many PurchaseOrderItems tied to each one, and each PurchaseOrderItem can have one Vendor associated with it. My dilemma, is that I need to not only show the Vendors in the PurchaseOrder GridView, but make that column capable of being filtered and sorted. I have figured out the MySQL query to get the Vendors associated with PurchaseOrder, but am at a loss in tying it all together with Yii2.

Query:

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 Model:

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 Model:

class PurchaseOrderItems extends \yii\db\ActiveRecord
{
    public function getVendor()
    {
        return $this->hasOne(Vendors::className(), ['id' => 'vendor_id']);
    }
}

Vendors Model:

class Vendors extends \yii\db\ActiveRecord 
{
    public function getPurchaseOrderItems()
    {
        return $this->hasMany(PurchaseOrderItems::className(), ['vendor_id' => 'id']);
    }
}

Is there a way to tie PurchaseOrders to Vendors, so that I can show Vendors associated with it like I'm doing in the MySQL query?

Edit Forgot to add to the original question, I had created a function within the PurchaseOrder model to list the Vendors, however; they are not sortable in the GridView this way.

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

Answer 1

With the below modifications, it should work fine.

PurchaseOrder model:

<?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 model:

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

And finally configure the grid 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]) ?>

Search 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>