How to get the value of a foreign key instead of a key in the form of a grid with search and filtering in yii 2?

Question

I have two staff tables with id , name and attendance columns. staff_id used as a foreign key in the attendance table.

I want to display the employee name in gridview for visits.

Attendance Model :

 public function getStaff() { return $this->hasOne(Staff::className(), ['id' => 'staff_id']); } public function getStaffName() { return $this->staff->name; } 

and in index.php i used this code

  <?= GridView::widget([ [ 'attribute'=>'staff_id', 'value'=>'StaffName', ], ]); ?> 

to get the value of the name of the staff. Thus, I get the employee name successfully, but the problem is that when I do a personnel name search in gridview, it says that "staff_id" should be integer, as I define it as an integer, but here I want to search for the personnel name instead id

How is this possible? thanks in advance

Answer 1

Add this to your search model.

$query->joinWith(['staff(relation name)']);

And add below code to filter request.

$query->andFilterWhere(['like', 'staff.name', $this->staff_id])

In staff.name , that in staff is the name of the table.

Answer 2

You can use this code

 <?= GridView::widget([ 'dataProvider' => $dataProvider, 'columns' => [ 'staff.name', ], ]); ?> 

OR use this code

 <?= GridView::widget([ 'dataProvider' => $dataProvider, 'columns' => [ [ 'attribute' => 'staff.name', 'header' => 'Staff title' 'value'=> 'function ($model, $key, $index, $grid){ return $model->staff->name; }' ], ], ]); ?> 

OR in code you can use this

 <?= GridView::widget([ [ 'attribute'=>'staff_id', 'value'=>'getStaffName()', ], ]); ?> 

and for search you can watch this video Search for relevant table data

Answer 3

 You can use this code 1. relation in models public function getCountry() { return $this->hasOne(Country::className(), ['id' => 'country_id']); } 2. in grid view <?= GridView::widget([ 'dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'columns' => [ ['class' => 'yii\grid\SerialColumn'], 'id', 'state_name', // 'country_id', [ 'attribute'=>'country_id', 'value'=>'country.country_name', ], ['class' => 'yii\grid\ActionColumn'], ], ]); ?> 3. Search Models change country_id from integer to safe than change on search function public function search($params) { $query = State::find(); $query->joinWith(['country']); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); $this->load($params); if (!$this->validate()) { return $dataProvider; } $query->andFilterWhere([ 'id' => $this->id, ]); $query->andFilterWhere(['like', 'state_name', $this->state_name]) ->andFilterWhere(['like', 'country.country_name', $this->country_id]); return $dataProvider; }