Yii2 How to use dataprovider with custom and dynamic alias

Question

In yii2 I have to use SQL with user-defined dynamic alias.

So as it's dynamic so it's not possible to add as public property in the model.

So following code is not working with following SQL:

SELECT `item_id`         AS `Item Id`,
       `on_hand`         AS `On Hand`,
       `initial_on_hand` AS `Amount Produced`,
       `best_by_date`    AS `Best By`,
       `item_name`               AS `Item Name`
FROM   `stock` 

here is yii2 code:

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

So whats the possible solution using which I can use yii2 grid with pagination and controls?

Answer 1

use this solution

$query = (new Query()) 
       ->select(
       'stock.item_id as Item Id,
        stock.on_hand as On Han,
        stock.intial_on_hand as Amount Produced,
        stock.best_By_date as Best By ,
        stock.item_name as Item Name
       )
        ->from('notifications as n') ->all()
    
    ------------------------------------------------------------------------

instead of doing this you can just simply create a method called attributeLabels() inside of your model stock if your purpose is getting labels .

  public function attributeLabels()
    {
        return [
            'item_id' => 'Item Id',
            'on_hand' => 'On Hand',
            'initial_on_hand' => 'Amount Produced`,
            'decaissement_id' => 'Decaissement ID',
            'best_by_date' => 'Approved By',
            'item_name' => 'Item Name',
        ];
    }

Answer 2

If you absolutely insist on using user input as aliases in your SQL query you can use yii\data\SqlDataProvider instead of yii\data\ActiveDataProvider.

I still believe that doing that is really bad idea because of possible SQL injection. There is no way to use parameters as aliases in SQL query. That means you are letting user directly modify your SQL query.

If you want to have dynamic labels you can do it for example this way. Let's assume you have some dynamic_label tabel in DB where you store the labels set in backend. The table can look for example like this:

id	model	field	label
1	Stock	item_id	Item ID
2	Stock	on_hand	On hand

You also have DynamicLabel model as ActiveRecord model for that table. Then, your model can look for example like this:

class Stock extends ActiveRecord
{
    private static ?array $labels = null;

    private static function loadLabels(): void
    {
        if (self::$labels !== null) {
            // Labels are already loaded
            return;
        }
        self::$labels = DynamicLabel::find()
           ->select(['label', 'field']) // we will only need columns label and field
           ->where(['model' => 'Stock']) // select only rows that belong to current model 
           ->indexBy('field') // index result using the value in field column
           ->column(); // get first column of result as array
    }

    public function attributeLabels(): array
    {
        self::loadLabels();
        return self::$labels;
    }

    // ... other definitions in Stock model ...
}

The labels are stored in static property to make sure that they are loaded only once during request even if multiple instances of Stock model are used and the attributeLabels() method is called multiple times.

Answer 3

$query = StockModel::find();
$query->select([
            'stock.item_id as Item Id',
            'stock.on_hand as On Han',
            'stock.intial_on_hand as Amount Produced',
            'stock.best_By_date as Best By',
            'stock.item_name as Item Name'
    ]);

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