php - Yii2: how to populate a Gridview column using a SQL query?

Solution:

What do you have in$dataprovider? If it is loaded with objects from persons table, you can call it likeid -lastname or you can call it like that:

[
            'label' => 'Lastname',
            'value' => function($model) {
                 return $model->lastname;
            }
]
Copy code

In PersonsSearch.php

public function rules()
{
    return [
        [['id'], 'integer'],
        [['lastname'], 'safe'],
    ];
}
Copy code

and search function

public function search($params)
{
    $query = Persons::find();

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort' => [
            'defaultOrder' => [
                'id' => SORT_ASC
            ]
        ],
    ]);

    $this->load($params);

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

    $query->andFilterWhere([
        'id' => $this->id,
    ]);

    $query->andFilterWhere(['ilike', 'id', $this->id]);
    $query->andFilterWhere(['like', 'lastname', $this->lastname]);

    return $dataProvider;
}
Copy code

Other way when you have

 [
                'label' => 'Lastname',
                'value' => function($model) {
                     return $model->getLastName();
                }
    ]
Copy code

Create function inPersons.php model with namegetLastName()

public function getLastName(){
   $myQuery = 'SELECT lastname FROM persons'; //maybe you have where condition "where id = {$this->id}" ?
   $result = Yii::$app->getDb()->createCommand($myQuery)->queryOne();
   if($result){
        return $result['lastname'];
   }
}
Copy code

If you have many rows, then usequeryAll() and foreach the result.