• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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.