Been a while since I did joins, and admittedly not something I look forward to! Here’s what I have:
Table user
- id
Table profile
- id
- name
Table user_profile
- id
- user_id
- profile_id
I just need to output the NAME of all the profiles that a user has (a user can have many profiles). What model relation do I need to create to make this work? I am outputting this on a USER view by the way.
Why do you use 3 tables? Isn’t it enough just to use 2 tables:
User
Profile
id
name
owner_id => this is a foreign key to User(id)
You need 3rd table only if relation between User and Profile is MANY_MANY.
But a profile can only belong to a one user?
If so, then it’s the same as User-Post relation in the Guide: http://www.yiiframework.com/doc/guide/1.1/en/database.arr
An example of MANY_MANY is Post-Category by the same link.
No a profile can belong to many users! Example:
Table user
id: 1
id: 2
Table profile
id: 1 | name: Business
id: 2 | name: Personal
Table user_profile
id: 1 | user_id: 1 | profile_id: 1
id: 2 | user_id: 1 | profile_id: 2
id: 3 | user_id: 2 | profile_id: 1
You don’t need “id” column in the “user_profile” table. Use (user_id, profile_id) as a primary key. As I said in my previous post, take a look at the Guide (Post’s relations).
I thought Yii always needs an auto-increment ID field? Like in CGridView it uses the ‘id’ field to view/update/delete records. That’s why I included it there.
There is no word about this in the documentation. Yii just helps to build an application and doesn’t impose any restrictions on your database schema. Also, you don’t need to create a special class for “user_profile” table, and I don’t think you’ll want to display this table’s rows on some page.
A little note: ID field is one of antipatterns described in the Bill Karwin’s “SQL Antipatterns” book. In a few words, making ID field in any table is senseless sometimes (will you need to access “user_profile” rows by “id”?) and naming this field “id” can also be a bad practice (it is better to use “u_id” or “user_id”, “p_id”). But you can disagree with it 