How to integrate SQL-Generated columns in Yii2 GridView

Question

To show my GridView I use this ActiveDataProvider:

public function search($params)
{
  $query = PublicationsPublication::find()
    ->select(['eid', 'title', 'pubdate', 'citedby', "STRING_AGG(DISTINCT(CONCAT(author.authid, ' - ', authname)), ', ') AS authors"])
    ->joinWith('publicationsAuthor')
    ->groupBy(['eid','title','pubdate','citedby']);

  $dataProvider = new ActiveDataProvider([
    'query' => $query,
  ]);
  $this->load($params);
  if (!$this->validate()) {
    return $dataProvider;
  }
  ...
}

I can't figure out how to use the column generated by the STRING_AGG() function in the Gridview.

Just in case is needed, the publicationsAuthor relation is coded this way:

public function getPublicationsAuthor() {
  return $this->hasMany(PublicationsAuthor::className(), ['authid' => 'authid'])
    ->viaTable('publications.pub_author', ['pubid' => 'id']);
}

I need to use the STRING_AGG() function because I want to show many authors in one cell of the Gridview.

I tried to use the "authors" column in this way:

$gridColumns = [
  [
    'class' => 'kartik\grid\SerialColumn',
    'width' => '20px',
  ],
  'eid',
  'title',
  'pubdate',
  'citedby',
  'authors',
];
echo GridView::widget([
  'dataProvider' => $dataProvider,
  'filterModel' => $searchModel,
  'columns' => $gridColumns,
  'pager' => [
    'firstPageLabel' => 'First',
    'lastPageLabel'  => 'Last'
  ],
  ...
]);

But unfortunately it didn't work. In the Grid all the values are set to "not set". The query works great because I tested it in PgAdmin.

score 0 · Accepted Answer · answered May 11 '21 at 08:53

yii\data\ActiveDataProvider works with models so only fields defined by model are available by default. The easiest way to add field generated by some expression is to add public property with same name to your model like this:

class PublicationsPublication extends ActiveRecord
{
    public $authors;

    // ... other code in PublicationsPublication model ...
    
    public function attributeLabels()
    {
         // You can also add label for new field
         return [
             'authors' => 'Authors',
             // ... other labels ...
         ];
    }

}

The public property $authors will be loaded with data from field authors in the result of your SQL query. Then you can use it in grid as any other field.

The other option is to use yii\data\SqlDataProvider instead of yii\data\ActiveDataProvider.

How to integrate SQL-Generated columns in Yii2 GridView

1 Answers1