How To Use Filters In The Gridview Widget When Table Relationship Is Present?

Hello Guys,

Sorry for the long post, but I’m very new to Yii, let alone Yii2. I would like to understand how I should handle table relationship, so I set up a very simple scenario with two tables, one called employee and another one called team. An employee can belong to one team and one team can have multiple employees. Here is the migration that I created for this scenario:




class m131125_221834_create_basic_schema extends \yii\db\Migration

{

	public function up()

	{

        $this->createTable('employee', [

            'id' => Schema::TYPE_PK.' NOT NULL',

            'first_name' => Schema::TYPE_STRING.'(32) NOT NULL',

            'last_name' => Schema::TYPE_STRING.'(32) NOT NULL',

            'account_type' => Schema::TYPE_STRING."(1) NOT NULL DEFAULT 'E'",

            'team_id' => Schema::TYPE_INTEGER.' NOT NULL'

        ]);


        $this->createTable('team', [

            'id' => Schema::TYPE_PK.' NOT NULL',

            'short_name' => Schema::TYPE_STRING.'(64) NOT NULL',

            'description' => Schema::TYPE_TEXT

        ]);


        $this->addForeignKey('employee_to_team','employee','team_id','team','id');

	}


	public function down()

	{

        $this->dropForeignKey('employee_to_team','employee');


        $this->dropTable('employee');


        $this->dropTable('team');

	}

}



As you can see there is a foreign key in the employee table referencing a team_id in the team table. I’m using the yii2-advanced app for starting point. Created models (\common\models\team and \common\models\employee respectively) for both tables using the gii utility. Also created a CRUD for the employee table using gii.

When I go to the employee index page, team_id will be listed as opposed to the team’s name.

So, opened the file called \common\models\search\EmployeeSearch and located the search() function. It defines the query in the first line, which originally reads:




$query = Employee::find();



Replaced the first line with the following so that the corresponding team records will be provided:




$query = Employee::find()->leftJoin('team','employee.team_id=team.id')->with('Team');



Also replaced the ‘team_id’ column of the GridView widget with ‘Team’ like so (\backend\views\employee\index.php):




	<?php echo GridView::widget([

		'dataProvider' => $dataProvider,

		'filterModel' => $searchModel,

		'columns' => [

			['class' => 'yii\grid\SerialColumn'],


			//'id',

			'first_name',

			'last_name',

			'account_type',

			'Team',


			['class' => 'yii\grid\ActionColumn'],

		],

	]); ?>



I got an error Getting unknown property: common\models\Employee::Team

so added the __toString() method to \common\models\Team:




    public function __toString()

    {

        return $this->short_name;

    }



It did the trick. Team ID is gone and friendly name is displayed in the GridView widget. However, the filter field remains empty and I cannot figure it out how I can make GridView to provide a dropdown box with all used teams or at least an input box where the user can input the name of the team.

I tried using the filter property in GridView with no luck.

Can you guys please give me a pointer how should I implement the filter functionality in case table relationship is used?

Thank you in advance.

PS.: In the meantime I figured out sorting but still no luck with filters.

Anyone? Also stuck on this…

I don’t think the built-in functionality can handle this; you’ll need to extend the base classes with your own.

Not sure if this is 100% right, but

  1. Extend DataColumn and modify the following two functions:



protected function renderHeaderCellContent()

protected function renderFilterCellContent()



  1. Update EmployeeSearch::search($params) to handle the sort parameter

Thanks Amnah for your help. Search is relatively easy to implement by making some changes to $dataProvider in the search function like you said:




	$dataProvider = new ActiveDataProvider([

	    'query' => $query,

            'sort' => array(

                'attributes' => array(

                    'Team' => array(

                        'asc' => array('team.short_name' => SORT_ASC, 'last_name' => SORT_ASC),

                        'desc'=> array('team.short_name' => SORT_DESC, 'last_name' => SORT_ASC)

                    ),

                    'last_name',

                    'first_name',

                    'account_type'

                )

            ),

	]);



I will have a look into overriding the functions you mentioned, but I was hoping for a less complicated solution as this is pretty low-level stuff when I need to override the actual render methods. Thanks again for looking into this.

Hi, somebody knows how to implement filters in Model Search when you have Relations?

GRIDVIEW


<?php echo GridView::widget([

		'dataProvider' => $dataProvider,

		'filterModel' => $searchModel,

		'columns' => [

			['class' => 'yii\grid\SerialColumn'],

			'name',

			'last_name',

            [

                'attribute' => 'employee_type_id',

                'label' => 'Employee Type',

                'value' => function($model, $index, $dataColumn) {

                        return $model->employeeType->name;

                    },

            ],


			['class' => 'yii\grid\ActionColumn'],

		],

	]); ?>

MODEL




$query = Employee::find()

            ->leftJoin('employee_type','employee.employee_type_id=employee_type.id')

            ->with('EmployeeType');

$dataProvider = new ActiveDataProvider([

			'query' => $query,

            'sort' => array(

                'attributes' => array(

                    'employee_type_id' => array(

                        'asc' => array('employee_type.name' => SORT_ASC, 'name' => SORT_ASC),

                        'desc'=> array('employee_type.name' => SORT_DESC, 'name' => SORT_ASC)

                    ),

                    'name',

                    'last_name',

                )

            ),

		]);

I could see the filter but it does not work because it is trying to search by employee_type_id instead of employee_type.name.

And if I add this condition does not work


$this->addCondition($query, 'employee_type.name');

Please, if somebody has the solution I’m going to thank.

@sciolini

Easy solution: use a dropdown. Harder solution: add an attribute to your search model. In your case, it’s extremely tricky because both of your tables have a “name” column, which means that you’ll have to come up with some clever way to account for both search terms.

(Hint: think of the sql command, specifically the ambiguous part where name = ?)

See below guide for an intro.

@everyone else

I’ve written a guide on how to display related model data. Hopefully this will help people down the line if they visit this thread.

http://amnah.net/2013/12/22/how-to-display-sort-and-filter-related-model-data-in-gridview/

@amnah

I was reading your blog and I think the best solution is add a dropdown.

Could you write the code of the dropdown method that you use in the GridView?


'filter' => Role::dropdown()

Is it possible to use a textfield instead of dropdown withoud add a new field in the Model?

Thanks!

The dropdown would be someting like




$dropdown = [];

foreach ($employeeTypes as $employeeType) {

    $dropdown[$employeeType->id] = $employeeType->name;

}

return $dropdown;



In your case, it is possible to use a textfield without creating a new attribute. This is because you already have an attribute in there that you can use: employee_type_id.

You would need to do two things:

  1. Modify the rules() function and change the rule for employee_type_id to string instead of int

  2. Modify the public function search($params) function to process it

The exact implementation, I’m not sure about it. Let me try it out for a bit.

Edit: easier than I thought, though it’s bit of a hack.

In protected function addCondition(), you can modify the attribute. Something like this:




$attribute = $attribute == "employee_type_id" ? "tbl_employee_type.name" : $attribute;



I’ll let you figure out a more proper way to do implement it.

@amnah

Thanks a lot for your answer!!

I could do it with the following code

EMPLOYEESEARCH.PHP


public function search($params)

	{

        $query = Employee::find()

            ->leftJoin('employee_type','employee.employee_type_id=employee_type.id')

            ->with('EmployeeType');


		$dataProvider = new ActiveDataProvider([

			'query' => $query,

            'sort' => array(

                'attributes' => array(

                    'employee_type_id' => array(

                        'asc' => array('employee_type.name' => SORT_ASC, 'name' => SORT_ASC),

                        'desc'=> array('employee_type.name' => SORT_DESC, 'name' => SORT_ASC)

                    ),

                    'name',

                    'last_name',

                )

            ),

		]);


		if (!($this->load($params) && $this->validate())) {

			return $dataProvider;

		}


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

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

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

		return $dataProvider;

	}


protected function addCondition($query, $attribute, $partialMatch = false)

	{

		$value = $this->$attribute;

		if (trim($value) === '') {

			return;

		}

		if ($partialMatch) {

			$value = '%' . strtr($value, ['%'=>'\%', '_'=>'\_', '\\'=>'\\\\']) . '%';


            $attribute = $attribute == "name" ? "employee.name" : $attribute;

            $attribute = $attribute == "last_name" ? "employee.last_name" : $attribute;

            $attribute = $attribute == "employee_type_id" ? "employee_type.name" : $attribute;


			$query->andWhere(['like', $attribute, $value]);

		} else {

			$query->andWhere([$attribute => $value]);

		}

	}

And I change the rule ‘employee_type_id’ to safe instead of integer.

I do not like to add all those if in the addCondition function but i have to do it because both tables has the attribute ‘name’.

I read another solution that i think is better but the table does not show the filter

MODEL


 /**

     * @return EmployeeType::name string

     */

    public function getEmployeeTypeName() {

        return $this->employeeType->name;

    }

INDEX


<?php echo GridView::widget([

		'dataProvider' => $dataProvider,

		'filterModel' => $searchModel,

        'tableOptions'=>['class'=>'table table-condensed'],

		'columns' => [

			['class' => 'yii\grid\SerialColumn'],

			'name',

			'last_name',

            [

                'label'=>'Employee Type',

                'attribute' => 'employeeTypeName',

            ],


			['class' => 'yii\grid\ActionColumn'],

		],

	]); ?>

SEARCH


$dataProvider = new ActiveDataProvider([

			'query' => $query,

            'sort' => array(

                'attributes' => array(

                    'employeeTypeName' => array(

                        'asc' => array('employee_type.name' => SORT_ASC, 'name' => SORT_ASC),

                        'desc'=> array('employee_type.name' => SORT_DESC, 'name' => SORT_ASC)

                    ),

                    'name',

                    'last_name',

                )

            ),

		]);

I tried to add a filter manually in the column but i does not work. Do you know how to add a filter in that case?

Thanks!

I don’t understand why you are so hesitant to add the attribute. This is the search model, not the user model; they don’t need to match up.

If you’re searching for employee type name, then you should have the proper attribute for it. Why hack around it just because gii didn’t generate it for you?

As for your question, it’s because it doesn’t have the attribute. Try to make it work around this function if you want.




protected function renderFilterCellContent()

	{

		if (is_string($this->filter)) {

			return $this->filter;

		} elseif ($this->filter !== false && $this->grid->filterModel instanceof Model &&

				  $this->attribute !== null && $this->grid->filterModel->isAttributeActive($this->attribute))

		{

			if (is_array($this->filter)) {

				$options = array_merge(['prompt' => ''], $this->filterInputOptions);

				return Html::activeDropDownList($this->grid->filterModel, $this->attribute, $this->filter, $options);

			} else {

				return Html::activeTextInput($this->grid->filterModel, $this->attribute, $this->filterInputOptions);

			}

		} else {

			return parent::renderFilterCellContent();

		}

	}

  1. In Model EmployeeSearch.php
    Remove ‘team_id’ from [[…], ‘integer’],
    Put ‘team_id’ in the line safe
    [[‘team_id’], ‘safe’],

  2. $query = Employee::find()->leftJoin(‘team’,‘employee.team_id=team.id’)->with(‘Team’);

  3. In $query->andFilterWhere([
    // ‘team_id’ => $this->team_id,

  4. In $query->andFilterWhere([ …
    ->andFilterWhere([‘like’, ‘team.name’, $this->team_id])

  5. In index.php

<?= GridView::widget([ 'dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'columns' => [ ['class' => 'yii\grid\SerialColumn'], [ 'label' => 'Team', 'attribute' => 'team_id', 'value' =>'team.name', ], that's all