SQL Query to Ignore Usergroups with no Users

John L.

Well-known member
#1
Hi All,

Quick question, in an addon I'm working I am searching for all the usergroups in XenForo:
PHP:
    public function getUserGroups()
    {
        return $this->_getDb()->fetchAll('
        SELECT user_group_id, title
        FROM xf_user_group
        WHERE user_group_id !=1
        ORDER BY user_group_id
        ');
    }
Is there an SQL query that will allow me to ignore usergroups that do not have any users in them? Appreciate the help!
 

Brogan

XenForo moderator
Staff member
#2
The xf_user_group table doesn't include a count of members in each group.

I believe the only tables which have a user <-> user group relation in them are (from memory) the xf_user and xf_user_group_relation tables.
So you would have to use one of those to perform the check against.
 

John L.

Well-known member
#3
The xf_user_group table doesn't include a count of members in each group.

I believe the only tables which have a user <-> user group relation in them are (from memory) the xf_user and xf_user_group_relation tables.
So you would have to use one of those to perform the check against.
Thanks Brogan, I suppose it would be better to right a function to get that count for each usergroup and then I'll just write a conditional based off that value to do what I need to do.
 

Robust

Well-known member
#4
Thanks Brogan, I suppose it would be better to right a function to get that count for each usergroup and then I'll just write a conditional based off that value to do what I need to do.
There's a few more efficient things you can do. For example, you can store usergroup numbers in the table (add a column) and then add a where condition to the query.

Or, better yet, you can just add a join to that query I think. My brain is a bit switched off right now but if you did a join to ensure results exist, then that'd work too.
 
Top