Yii MANY_MANY relation does not work for me

Yii 1.1 General Discussion
1

Hi everybody,

I am now discovering the Yii framework and doing it by trying to develop a simple application which allows to create Leagues and assign Players to it. The relationship between these is many to many (a player can subscribe to many leagues, and one league contains many players). So I have three db tables - tbl_league, tbl_player and tbl_league_player and I created foreign keys like this:


ALTER TABLE tbl_league_player ADD FOREIGN KEY league_id_idxfk (league_id) REFERENCES tbl_league (id);


ALTER TABLE tbl_league_player ADD FOREIGN KEY player_id_idxfk (player_id) REFERENCES tbl_player (id);

Now I am trying to display League with the list of all players subscribed to it, so my actionView in LeagueController says:




public function actionView($id)

{


    $issueDataProvider = new CActiveDataProvider('Player', array(

        'criteria' => array(

            'condition' => 'league_id=:leagueId',

            'params' => array(

                ':leagueId' => $this->loadModel($id)->id

            ),

        ),

        'pagination' => array(

            'pageSize' => 1,

        ),

    ));


    $this->render('view',array(

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

        'issueDataProvider' => $issueDataProvider,

    ));

}

What I am trying to do here is to get all players from the Player table subscribed to a particular league ID. For this I have the Player model with relation defined like this:




public function relations()

{

    // NOTE: you may need to adjust the relation name and the related

    // class name for the relations automatically generated below.

    return array(

        'League' => array(self::MANY_MANY, 'League', 'tbl_league_player(player_id, league_id)'),

    );

}

The problem is that I go to, for example, league/view&id=2, I am getting this error: "SELECT COUNT(*) FROM tbl_player t WHERE league_id=:leagueId" so it looks like the relation does not work. What am I missing?

Thanks a lot!

2

I believe your criteria is stating to compare the league_id from the Player table. It won’t automatically do the join to the other table to perform the filtering. Take a look at the CDbCriteria::with property to make it join to other tables, although I’ve had problems doing this with stat queries in the past.

That said I would probably just load the League and use $league->players in the views.

3

This may help.

4

Thanks for your help, guys. I was under the impression that models can handle relations via the third table, if not - whats the point specifying the MANY_MANY relation in the model?

Anyway - the main problem now is that if I use another way to obtain data - how would I display them? I was trying to use the [font="Courier New"]zii.widgets.CListView[/font] but, apparently, this only works with CActiveDataProvider, which I cant use because of the relation issue. Would you be able to help?

Thanks a lot!

5

Did you try this?




public function actionView($id)

{

  $issueDataProvider = new CActiveDataProvider('Player', array(

    'criteria' => array(

      'with'=>'league',

      'together'=>true,

      'condition' => 'league_id=:leagueId',

      //or possibly 'condition' => 'league.league_id=:leagueId',

      'params' => array(

        ':leagueId' => $this->loadModel($id)->id

      ),

    ),

    'pagination' => array(

      'pageSize' => 1,

    ),

  ));


  $this->render('view',array(

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

    'issueDataProvider' => $issueDataProvider,

  ));

}

/Tommy

6

Thanks Tommy,

but this does not work either. I am getting error:

Relation "league" is not defined in active record class "Player".

7

I notice you named the relationship "League".

/Tommy