如何在yii2中以网格视图显示另一个表的数据？

Question

In siteController I write query and pass array of dataProvider to index.php to display contain in table form. In index.php I want to display member name instead of memberID. For that I have write inner query and which is successfully run on command prompt successfully.Here I am not able to print first name instead of 'member id'

      public function actionIndex()
      {              
     $query = new \yii\db\Query;
      $query->select(['member.firstName',
                    'complaint.practiceCode','complaint.id',
                    'complaint.description','member.firstName'])
           ->from(['complaint'])
           ->innerJoin(['member','complaint.memberID = member.id'])
           ->groupBy(['complaint.id'])
           ->where(['complaint.deleted' => 'N']);
     $query->createCommand();

Here I have pass data by creating $dataProvider4 but I am not able to set value of firstName instead of memberID.

      $dataProvider4= new ActiveDataProvider([
            'query' => $query,
            'pagination' => false,
       ]);

        return $this->render('index', [
       'dataProvider4'=>$dataProvider4]);

<?= GridView::widget([
    'dataProvider'=>$dataProvider4,
    'summary'=>'Total'.'&nbsp<b>'.$complaintModel.'</b>&nbsp'.'Complaints',
    'columns' => [
       ['class' => 'yii\grid\SerialColumn'],
           'practiceCode',
            // 'memberID',
            'description',
            'status',
       ],
    ]); ?>

I have pass data through the dataProvider.

Answer 1

Since you are using a powerful framework, then you'd be better to let the framework do the complicated thing for you, rather than trying to write your own queries. It's what Yii was designed for. Try this in your action.

public function actionIndex()
    {              
    $query = Member::find()->
        ->select(['firstName', complaint.practiceCode', complaint.id', 'complaint.description'])
        ->groupBy(['complaint.id'])
        ->joinWith('complaints')//Tells Yii to use the complains relation that we define below. By default it is an inner join
        ->where(['complaint.deleted' => 'N']);

$dataProvider= new ActiveDataProvider([
    'query' => $query,
    'pagination' => false,
]);

return $this->render('index', [
    'dataProvider4'=>$dataProvider]);

In your model you will need to define a relation that you can use in the query;

public function getComplaints(){
    return $this->hasMany(Complaints::className(), 'memberID' => 'id');
}

This is useful as it will allow you to get complaints without having to write your own query to get them.

Yii will sort out all the column names for you, and write the query.