Thread

Hi, I have the following scenario:

There are are three tables involved in this: groups, user_types, and resources.

user_types have a many-to-many relationship with groups - The user can create user_types, say one named ‘painter’ and one named ‘foreman’. They can then choose which user_types are available to each group. For instance, assuming there are two groups ‘a’ and ‘b’, the user might decide that group ‘a’ has painters and foremen, and that group ‘b’ has only painters.

So when creating a resource, such as a manual, the user specifies which user_types can access them in each group. So the user might create manuals 1 and 2, and assign manual 1 to painters in group a, and manual 2 to painters in group b

My tentative DataMapper solution to this is to have a many-to-many relationship between groups and user_types using a join table called groups_user_types. Then, that join table has a many-to-many relationship to resources, using a join table called groups_user_types_resources. I am unsure if this can be done in DM, as I can’t find any documentation / examples with this particular relationship. Can I do this in DM, and if so, how? And if not, any suggestions on how to implement a solution blending DM with CodeIgniter’s Active Record class as painlessly as possible?

Db tables would look like this:

resources
---------
id|name
 1|manual1
 2|manual2

user_types
----------
id|name
 1|painters
 2|foremen

groups
------
id|name
 1|a
 2|b

groups_user_types
-----------------
id|group_id|user_type_id
 1|       1|           1
 2|       1|           2
 3|       2|           1

groups_user_types_resources
---------------------------
id|resource_id|groups_user_type_id
 1|          1|                  1
 2|          1|                  3
 3|          2|                  1
 4|          2|                  2

I’d appreciate any corrections and insight you could provide.

Hey there.

Ah yes I know your ‘issue’ you effectively want a join with 3 tables. There is a way to do this.

Make a DM object out of groups_user_types_resources. Give that DM object a ‘has_one’ relation with each of the tables/DM objects.

😊

(If you cant get something done in DM you can always use the query() method and write your own query as well, but I have not had to use it just yet)

I’m not sure that will work for my situation. I have to be able to be able to separate the relationship between user_types and groups and then for each combination of user_type and group, I add as many resources as needed. So I tried making a model for groups_user_types_resources, with a resource_id and a groups_user_type_id, but I don’t know how to use DM to access the join table.

If I do it with a join table with all three, won’t that prevent me from separating the assigned user_types for each group from the assigned resources for that groups_user_types combination? Or are you suggesting that I use the table to either have a user_type_id and group_id (to represent the first combination), or all three (to represent a particular permission for a resource for that combination of user_type and group?

Ahh I see

Well you can make a DM object out of two and add a ‘has many’ to the third with a join table, effectively a join table on a join table.

If you want to know how to work with join fields I recommend reading this bit: http://datamapper.maglok.nl/pages/joinfields.html

So in that case do I keep

  groups_user_types

and also create a different model that is essentially the same but named differently so I can reference it in the has_many / has_one properties?

Nevermind, I think I understand. I just need to create a model for ‘groups_user_types’, and define has one group and has one user_type. Those two models will now do has many groups_user_types instead of each other. it’s essentially defining the join table as a model instead of it being only in the database.

Thanks for your help, Maglok.

Not a problem. 😊