YII2 SqlDataProvider不起作用关系表值

Question

This is my model Riders:

<?php

namespace backend\models;

use Yii;

class Riders extends \yii\db\ActiveRecord
{
public static function tableName()
{
    return 'riders';
}

public function rules()
{
    return [
        [['cagories_category_id', 'rider_firstname', 'rider_no_tlpn', 'rider_ucinumber', 'countries_id', 'rider_province', 'rider_city', 'rider_dateofbirth', 'rider_gender'], 'required'],
        [['user_id', 'countries_id'], 'integer'],
        [['rider_dateofbirth', 'cagories_category_id'], 'safe'],
        [['rider_gender', 'rider_status'], 'string'],
        [['rider_firstname', 'rider_lastname', 'rider_nickname', 'rider_province', 'rider_city'], 'string', 'max' => 45],
        [['rider_email', 'rider_sponsor', 'rider_birthcertificate_url', 'rider_parental_consent_url'], 'string', 'max' => 100],
        [['rider_no_tlpn'], 'string', 'max' => 15],
        [['rider_ucinumber'], 'string', 'max' => 11]
    ];
}

/**
 * @inheritdoc
 */
public function attributeLabels()
{
    return [
        'rider_id' => 'rider_id',
        'cagories_category_id' => 'Category Name',
        'user_id' => 'User Team',
        'rider_firstname' => 'Rider Firstname',
        'rider_lastname' => 'Rider Lastname',
        'rider_nickname' => 'Rider Nickname',
        'rider_email' => 'Rider Email',
        'rider_no_tlpn' => 'Rider No Tlpn',
        'rider_ucinumber' => 'Rider Ucinumber',
        'countries_id' => 'Country Name',
        'rider_province' => 'Rider Province',
        'rider_city' => 'Rider City',
        'rider_sponsor' => 'Rider Sponsor',
        'rider_dateofbirth' => 'Rider Dateofbirth',
        'rider_gender' => 'Rider Gender',
        'rider_birthcertificate_url' => 'Rider Birthcertificate Url',
        'rider_parental_consent_url' => 'Rider Parental Consent Url',
        'rider_status' => 'Rider Status',
    ];
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getRegistrations()
{
    return $this->hasMany(Registrations::className(), ['riders_rider_id' => 'rider_id']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getCagoriesCategory()
{
    return $this->hasOne(Categories::className(), ['category_id' => 'cagories_category_id']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getUser()
{
    return $this->hasOne(User::className(), ['id' => 'user_id']) -> from(user::tableName() . 'ud');
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getUserDesc()
{
    return $this->hasOne(UserDesc::className(), ['desc_id' => 'user_id']) -> from(['ud' => userDesc::tableName()]);
}

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

}

This my Controller actionIndex:

$searchModel = new RidersSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);

$totalCount = Yii::$app->db->createCommand('SELECT COUNT(*) FROM riders WHERE user_id = :user_id',
    [':user_id' => Yii::$app->user->identity->id])->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM riders WHERE user_id = :user_id',
    'params' => [':user_id' => Yii::$app->user->identity->id],
    'totalCount' => $totalCount,
    'key' => 'rider_id',
    'pagination' => [
        'pageSize' => 10,
    ],
    'sort' => [
        'attributes' => [
            'cagories_category_id',
            'rider_id',
            'rider_firstname',
            'rider_email:email',
            'rider_no_tlpn',
        ]
    ]
]);

$models = $dataProvider->getModels();

return $this->render('index', [
    'searchModel' => $searchModel,
    'dataProvider' => $dataProvider,
]);

This is my view index:

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    // 'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        [
            'label' => 'Category Name',
            'attribute'=>'cagories_category_id',
            'value' => 'cagoriesCategory.category_name', <---Can't work again
        ],
        [
            'label' => 'BIB',
            'attribute'=>'rider_id',
        ],
        'rider_firstname',
        'rider_email:email',
        'rider_no_tlpn',

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

Before I use sqldataprovider, it can call from model function have relation, after use sqldataprovider can't work. How to get relation table value??? then before use it, i can to merge rider_firstname and rider_lastname with return $model->rider_firstname . " " . rider_lastname; after use sqldataprovider can't work too??

Answer 1

SqlDataProvider returns data as an array so You can't able to access related object with $dataProvider->models()

either you have to use ActiveDataProvider or change your Sql of SqlDataProvider with join condition

sql='sql' => 'SELECT * FROM riders inner join '. Categories::tableName() .' as c on c.category_id=riders.cagories_category_id WHERE user_id = :user_id'