Yii2 multiple table in gridview

Question

I have 2 tables with strategy and risk_colors .

I generated Model and CRUD with GII and modified it just a little to get risk_value column in my GridView widget.

Here is my Strategy.php

<?php

namespace backend\models;

use Yii;
use yii\db\ActiveRecord;
use yii\helpers\Url;
use yii\helpers\Html;
use yii\helpers\ArrayHelper;
use yii\db\Expression;
/**
 * This is the model class for table "strategy".
 *
 * @property int $id
 * @property string $strategy_title
 * @property string $strategy_description
 * @property string $strategy_current_money
 * @property int $risk_colors_id
 *
 * @property SelectedStrategies[] $selectedStrategies
 * @property RiskColors $riskColors
 */
class Strategy extends \yii\db\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'strategy';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['strategy_title', 'strategy_description', 'strategy_current_money', 'risk_colors_id'], 'required'],
            [['strategy_current_money', 'risk_colors_id'], 'integer'],
            [['strategy_title'], 'string', 'max' => 255],
            [['strategy_description'], 'string', 'max' => 2055],
            [['risk_colors_id'], 'exist', 'skipOnError' => true, 'targetClass' => RiskColors::className(), 'targetAttribute' => ['risk_colors_id' => 'id']],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'strategy_title' => 'Strategy Title',
            'strategy_description' => 'Strategy Description',
            'strategy_current_money' => 'Strategy Current Money',
            'risk_colors_id' => 'Risk Color ID',
            'riskValue' => Yii::t('app', 'Risk'),
            'colorNumber' => 'Color Number',
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSelectedStrategies()
    {
        return $this->hasMany(SelectedStrategies::className(), ['strategy_id' => 'id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getRiskColors()
    {
        return $this->hasOne(RiskColors::className(), ['id' => 'risk_colors_id']);
    }

    /**
     * @return risk value
     */
    public function getRiskValue()
    {
        return $this->riskColors->risk_value;
    }

    /**
     * @return Get risk value list for dropdown
     */
    public function getRiskValueList()
    {
        $droptions = RiskColors::find()->asArray()->all();
        return ArrayHelper::map($droptions, 'id', 'risk_value'); 
    }

    /**
     * @return Get risk value list for dropdown
     */
    public function getColorNumberList()
    {
        $droptions = RiskColors::find()->asArray()->all();
        return ArrayHelper::map($droptions, 'id', 'color_number'); 
    }

}

here is my index.php.

After all i got risk_value column in my GridView, but it looks like i cant sort my table by this field. Here is .

Here is my Search Model

So my question is what should i do to make this field sortable?

Answer 1

In your StrategySearch.php

public function search($params)
{
     $query = Strategy::find()->joinWith(['riskColors']);

     // add conditions that should always apply here

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

     $dataProvider->sort->attributes['risk_colors_id'] = [
         'asc' => ['risk_colors.risk_value' => SORT_ASC],
         'desc' => ['risk_colors.risk_value' => SORT_DESC],
     ];

     $this->load($params);

     if (!$this->validate()) {
         // uncomment the following line if you do not want to return any records when validation fails
         // $query->where('0=1');
         return $dataProvider;
     }

     // grid filtering conditions
     $query->andFilterWhere([
         'id' => $this->id,
         'strategy_current_money' => $this->strategy_current_money,
         // 'risk_colors_id' => $this->risk_colors_id,
         'risk_colors.risk_value' => $this->risk_colors_id
     ]);

     $query->andFilterWhere(['like', 'strategy_title', $this->strategy_title])
           ->andFilterWhere(['like', 'strategy_description', $this->strategy_description]);

     return $dataProvider;
}

Refere GridView Sorting