phpgridviewyii2yii2-advanced-app

Yii2 gridview three tables joins


I am a new in yii2 framework, when i trying to join more than two tables than it is not loading in gridview.

I am trying fetch language name from language table which i can get by userlanguage table which has a two field as below. So as per the userID I want to fetch the language details of user. show language name in my

My table structute is:

Users table

from this table i will get userID which is join with userID of userlanguage table and a userlanguage table contains two field

userID
languageID

By getting languageID from userlanguage table I have to show languageName from language table, which contains two field:

languageID 
languageName

A usertable contains:

userID
userName

etc.

 UsersSearch.php(MySearchModel)


    <?php

namespace backend\models;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use backend\models\Users;
use backend\models\Userlanguage;
use backend\models\Language;
use yii\db\Query;


class UsersSearch extends Users
{
    public $languageID;

    public function rules()
    {
        return [
            [['userID', 'countryId', 'userHomeCityID', 'userCurrentCityID', 'userStars', 'userFans', 'userLike', 'userNutural', 'userDontLike'], 'integer'],
            [['userEmail', 'userPassword', 'userName', 'userMobile', 'userCountryCode', 'userAbout', 'userBirthDate', 'userGender', 'userHeightUnit', 'userWeightUnit', 'userBodyColour', 'userEyesColour', 'userHairColour', 'userAdditionalInformation', 'userProfilePicture', 'userCoverPhoto', 'userLastLogin', 'userCreatedDate', 'userStatus', 'userDeviceType', 'userDeviceID', 'userType'], 'safe'],
            [['userHeight', 'userWeight'], 'number'],
        ];
    }


    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }


    public function search($params)
    {

        $query = Users::find();

        // add conditions that should always apply here

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


        $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;
        }




        $query->joinWith(["user"=>function($a){$a->from("users user");}]);

        $query->joinWith(["languages"=>function($c){$c->from("language languages");}]);

        $query->andFilterWhere([
            'userID' => $this->userID,
            'countryId' => $this->countryId,
            'userBirthDate' => $this->userBirthDate,
            'userHomeCityID' => $this->userHomeCityID,
            'userCurrentCityID' => $this->userCurrentCityID,
            'userHeight' => $this->userHeight,
            'userWeight' => $this->userWeight,
            'userStars' => $this->userStars,
            'userFans' => $this->userFans,
            'userLike' => $this->userLike,
            'userNutural' => $this->userNutural,
            'userDontLike' => $this->userDontLike,
            'userLastLogin' => $this->userLastLogin,
            'userCreatedDate' => $this->userCreatedDate,

        ]);

        $query->andFilterWhere(['like', 'userEmail', $this->userEmail])
            ->andFilterWhere(['like', 'userPassword', $this->userPassword])
            ->andFilterWhere(['like', 'userName', $this->userName])
            ->andFilterWhere(['like', 'userMobile', $this->userMobile])
            ->andFilterWhere(['like', 'userCountryCode', $this->userCountryCode])
            ->andFilterWhere(['like', 'userAbout', $this->userAbout])
            ->andFilterWhere(['like', 'userGender', $this->userGender])
            ->andFilterWhere(['like', 'userHeightUnit', $this->userHeightUnit])
            ->andFilterWhere(['like', 'userWeightUnit', $this->userWeightUnit])
            ->andFilterWhere(['like', 'userBodyColour', $this->userBodyColour])
            ->andFilterWhere(['like', 'userEyesColour', $this->userEyesColour])
            ->andFilterWhere(['like', 'userHairColour', $this->userHairColour])
            ->andFilterWhere(['like', 'userAdditionalInformation', $this->userAdditionalInformation])
            ->andFilterWhere(['like', 'userProfilePicture', $this->userProfilePicture])
            ->andFilterWhere(['like', 'userCoverPhoto', $this->userCoverPhoto])
            ->andFilterWhere(['like', 'userStatus', $this->userStatus])
            ->andFilterWhere(['like', 'userDeviceType', $this->userDeviceType])
            ->andFilterWhere(['like', 'userDeviceID', $this->userDeviceID])
            ->andFilterWhere(['like', 'userType', $this->userType])
            ->andFilterWhere(['like', 'userslanguage.userID', $this->userID])
            ->andFilterWhere(['like', 'languages.languageID', $this->getAttribute('languages.languageID')])    
            ->andFilterWhere(['like', 'languages.languageName', $this->getAttribute('languages.languageID')]);

        return $dataProvider;
    }
}

When i echo my query it works fine and return me the data as per i require.

  SELECT `user`.`userID`, `user`.`userEmail`, `languageName`, `l`.`languageID`, `l`.`languageName`, `user`.`userName`, `user`.`userMobile`, `user`.`userGender`, `user`.`userStatus` FROM `users` `user` LEFT JOIN `userlanguage` `ul` ON user.userID = ul.userID LEFT JOIN `language` `l` ON ul.languageID = l.languageID


Users.php(MyModel)





    <?php

namespace backend\models;

use Yii;

class Users extends \yii\db\ActiveRecord
{

    public $languageID;

    public static function tableName()
    {
        return 'users';
    }


    public function rules()
    {
        return [
            [['userEmail', 'userPassword', 'userName', 'userMobile', 'userCountryCode', 'countryId', 'userHomeCityID', 'userCurrentCityID', 'userHeight', 'userHeightUnit', 'userWeight', 'userWeightUnit', 'userBodyColour', 'userEyesColour', 'userHairColour', 'userAdditionalInformation', 'userProfilePicture', 'userCoverPhoto', 'userStatus', 'userDeviceType', 'userDeviceID', 'userType'], 'required'],
            [['countryId', 'userHomeCityID', 'userCurrentCityID', 'userStars', 'userFans', 'userLike', 'userNutural', 'userDontLike'], 'integer'],
            [['userBirthDate', 'userLastLogin', 'userCreatedDate','languageID','languageName'], 'safe'],
            [['userHeight', 'userWeight'], 'number'],
            [['userStatus', 'userType'], 'string'],
            [['userEmail', 'userPassword', 'userProfilePicture', 'userCoverPhoto'], 'string', 'max' => 100],
            [['userName', 'userHeightUnit', 'userWeightUnit'], 'string', 'max' => 20],
            [['userMobile', 'userGender', 'userDeviceType'], 'string', 'max' => 10],
            [['userCountryCode'], 'string', 'max' => 5],
            [['userAbout', 'userDeviceID'], 'string', 'max' => 500],
            [['userBodyColour', 'userEyesColour', 'userHairColour'], 'string', 'max' => 50],
            [['userAdditionalInformation'], 'string', 'max' => 4000],
            [['userEmail'], 'unique'],
            [['userName'], 'unique'],
            [['userMobile', 'countryId'], 'unique', 'targetAttribute' => ['userMobile', 'countryId'], 'message' => 'The combination of User Mobile and Country ID has already been taken.'],

        ];

    }

    public function attributes(){
        return array_merge(parent::attributes(), ['languages.languageID']);
    }

    public function attributeLabels()
    {
        return [
            'userID' => 'User ID',
            'userEmail' => 'User Email',
            'userPassword' => 'User Password',
            'userName' => 'User Name',
            'userMobile' => 'User Mobile',
            'userCountryCode' => 'User Country Code',
            'countryId' => 'Country ID',
            'userAbout' => 'User About',
            'userBirthDate' => 'User Birth Date',
            'userGender' => 'User Gender',
            'userHomeCityID' => 'User Home City ID',
            'userCurrentCityID' => 'User Current City ID',
            'userHeight' => 'User Height',
            'userHeightUnit' => 'User Height Unit',
            'userWeight' => 'User Weight',
            'userWeightUnit' => 'User Weight Unit',
            'userBodyColour' => 'User Body Colour',
            'userEyesColour' => 'User Eyes Colour',
            'userHairColour' => 'User Hair Colour',
            'userAdditionalInformation' => 'User Additional Information',
            'userProfilePicture' => 'User Profile Picture',
            'userCoverPhoto' => 'User Cover Photo',
            'userStars' => 'User Stars',
            'userFans' => 'User Fans',
            'userLike' => 'User Like',
            'userNutural' => 'User Nutural',
            'userDontLike' => 'User Dont Like',
            'userLastLogin' => 'User Last Login',
            'userCreatedDate' => 'User Created Date',
            'userStatus' => 'User Status',
            'userDeviceType' => 'User Device Type',
            'userDeviceID' => 'User Device ID',
            'userType' => 'User Type',
            'languageID' => \Yii::t('app', 'Langauge ID'),
            'email' => \Yii::t('app', 'Language Name'),
        ];

    }

    public function getUser(){
        return $this->hasOne(Users::className(), ['userID' => 'userID']);
    }

    public function getLanguages(){

        return $this->hasMany(Language::className(), ['languageID' => 'languageID'])
            ->viaTable('userlanguage', ['userID' => 'userID']);


    }


}




index.php(My Gridview)



    <?php

use yii\helpers\Html;
use yii\grid\GridView;
use yii\bootstrap\Modal;
use yii\helpers\Url;

$this->title = 'Users';
$this->params['breadcrumbs'][] = $this->title;
?>
<div class="users-index">

    <h1><?= Html::encode($this->title) ?></h1>
    <?php // echo $this->render('_search', ['model' => $searchModel]); ?>

    <p>
        <?= Html::a('Create Users', ['create'], ['class' => 'btn btn-success']) ?>
    </p>

    <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            'userID',
            'userEmail:email',
            'languages.languageID',
            'languages.languageName',
            'userName',
            'userMobile',
            'userGender',
            'userStatus',


            ['class' => 'yii\grid\ActionColumn','header' => 'Action','template' => '{view}&nbsp;{update}&nbsp;{delete}&nbsp;&nbsp;{my_button}', 
            'buttons' => [
                'my_button' => function ($url, $model, $key) {
                    if($model->userStatus == 'Active'){
                    return Html::a('Make It Inactive', ['changestatus', 'id'=>$model->userID]);
                    }else{
                        return Html::a('Make It Active', ['changestatus', 'id'=>$model->userID]);
                    }
                },

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

</div>

It's return this array in $dataprovider

yii\data\ActiveDataProvider Object
(
    [query] => yii\db\ActiveQuery Object
        (
            [sql] => 
            [on] => 
            [joinWith] => Array
                (
                    [0] => Array
                        (
                            [0] => Array
                                (
                                    [users] => Closure Object
                                        (
                                            [static] => Array
                                                (
                                                    [callback] => 
                                                    [alias] => user
                                                )

                                            [this] => yii\db\ActiveQuery Object
 *RECURSION*
                                            [parameter] => Array
                                                (
                                                    [$query] => 
                                                )

                                        )

                                )

                            [1] => 1
                            [2] => LEFT JOIN
                        )

                    [1] => Array
                        (
                            [0] => Array
                                (
                                    [0] => language
                                )

                            [1] => 1
                            [2] => LEFT JOIN
                        )

                )

            [select] => 
            [selectOption] => 
            [distinct] => 
            [from] => 
            [groupBy] => 
            [join] => 
            [having] => 
            [union] => 
            [params] => Array
                (
                )

            [_events:yii\base\Component:private] => Array
                (
                )

            [_behaviors:yii\base\Component:private] => Array
                (
                )

            [where] => 
            [limit] => 
            [offset] => 
            [orderBy] => 
            [indexBy] => 
            [modelClass] => backend\models\Users
            [with] => 
            [asArray] => 
            [multiple] => 
            [primaryModel] => 
            [link] => 
            [via] => 
            [inverseOf] => 
        )

    [key] => 
    [db] => 
    [id] => 
    [_sort:yii\data\BaseDataProvider:private] => 
    [_pagination:yii\data\BaseDataProvider:private] => 
    [_keys:yii\data\BaseDataProvider:private] => 
    [_models:yii\data\BaseDataProvider:private] => 
    [_totalCount:yii\data\BaseDataProvider:private] => 
    [_events:yii\base\Component:private] => Array
        (
        )

    [_behaviors:yii\base\Component:private] => 
)

Solution

  • Finally i got the answer of my question of Yii Forum:

    Follow this below link:

    junction table with search and sorting functionality