Where is Drupal Organic Group Role Data stored? How do I access it?

Question

I've created a custom Role for my Group. I've assigned a user, who was a member of the Group, to this special Role. Now, I want to access a user's Role in the Group via PHP, but I can't find it anywhere.
I've pored over the Devels of both the User and Group. I can access the fact that the user is a member of the group in the user's group_audience array, but not what their role is in that group.

Any advice?

Edit: Drupal 7

@Dan I've just updated my answer; I think it will now answer your question specifically. I hope you got it sorted, in any event, but please do give me a shiny green tick if I actually answered your question ;o) — Owen Blacker, Feb 17 '12 at 11:22

Owen Blacker · Answer 1 · 2012-02-17T11:21:22.780

Edit: There's some background here first, then the actual answer is after that.

Background

Looking in the mySQL database on our development server, there seems to be a handful of OG-related tables in our Drupal database. I'm pretty sure the version running on the devserver is og-7.x-1.x-dev.

og
og_membership
og_membership_type
og_menu
og_role
og_role_permission
og_users_roles
field_data_og_membership_request
field_revision_og_membership_request

Their definitions look like this:

mysql> describe og;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| gid         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| etid        | int(10) unsigned | NO   | MUL | 0       |                |
| entity_type | varchar(32)      | NO   |     |         |                |
| label       | varchar(255)     | NO   |     |         |                |
| state       | int(11)          | NO   |     | 1       |                |
| created     | int(11)          | NO   |     | 0       |                |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

mysql> describe og_membership;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type        | varchar(255)     | NO   |     |         |                |
| etid        | int(10) unsigned | NO   | MUL | 0       |                |
| entity_type | varchar(32)      | NO   |     |         |                |
| gid         | int(11)          | NO   | MUL | NULL    |                |
| state       | varchar(255)     | YES  |     |         |                |
| created     | int(11)          | NO   |     | 0       |                |
+-------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> describe og_membership_type;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | NO   | UNI |         |                |
| description | varchar(255) | NO   |     |         |                |
| status      | tinyint(4)   | NO   |     | 1       |                |
| module      | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe og_menu;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| gid       | int(11)      | NO   | PRI | NULL    |       |
| menu_name | varchar(128) | NO   | PRI |         |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe og_role;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| rid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| gid   | int(11)          | NO   |     | NULL    |                |
| name  | varchar(64)      | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe og_role_permission;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| rid        | int(10) unsigned | NO   | PRI | NULL    |       |
| permission | varchar(64)      | NO   | PRI |         |       |
| module     | varchar(255)     | NO   |     |         |       |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe og_users_roles;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
| rid   | int(10) unsigned | NO   | PRI | 0       |       |
| gid   | int(11)          | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe field_data_og_membership_request;
+------------------------------+------------------+------+-----+---------+
| Field                        | Type             | Null | Key | Default |
+------------------------------+------------------+------+-----+---------+
| entity_type                  | varchar(128)     | NO   | PRI |         |
| bundle                       | varchar(128)     | NO   | MUL |         |
| deleted                      | tinyint(4)       | NO   | PRI | 0       |
| entity_id                    | int(10) unsigned | NO   | PRI | NULL    |
| revision_id                  | int(10) unsigned | YES  | MUL | NULL    |
| language                     | varchar(32)      | NO   | PRI |         |
| delta                        | int(10) unsigned | NO   | PRI | NULL    |
| og_membership_request_value  | longtext         | YES  |     | NULL    |
| og_membership_request_format | varchar(255)     | YES  | MUL | NULL    |
+------------------------------+------------------+------+-----+---------+
9 rows in set (0.00 sec)

mysql> describe field_revision_og_membership_request;
+------------------------------+------------------+------+-----+---------+
| Field                        | Type             | Null | Key | Default |
+------------------------------+------------------+------+-----+---------+
| entity_type                  | varchar(128)     | NO   | PRI |         |
| bundle                       | varchar(128)     | NO   | MUL |         |
| deleted                      | tinyint(4)       | NO   | PRI | 0       |
| entity_id                    | int(10) unsigned | NO   | PRI | NULL    |
| revision_id                  | int(10) unsigned | NO   | PRI | NULL    |
| language                     | varchar(32)      | NO   | PRI |         |
| delta                        | int(10) unsigned | NO   | PRI | NULL    |
| og_membership_request_value  | longtext         | YES  |     | NULL    |
| og_membership_request_format | varchar(255)     | YES  | MUL | NULL    |
+------------------------------+------------------+------+-----+---------+
9 rows in set (0.00 sec)

(I've removed the empty Extras column from the two field_* tables to avoid horizontal scrolling.) Hope that helps?

My workings

Having just had to mess with this myself on my own Drupal site, it turns out that og_membership has a row for each user in each group (I've abbreviated the type, which read og_membership_type_default):

mysql> select * from og_membership where gid = 324 and etid = 182905;
+--------+-----------------+--------+-------------+-----+-------+------------+
| id     | type            | etid   | entity_type | gid | state | created    |
+--------+-----------------+--------+-------------+-----+-------+------------+
| 223562 | og_m..._default | 182905 | user        | 324 | 1     | 1329388409 |
+--------+-----------------+--------+-------------+-----+-------+------------+
1 row in set (0.01 sec)

In this row, the id is an autoincrementing identifier for the table og_membership, the etid corresponds to the users.uid for the user in question and gid corresponds to to the og.gid for the group in question.

So if I run the query

update og_membership set gid = 38 where gid = 324;

then all the members of group #324 are moved to group #38 (which is what I've just needed to do, due to an error in an import script).

I think the answer to your question is that the og_membership.type corresponds to an og_membership_type.name. Looking at that table:

mysql> select * from og_membership_type;
+----+----------------------------+-------------+--------+--------+
| id | name                       | description | status | module |
+----+----------------------------+-------------+--------+--------+
|  1 | og_membership_type_default | Default     |      2 | og     |
+----+----------------------------+-------------+--------+--------+
1 row in set (0.00 sec)

, I think the og_membership_type.status corresponds to the og_role.rid:

mysql> select * from og_role;
+-----+-----+----------------------+
| rid | gid | name                 |
+-----+-----+----------------------+
|   1 |   0 | non-member           |
|   2 |   0 | member               |
|   3 |   0 | administrator member |
+-----+-----+----------------------+
3 rows in set (0.00 sec)

Actual answer

So I think the query you want is:

select og_role.name
from og_role
    inner join og_membership_type on og_role.rid = og_membership_type.status
    inner join og_membership on og_membership_type.name = og_membership.type
where og_membership.gid = $group_id;

where $group_id is the og.gid of the group in question. (The ID that appears in the URL is the og.etid, so you might want to add another join to that query.

Where is Drupal Organic Group Role Data stored? How do I access it?

1 Answers1

Background

My workings

Actual answer