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)

Table B
id (PK)

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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.