Yii GridView Columns returning (not set)

Question

I am using the Yii php framework. My goal is simple, I wish to display the following data: P_id, Autor, Anio, Titulo, Resumen. I have a modal class named ScientificProduction.

This is my code for the file:

<?php

namespace app\models;

use Yii;

/**
 * This is the model class for table "scientific_production".
 *
 * @property int $SP_id
 * @property int $User_id
 * @property int $P_id
 * @property string $Titulo
 */
class ScientificProduction extends \yii\db\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'scientific_production';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['User_id', 'P_id', 'Titulo'], 'required'],
            [['User_id', 'P_id'], 'integer'],
            [['Titulo'], 'string', 'max' => 255],
        ];
    }

       // Define relacion con libros table
       public function getLibro()
       {
           return $this->hasOne(Libros::class, ['Libro_id' => 'P_id']);
       }
   
       // Define relacion con cap_libros table
       public function getCapLibro()
       {
           return $this->hasOne(CapLibros::class, ['Cap_id' => 'P_id']);
       }
   
       // Define relacion con articulos table
       public function getArticulo()
       {
           return $this->hasOne(Articulos::class, ['Articulos_id' => 'P_id']);
       }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'SP_id' => 'Sp ID',
            'User_id' => 'User ID',
            'P_id' => 'P ID',
            'Titulo' => 'Titulo',
        ];
    }
}

Now, in my controller class I declare an ActiveDataProvider to pass it the necessary query. For this I declare it in my actionIndex function so I can render my view file index.

My code for actionIndex is this:

public function actionIndex()
    {
        $provider = new ActiveDataProvider([
            'query' => ScientificProduction::find()
                ->select([
                    'scientific_production.P_id',
                    'COALESCE(libros.Autor, cap_libros.Autores_capitulo, articulos.Autor) AS Autor',
                    'COALESCE(libros.Anio, cap_libros.Anio, articulos.Anio) AS Anio',
                    'COALESCE(libros.Titulo, cap_libros.Titulo_capitulo, articulos.Titulo) AS Titulo',
                    'COALESCE(libros.Resumen, cap_libros.Resumen, articulos.Resumen) AS Resumen'
                ])
                ->leftJoin('libros', 'scientific_production.P_id = libros.Libro_id')
                ->leftJoin('cap_libros', 'scientific_production.P_id = cap_libros.Cap_id')
                ->leftJoin('articulos', 'scientific_production.P_id = articulos.Articulos_id'),
            'pagination' => [
                'pageSize' => 10, // Adjust page size as needed
            ],
            'sort' => [
                'attributes' => [
                    'P_id',
                    'Autor',
                    'Anio',
                    'Titulo',
                    'Resumen',
                ],
            ],
        ]);

        // returns an array of Post objects
        return $this->render('index', [
            'dataProvider' => $provider,
         ]);
    }

My code for the view file is this:

<?php
   use yii\grid\GridView;
   
   echo GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' =>[
         [            
             // you may configure additional properties here
             'attribute' => 'P_id',
             'headerOptions'=>[ 'style'=>'background-color:#691C32;' ]
         ],
         [            
            // you may configure additional properties here
            'attribute' => 'Autor',
            'headerOptions'=>[ 'style'=>'background-color:#691C32' ]
        ],
        [            
         // you may configure additional properties here
         'attribute' => 'Anio',
         'label' =>'Año',
         'headerOptions'=>[ 'style'=>'background-color:#691C32' ]
         ],
         [            
            // you may configure additional properties here
            'attribute' => 'Titulo',
            'headerOptions'=>[ 'style'=>'background-color:#691C32' ]
        ],
        [            
         // you may configure additional properties here
         'attribute' => 'Resumen',
         'headerOptions'=>[ 'style'=>'background-color:#691C32' ]
         ],
      ],
     ]);

?>

What do I expect, a grid table with the data for P_id, Autor, Anio, Titulo and Resumen.

What do I get, the values (not set) for my columns Autor, Anio and Resumen.

I do a join with three other tables, said tables have one item register. I tried debbuging the sql statement using the sql generated by yii:

echo $dataProvider->query->createCommand()->sql;

This command generated a valid sql statement that I executed in my database and got the data I wanted.

Answer 1

When you are using yii\db\ActiveQuery like this:

'query' => ScientificProduction::find()
    ->select([
        // ...
    ])
    // ...

This is what actually happens:

1. The query is executed and data are retrieved from DB.
2. ScientificProduction model instance is created for each unique row returned by query.
3. Model instances are populated with data from query.

By default, the ScientificProduction model only "knows" about fields that matches columns in scientific_production DB table. When the models are populated in step 3, only "known" fields are populated with data, rest is ignored.

Then the yii\grid\GridView receive those models from yii\data\ActiveDataProvider and it's working with them. It's not working with SQL results directly. That's why fields like P_id and Titulo are fine, because they exists in scientific_production table, but others appear empty.

To fix it you have two options.

1. Force ActiveQuery to return result as array

When creating the query you can tell it that you don't want to create instances of ScientificProduction model. By calling asArray() method you can force ActiveQuery to skip steps 2 and 3 and return data from DB as array as they came from DB.

You need to simply add asArray() method call to the chain of methods when you are creating your query.

'query' => ScientificProduction::find()
    ->select([
        // ...
    ])->asArray()
    // ...

2. Create properties for calculated fields in model

If you prepare properties for calculated fields in model they will be populated with data during step 3. For example like this:

class ScientificProduction extends \yii\db\ActiveRecord
{
    public $Autor;
    public $Anio;
    public $Resumen;

    // ... rest of your model ...
}