Yii2 : Active Record Column Aliases

Question

I'm using Yii2 framework with advanced template.

I get problem with column alias in my controller file, here's my code:

$models = new ActiveDataProvider([
    'query' => User::find()->select(['member'=>'fullname'])
]);

The above query equivalent with:

SELECT fullname AS member FROM User;

I send the data to the view using this code:

return $this->render('view', [
        'model' => $models,
    ]);

I want to call the data in my view using GridView widget, here's my code:

echo GridView::widget([
    'dataProvider' => $model,
    'columns' => [
        'member',
    ],
]);

However, I got an error that tell me the 'member' parameter is not defined. How can I show the data of my query by calling the column name? (in my case it using alias)

I really appreciate any kind of helps!!

Answer 1

You should simply declare this attribute in your model :

class User extends ActiveRecord
{

    public $member;

Read more : https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#selecting-extra-fields

Answer 2

ActiveDataProvider works only with model attributes. member obviously is not presented there.

First of all, maybe it's better to refactor column names to be more clear instead of writing aliases? I don't see any benefit in your example.

If you nevertheless need to use aliases, as alternative for adding additional properties to class, you can work with them with help of ArrayDataProvider and SqlDataProvider.

Examples of usage:

ArrayDataProvider:

use yii\data\ArrayDataProvider;

$dataProvider = new ArrayDataProvider([
    'allModels' => User::find()->select(['member' => 'fullname'])->all(),
]);

SqlDataProvider:

use yii\data\SqlDataProvider;
use yii\db\Query;

...

$query = (new Query())
    ->select(['member' => 'fullname'])
    ->from('users');

$command = $query->createCommand();

$dataProvider = new SqlDataProvider([
    'sql' => $command->sql,
    'params' => $command->params,
    'totalCount' => $query->count(),
]);

For more details and features of usage please see official docs.

For your case it's better to use ArrayDataProvider, SqlDataProvider is for more complex queries.

In case of one alias and using model methods adding additional attribute as suggested by soju can be better.

But in my opinion it's useless and it's better to refactor column names in case of some ambiguity.