Easy Profile WIKI
Toggle navigation
  • Home
  • System Requirements
  • Installation
  • Configuration
  • Custom Fields
    • Create Custom Field
    • Custom Fields Types
    • HTML Delimiter Field Type
    • Conditions
    • Translate Fields
  • User List/Search
  • Modules
    • Users List Module
    • Search Module
    • Users Map Module
    • SQL Custom Where Parameter
  • Content Plugin
  • Author Plugin
  • Articles Tab Plugin
  • Joomla Search Plugin
  • Ajax UserList Plugin
  • Social Connect Plugin
    • Intro
    • Setup Facebook Login
    • Setup Twitter Login
    • Setup Google+ Login
    • Setup LinkedIn Login
    • Setup Instagram Login
  • Social Network Plugin
    • System Requirements
    • Installation
    • Configuration
    • Make your language
  • Jomsocial Bridge
  • Import User Plugin
  • Integrations
    • Acymailing
    • EasyDiscuss
    • EasyBlog
    • Komento
    • Kunena
    • Joomgallery
    • JReviews
    • UddeIM
    • Emerald
    • Membership Pro
  • Tips & Tricks
  • Developers
    • JsnUser Object
    • Triggers
  • Changelog
  • Support

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)