Another Many_Many question

Yii 1.1 General Discussion
1

So I’m finally starting to get it. I can retrieve records from Many_Many relationships using the junction table model. Still working on actually saving them but that isn’t my question for now.

I have been ready many online tutorials and I think some of them have older workarounds for previous versions of Yii, or are just ‘bad’ approaches. Therefore, for myself and other newbies I wanted to clear this up.

For my examples we’ll use the following tables/models:

Posts

Categories

Posts_Categories

I just want to be clear on best practice for Many_Many.

Assumptions:

  1. You need 3 tables and models with proper relations() setup

One for each data table(Posts, Categories), and one for the junction table (Posts_Categories).

  1. The junction table needs only relations() and tableName() defined

  2. For the junction table (Posts_Categories) you can have:

a. JUST the two foreign keys (post_id, category_id)

b. An autoincrementing primary key (id) plus the two foreign keys (post_id, category_id)

Both choices are acceptable.

Questions:

  1. Are there any circumstances would you build a controller and views for the junction table(Posts_Categories)?

  2. Why would you ever setup a HAS_MANY relationship between a data table and its junction table?

example:

Posts HAS_MANY Posts_Categories

Posts_Categories BELONGS_TO Posts

Thanks!

2

  1. No need to generate anything other than the model itself. A pivot table should be managed by it’s owner models IMO.

  2. Relations example:

‘Project’ model:


'bugTrackers' => array(self::MANY_MANY, 'Tracker', '{{project_tracker}}(project_id, tracker_id)'),

‘Tracker’ model:


'bugProjects' => array(self::MANY_MANY, 'Project', '{{project_tracker}}(tracker_id, project_id)'),

‘project_tracker’ is a pivot table with only two fields: ‘project_id’ and ‘tracker_id’.

Gii is actually capable to generating models with the right relationships.

3

Hi, I am actually facing a similar problem. If I understand correctly, you suggest not using the pivot model, since the Project and Tracker models will take care of the requirements?

4

No, it’s using a pivot table called ‘project_tracker’ - where tracker is bound to tracker_id and project is bound to project_id. :)

5

What if you had a column in project_tracker that you wanted to access? Wouldn’t you then setup a HAS_MANY relation from each of the models to project_tracker?

6

Now that I have a little more experience I can say Yes - in this case you would have a separate HAS_MANY relation to access/manipulate the additional data fields.

I was reading up on this topic at StackOverflow and there were many suggestions that if you find yourself in this situation it probably means your DB design needs fixing. I am indeed in that situation and have one table with MANY_MANY with additional fields and have it working as I want… but am curious how else to structure it anyway.