Filter

[albersjan]

Hi, I want to filter the dropdown list of an element of type "user plugin" when using a form . Filter has to be limited to those users that are in the same usergroup as the logged user is in.

 

[troester]

That's not built in.
You can change the element type to "databasejoin", pointing to #_users table.
For a databasejoin you can set a filter in "Data - where" or use "Advanced" for complex options.

 

[albersjan]

Thanks

 

[cheesegrits]

What do you mean by "the same usergroup as the logged on user is in", bearing in mind that a user can be in any number of groups.

To limit the join to users that share any group membership with the logged on user, a WHERE filter on the join of ...

{thistable}.id IN (SELECT u.id FROM #__users AS u LEFT JOIN #__user_usergroup_map AS m ON m.user_id = u.id WHERE m.group_id IN (SELECT group_id FROM #__user_usergroup_map WHERE user_id = '{$my->id}')

... or, I think this one would work, without the second subquery, which might make it more efficient if you have a large users table ...

{thistable}.id IN (SELECT u.id FROM #__users AS u LEFT JOIN #__user_usergroup_map AS m1 ON m1.user_id = u.id LEFT JOIN #__user_usergroup_map AS m2 ON  m1.group_id = m2.group_id WHERE m2.user_id = '{$my->id}')

However, this may be of limited use if (say) all your users are in the "Registered" group, and you are trying to separate them by some other secondary group.

If you needed to exclude "common" groups, you could add a NOT IN(... list of group ids) ...

{thistable}.id IN (SELECT u.id FROM #__users AS u LEFT JOIN #__user_usergroup_map AS m1 ON m1.user_id = u.id LEFT JOIN #__user_usergroup_map AS m2 ON  m1.group_id = m2.group_id WHERE m2.user_id = '{$my->id}' AND m2.group_id NOT IN(1,2,3))

Replace 1,2,3 with the group ids you want to ignore. Or if it's easier, use IN (4,5,6) and specify the set of groups you are interested in.

-- hugh