SQL Custom Where
SQL Custom Where parameter allow you to set a static filters on Users Lists/Tables or List module.
This filter is permanent, also after search.
This is very helpful to create various list of different user types (Teachers/Students, Staff/Members, ....)
Here are some examples of more complex queries
Filter Users with custom fields (you can use every SQL operator)
alias_of_field = 'value_to_filter'
Custom field equal to some value
alias_of_field 'value_to_filter'
Custom field not equal to some value
alias_of_field IN ('value_to_filter1','value_to_filter2','value_to_filter3')
Custom field have one or more of values
alias_of_field NOT IN ('value_to_filter1','value_to_filter2','value_to_filter3')
Custom field have not one or more of values
NOTE: fields like checkbox or select (with multiple value enabled) store the data in DB with JSON format, so something like this
["music","art","design"], so to filter users you need to use SQL LIKE command, example to have users interested to music:
interests LIKE '%"music"%'
Online Users
user_id IN (SELECT DISTINCT userid FROM #__session WHERE client_id=0)
Include some Users
user_id IN (userid1,userid2,...)
Exclude some Users
user_id NOT IN (userid1,userid2,...)
Include some Usergroups
group_id IN (groupid1,groupid2,...)
Exclude some Usergroups
group_id NOT IN (groupid1,groupid2,...)
User From 2 Groups at the same time
user_id IN (SELECT user_id FROM #__user_usergroup_map WHERE group_id IN (groupid1,groupid2) GROUP BY user_id HAVING count(user_id)=2)
User From 3 Groups at the same time
user_id IN (SELECT user_id FROM #__user_usergroup_map WHERE group_id IN (groupid1,groupid2,groupid3) GROUP BY user_id HAVING count(user_id)=3)
Get Users with some Joomla articles
user_id IN (SELECT DISTINCT created_by FROM #__content)