PHP、Yii2 GridView 过滤关系值

PHP, Yii2 GridView filtering on relational value

提问by Mr Goobri

Following on from this:

继此之后：

Yii2 how does search() in SearchModel work?

Yii2 SearchModel 中的 search() 是如何工作的？

I would like to be able to filter a GridViewcolumn of relational data. This is what I mean:

我希望能够过滤一GridView列关系数据。这就是我的意思：

I have two tables, TableAand TableB. Both have corresponding models generated using Gii. TableAhas a foreign key to a value in TableB, like this:

我有两张桌子，TableA和TableB。两者都有使用 Gii 生成的相应模型。TableA有一个值 in 的外键TableB，像这样：

TableA
attrA1, attrA2, attrA3, TableB.attrB1

TableB
attrB1, attrB2, attrB3

attrA1 and attrB1 are the primary keys of their corresponding tables.

attrA1 和 attrB1 是它们对应表的主键。

Now, I have a Yii2 GridViewof attrA2, attrA3and attrB2. I have a working filter on attrA2and attrA3so that I can search on column values. I also have a working sort for these two columns too - by just clicking on the column header. I would like to be able to add this filtering and sorting on attrB2too.

现在，我有一个Yii2GridView的attrA2，attrA3和attrB2。我有一个工作过滤器attrA2，attrA3以便我可以搜索列值。我也对这两列进行了工作排序 - 只需单击列标题即可。我也希望能够添加此过滤和排序attrB2。

My TableASearchmodel looks like this:

我的TableASearch模型看起来像这样：

public function search($params){
    $query = TableA::find();
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

    $this->addCondition($query, 'attrA2');
    $this->addCondition($query, 'attrA2', true);
    $this->addCondition($query, 'attrA3');
    $this->addCondition($query, 'attrA3', true);

    return $dataProvider;
}

In my TableAmodel, I set the related value like this

在我的TableA模型中，我设置了这样的相关值

    public $relationalValue;

public function afterFind(){
    $b = TableB::find(['attrB1' => $this->attrB1]);
    $this->relationalValue = $b->relationalValue;
}

Although it is probably not the best way of doing this. I think I have to use $relationalValue somewhere in my search function but I'm not sure how. Similarly, I would like to be able to sort by this column too - just like I can for attrA2and AttrA3by clicking on the header link`. Any help would be appreciated. Thanks.

虽然这可能不是最好的方法。我想我必须在搜索功能的某个地方使用 $relationalValue 但我不确定如何使用。同样，我希望能够通过此列也进行排序-就像我可以为attrA2和AttrA3通过点击标题link`。任何帮助，将不胜感激。谢谢。

采纳答案by iltaf khalid

Filtering a gridview by a column is damn easy in Yii 2.0. Please add the filter attribute to a gridview column having lookup values, as under:

在 Yii 2.0 中按列过滤 gridview 非常容易。请将过滤器属性添加到具有查找值的 gridview 列，如下所示：

[
        "class" => yii\grid\DataColumn::className(),
        "attribute" => "status_id",
        'filter' => ArrayHelper::map(Status::find()->orderBy('name')->asArray()->all(), 'id', 'name'),
        "value" => function($model){
            if ($rel = $model->getStatus()->one()) {
                return yii\helpers\Html::a($rel->name,["crud/status/view", 'id' => $rel->id,],["data-pjax"=>0]);
            } else {
                return '';
            }
        },
        "format" => "raw",
], 

回答by Paul van Schayck

This is based on the description in the guide. The base code for the SearchModel comes from the Gii code generator. This is also assuming that $this->TableB has been setup using hasOne()or hasMany()relation. See this doc.

这是基于指南中的描述。SearchModel 的基本代码来自 Gii 代码生成器。这也假设 $this->TableB 已使用hasOne()orhasMany()关系设置。请参阅此文档。

1. Setup search model

1.设置搜索模型

In TableASearchmodel add:

在TableASearch模型中添加：

public function attributes()
{
    // add related fields to searchable attributes
    return array_merge(parent::attributes(), ['TableB.attrB1']);
}

public function rules() 
{
    return [
        /* your other rules */
        [['TableB.attrB1'], 'safe']
    ];
}

Then in TableASearch->search()add (before $this->load()):

然后TableASearch->search()添加（之前$this->load()）：

$dataProvider->sort->attributes['TableB.attrB1'] = [
      'asc' => ['TableB.attrB1' => SORT_ASC],
      'desc' => ['TableB.attrB1' => SORT_DESC],
 ];

$query->joinWith(['TableB']); 

Then the actual search of your data (below $this->load()):

然后实际搜索您的数据（如下$this->load()）：

$query->andFilterWhere([
    'like',
    'TableB.attrB1',
     $this->getAttribute('TableB.attrB1')
]);

2. Configure GridView

2. 配置 GridView

Add to your view:

添加到您的视图中：

echo GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        /* Other columns */
       'TableB1.attrB1',
        /* Other columns */        
     ]
]);

回答by Alex

I'm stuck with this problem too, and my solution is rather different. I have two simple models:

我也被这个问题困住了，我的解决方案很不一样。我有两个简单的模型：

Book:

书：

class Book extends ActiveRecord
{
    ....

    public static function tableName()
    {
        return 'books';
    }

    public function getAuthor()
    {
        return $this->hasOne(Author::className(), ['id' => 'author_id']);
    }

And Author:

和作者：

class Author extends ActiveRecord
{

    public static function tableName()
    {
        return 'authors';
    }

    public function getBooks()
    {
        return $this->hasMany(Book::className(), ['author_id' => 'id']);
    }

But my search logic is in different model. And i didn't find how can i implement search without creating additional field author_first_name. So this is my solution:

但我的搜索逻辑采用不同的模型。而且我没有找到如何在不创建附加字段的情况下实现搜索author_first_name。所以这是我的解决方案：

class BookSearch extends Model
{
    public $id;
    public $title;
    public $author_first_name;

    public function rules()
    {
        return [
            [['id', 'author_id'], 'integer'],
            [['title', 'author_first_name'], 'safe'],
        ];
    }

    public function search($params)
    {
        $query = Book::find()->joinWith(['author' => function($query) { $query->from(['author' => 'authors']);}]);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => array('pageSize' => 50),
            'sort'=>[
                'attributes'=>[
                    'author_first_name'=>[
                        'asc' => ['author.first_name' => SORT_ASC],
                        'desc' => ['author.first_name' => SORT_DESC],
                    ]
                ]
            ]
        ]);

        if (!($this->load($params) && $this->validate())) {
            return $dataProvider;
        }
        ....
        $query->andWhere(['like', 'author.first_name', $this->author_first_name]);
        return $dataProvider;
    }
}

This is for creating table alias: function($query) { $query->from(['author' => 'authors']);}

这是用于创建表别名： function($query) { $query->from(['author' => 'authors']);}

And GridView code is:

和 GridView 代码是：

<?php echo GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        [
            'attribute' => 'id',
            'filter' => false,
        ],
        [
            'attribute' => 'title',
        ],
        [
            'attribute' => 'author_first_name',
            'value' => function ($model) {
                    if ($model->author) {
                        $model->author->getFullName();
                    } else {
                        return '';
                    }
                },
            'filter' => true,
        ],
        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

I will appreciate any critiques and advice.

我将不胜感激任何批评和建议。