Introduction to Filtering and Sorting in Yii2

GridView in Yii2 provides a robust way to display and interact with data. However, when working with relational data, you may find yourself needing to filter and sort columns across different tables. This blog post will walk you through the process of configuring a Yii2 GridView to filter and sort relational data effectively.

Setting Up the Data Models

We have two tables, TableA and TableB, with corresponding models generated using Gii. TableA contains foreign keys referencing values in TableB. Specifically, the columns are as follows:

  • TableA: Attra1 (primary key), Attra2, Attra3
  • TableB: Attrb1 (primary key), Attrb2, Attrb3

Our goal is to include filtering and sorting operations on the ‘Attrb2’ column while already having them in place for ‘Attra2’ and ‘Attra3’.

Implementing the Filter and Sort Functions

First, you need to modify your search model, in this case, TableASearch model. Here’s how you can extend the search functionality to include filtering from TableB:

public function search($params) {
$query = TableA::find();
$query->joinWith('tableB');
$dataprovider = new ActiveDataProvider([    'query' => $query,]);
if (!($this->load($params) && $this->validate())) {
    return $dataprovider;
}
$query->andFilterWhere(['like', 'TableA.attra2', $this->attra2])      
->andFilterWhere(['like', 'TableA.attra3', $this->attra3])      
->andFilterWhere(['like', 'TableB.attrb2', $this->attrb2]);
return $dataprovider;
}

In the above code snippet, the ‘joinWith(‘tableB’)’ method is used to join TableB with TableA, allowing us to filter based on TableB attributes.

Updating the GridView Widget

Next, update the `GridView` widget in your view file to include ‘Attrb2’ column along with the filtering and sorting settings:

 $dataProvider,    
'filterModel' => $searchModel,    
'columns' => [        
'attra2',        
'attra3',        
[            
'attribute' => 'attrb2',            
'value' => 'tableB.attrb2',            
'filter' => Html::activeTextInput($searchModel, 'attrb2', ['class' => 'form-control']),        
],    
],]); 
?>

Now, your Yii2 GridView should allow filtering and sorting of ‘Attrb2’ from TableB along with the other columns from TableA.

Conclusion

Integrating filter and sort functionality for relational data in Yii2 GridView is straightforward once your models and search function are set up correctly. This enhancement can greatly improve user experience by providing versatile data interaction options. Give it a try and see how it simplifies data handling in your Yii2 projects.

Share!

Shares