How To Retrieve Columns From Both Tables Of A Join Query

Yii 2.0 General Discussions
1

I have 3 tables

PLAYER

id

first_name

last_name

SKILL

id

name

PLAYER_SKILL

id

playerId

skillId

level

How can I implement this query to show skill name and level for a player?


SELECT name, level FROM skill inner join player_skill on player_skill.skillId = skill.id where playerId = 15;

In the Player model I’ve implemented these relations, but cannot get both name (from one table) and level (from another)


public function getPlayerSkills()

    {

        $skills = $this->hasMany(PlayerSkill::className(), ['playerId' => 'id']);

        return $skills;

    }


    public function getSkills()

    {

        $skills = Skill::find()->innerJoin('player_skill')->where(['playerId' => $this->id]);

        return $skills;

    }
2

Instead of trying to get Skill.Name from the Player (with the attempted join), you could have Model for Skill which is linked via the PlayerSkill model with something like (the syntax may not be correct as I have not tested):

public function getSKill()

{


    return $this->hasOne(Skill::className(), ['id' => 'id']);


}

And in this manner, you can get all the PlayerSkills for the Player, and for each PlayerSkill you have access to the Skill.name

3

Hey thanks for the hint!

So this getSkill should be put in class PlayerSkill and then how should I use it?

something like this?


PlayerSkill::find()->innerJoin('Skill')->all()
4

You would not need to use a join as the framework would take care of things for you using the models.

If the models are setup correctly (ie, you have a getPlayerSkills function that returns the (one to many) (Model)PlayerSkill in (Model)Player and a getSkill function that returns a (Model)Skill in (Model)PlayerSkill) then you would simply use as follows:

// Get a player (assuming one exists with id=1

$player = Player::find(1);

foreach( $player->playerSkills as $playerSkill)

{

echo $playerSkill->level;


echo $playerSkill->skill->name;

}

Also, in my previous post, I mentioned the getSkill method that needs to be added to PlayerSkill needs something like:

return $this->hasOne(Skill::className(), [‘id’ => ‘id’]);

But based on your example it should be something like:

return $this->hasOne(Skill::className(), [‘id’ => ‘skillid’]);

Again, my syntax is not 100% correct, but I hope you get the idea.

5

Yes, got it!

Thanks!

6

Sorry one more thing.

This is OK to retrieve the data in a programmatic way, but what about creating a data provider and a search filter to populate a gridView like this?


$searchSkillModel = new SkillSearch;


 $dataProvider = new ActiveDataProvider([

                'query' => $player->getPlayerSkills(),

                'pagination' => [

                    'pageSize' => 20,

                ],

            ]);




return $this->render('@common/views/player/view', [

                'model' => $this->findModel($id),

                'dataProvider' => $dataProvider,

                'searchSkillModel' => $searchSkillModel,

                'isAdmin' => $isAdmin,

            ]);




 <?= GridView::widget([

        'dataProvider' => $dataProvider,

        'filterModel' => $searchSkillModel,

        'columns' => [

            ['class' => 'yii\grid\SerialColumn'],


            'name',

            //'description',

            'level',

        ],

    ]); ?>

Now I get an error when trying to display PlayerSkill->skill->name

7

Because name is not part of playerskill you have to put the reference to the relation (skill->name provided you have put that relation in the PlayerSkill model).

I remember in CGridView you could do something like the following:




'level',

array(

   'name'=>'Name',

   'value'=>'$data->skill->name',

),

I believe you can do the same in GridView (but I have not yet tried)