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

Discussion in 'XenForo Development Discussions' started by John L., Feb 15, 2016.

  1. John L.

    John L. Well-Known Member

    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!
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

    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. likes this.
  3. John L.

    John L. Well-Known Member

    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.
     
  4. Robust

    Robust Well-Known Member

    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.
     

Share This Page