Plugins: Managing Related Records

Question

I am working on a plugin that is requiring several DB tables, some of which are relational pivot tables. This is more of a general how-to, but also best practice question on managing those relationships. I can post code examples if requested, but I'm more curious if A) I'm missing anything withing Craft and/or Yii or B) if there are already ways to do what I'm asking.

As far as I can tell, Yii does have a way to automatically manage related records, in either a HAS_MANY or MANY_MANY relationship. Meaning, you need to manually save them and also manually how they are updated or deleted.

This is a basic example of the HAS_MANY relationship:

Table A (HAS_MANY Table B records)
----------
id (PK)
attributes

Table B
----------
id (PK)
attributes

Table C (pivot)
----------------
id (PK)
aId (FK)
bId (FK)

I have all of the models and records setup (I think) correctly. If I understand Yii correctly, when saving Table A records and relating Table B records via the pivot Table C, this has to be done via separate calls to TableCRecord->save(). This seems fine, but the question is, is there a way or best practice to handle updating and deleting these relations? And, is there a better way design-wise to handle this either with functionality within Craft or Yii?

Any help or advice is greatly appreciated. If you need more info, I will be happy to provide whatever is needed.

Thanks! Michael

Answer 1

You can see an example of how we're doing this exact scenario in Craft by looking in the database at the craft_users, craft_usergroups and the join table for the two, craft_usergroups_users as well as their related record classes in craft/app/records, UserRecord, UserGroupRecord and UserGroup_UserRecord.

UserRecord defines its relation to UserGroupRecord like so:

$relations['groups']  = array(static::MANY_MANY, 'UserGroupRecord', 'usergroups_users(userId, groupId)');

Essentially, "I'm related to UserGroups, through the usergroups_users table using the columns userId and groupId".

That way, if you're sticking purely with ActiveRecord, you don't have to worry about manually updating the usergropus_users table... Craft/Yii/ActiveRecord will do it for you.