[Deprecated] DMZ 1.5.3 (DataMapper OverZealous Edition)

[eluser]OverZealous[/eluser]
@macigniter
The usual way to do this is to have a dedicated class to track the relationships.

I have a small example here, but the basic process is that you would create a class that was called, for example, UserOfficeGroup.

This class would have a $has_one relationship with User, Office, and Group. User, Office, and Group would otherwise be unrelated. Each of the other objects would have a $has_many relationship back to UserOfficeGroup. The table for UserOfficeGroup only contains four columns:
• id
• user_id
• office_id
• group_id

You would run queries like this:

Code:

$user = ...
$offices = new UserOfficeGroup();
$offices->where_related($user);
$offices->include_related('office', '*', FALSE); // the FALSE gets all office fields without a prefix
$offices->include_related('group', '*'); // replace * with an array of fields, if you prefer
$offices->get();
foreach($offices as $office) {
    // NOTE: The office ID must be gotten as $office->office_id
    $office_name = $office->name;
    $group_name = $office->group_name;
}

Which I would probably pack into a method off of the User class, if you use it a lot. You can also swap around User, Office, and Group depending on what the query starts with.

The drawback to this method is that it is more difficult to edit and update the objects from a related point of view. For example, you can't modify and then save the "Office" in the example, because it is really a UserOfficeGroup object. Also you have to remember to manually delete any related UserOfficeGroup objects when deleting a User, and Office, OR a Group.



An alternative method is to add a column to the offices_users relationship join table. I'd call it group_id. Then you can easily look up $office->users and $users->office. If you need the group, it looks like this:

Code:

$user->offices->include_join_fields()->get();
foreach($user->offices as $office) {
    $group = new Group();
    $group->get_by_id($office->join_group_id);  // if there are a lot of offices, this can generate a lot of queries!
}

The drawback to this method is it hard to enforce within the database (no validation rules, for example), and cannot be retrieved with one query (like above).