SQL Query to Ignore Usergroups with no Users

[John L.]

Hi All,

Quick question, in an addon I'm working I am searching for all the usergroups in XenForo:

    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!

 

[Paul B]

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.]

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]

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.