I have an application that uses a nested set model class to organise my data, however I'm trying to write a query that will count the total amount of people in each group.
table: person_to_group
----+------------+-----------
|ID | Person_ID | Group_ID |
----+------------+-----------
| 1 | 3 | 1 |
| 2 | 3 | 2 |
| 3 | 5 | 2 |
| 4 | 7 | 3 |
----+------------+-----------
table: groups
----------+--------------+--------------+-------------
|Group_ID | Group_Name | Group_Left | Group_Right |
----------+--------------+--------------+-------------
| 1 | Root | 1 | 6 |
| 2 | Node | 2 | 5 |
| 3 | Sub Node | 3 | 4 |
----------+--------------+--------------+-------------
My query will be run within a while loop which lists all the group names.
I'm trying to accomplish a result like this:
Root - Members (3) <------- Notice that I want subgroups to be included in the count, and I don't want members to be counted more than once.
Any help would be much appreciated!
Since you run it inside a while loop with group names you can probably get the group's left and right values instead and put them into this query:
SELECT count(*) AS members FROM
( SELECT DISTINCT ptg.person_ID FROM groups g
JOIN person_to_group ptg
ON ptg.Group_ID=g.Group_ID
WHERE g.Group_Left >= 1
AND g.Group_Right <= 6 ) m
This works for a given group. If you wanted to get a full list of groups with member count for each in one query you would have to use something like:
SELECT m.Group_Name, count(*) AS members FROM
( SELECT g.Group_ID, g.Group_Name, ptg.Person_ID
FROM groups g
JOIN groups gsub
ON gsub.Group_Left >= g.Group_Left AND gsub.Group_Right <= g.Group_Right
JOIN person_to_group ptg
ON gsub.Group_ID = ptg.Group_ID
GROUP BY g.Group_ID, ptg.Person_ID ) m
GROUP BY m.Group_ID
However I think the first solution with a loop outside of sql would be more efficient.