Organic Groups is a great module, but it ain't half confusing, especially when it comes to using Views' relationships. What I wanted to do was get a list of groups that had no users. I had to step back and understand a lot first, so am explaining it here for future reference and for others.
The basics/typical organic group (OG) set-up
A 'group' is a node. A group can have users and other nodes attached to it by way of 'memberships'. Each attached node and user has it's own relationship record linking it to the group node. It can get more complex than this (apparently groups etc. do not need to be nodes), but let's focus on this typical use case.
Let's understand what's going on at table level
The node table holds a record for a group. This group's
nid is then found in the
gid field (g for group). The table also holds
group_type, which for our case will be
node because that's where our groups are defined.
The og_membership table then has an
etid (entity id) and entity_type (in our case, this will either be
Now what are all these Views relationships?
OG membership: Group Node from OG membership
This one assumes the node you started with is a group content node, because it joins it to
og_membership.etid and then goes on to join the group node.
So great for getting the group node(s) that relate to particular group content.
OG membership: Group User from OG membership
This one is useful if you have users defining a group. So this does not fit out typical case. Like the previous one, it assumes you're starting with a content node and you want the group, but for cases where the group is a user.
OG membership: Node from OG membership
Again, this one assumes you're starting with group content, and it looks for other content in the same group(s).
OG membership: OG membership from Node
This one is more raw because it just joins the membership record itself again assuming that you're starting with group content. Membership records are usually just behind-the-scenes ways to get to related content, but one gives you access to them directly.
OG membership: OG membership from Node group
Here it assumes you start with the group node, not group content. As the last one, this simply brings in the og_membership rows.
OG membership: OG Roles from membership
Assumes you start with group content, and gives you access to all the OG-specific user roles for all users related to the same group(s) as the content.
OG membership: Type
og_membership_type table for the group content.
OG membership: User from OG membership
Finally, this one starts with group content and finds users related to the same group(s).
Back to the problem: finding groups without users
I'm familiar with SQL, so I aproached the problem thinking the easiest way would be to add the condition into the LEFT JOIN, and then test on NULL:
SELECT g.title FROM node g LEFT JOIN og_membership m ON g.nid = m.gid AND m.group_type = 'node' AND m.entity_type = 'user' WHERE g.type = 'my_group_node_type' AND m.etid IS NULL;
However, you can't do this in Views, because the JOIN conditions are set-up by the relationships; you can only affect the WHERE and the HAVING conditions.
Also, in looking at the tables I found that the Admin user (uid 1) had somehow been given membership to all the groups (which were created programmatically). I also found that OG will not let you delete this user from the group - you can try in the groups interface and it says "Performed Remove from group on 1 item." in a green box, but it hasn't actually done anything!
So this moved the goal posts. Again, in SQL I would just do:
SELECT g.title FROM node g LEFT JOIN og_membership m ON g.nid = m.gid AND m.group_type = 'node' AND m.entity_type = 'user' AND m.etid > 1 WHERE g.type = 'my_group_node_type' AND m.etid IS NULL;
... but Views won't let me. So I resorted to a far less efficient
HAVING COUNT solution as follows
- First, my View begins with nodes, with a node type filter for my group bundle.
- I need the OG membership from Node group relationship, which will give me access to all the membership records for this group.
- I need to filter this relationship on Entity_type = user. This means I now have the groups and their users (i.e. I've excluded the groups content nodes from the relationships).
- I need Use aggregation turned on.
- I need to add another filter on the OG membership from node group relationship, this time on Entity Id = 1, with aggregation set to Maximum.
So what's that done? Well it's looking at the maximum user Id for a group. As Admin will always have user id 1, this works to find groups whose only user member is admin.
Phew, that was complicated. Well, if you know a better way, please leave me a comment.