How to use the Search Join Field
This field is designed to work in a List & Search Type and is useful when there is more than one table that is to be searched. An example, is to retrieve all of the users from a specific User Group or a list of articles created by a specific User Group.
Set Up
Basically the Search Join Field can be viewed as having two tables which are read from right to left. This is because the Right Table is assumed to be known by the query. Each row is its own join line or parameter in the SQL result.
In this example, the id of the #__users row is known, and matches the rows in the #__user_usergroup_map table. So this example adds the following SQL statement:
LEFT JOIN `#__usergroups` AS t2 ON t2.id = t1.group_id
At this point, the SQL statement for the List & Search Type has been altered, but nothing has been returned. This is due to the fact that no value has been matched.
Create the where clause
When a field is added to the List & Search Type, it adds conditions to the SQL query. In this example, a hidden text box is added with a default value of a User Group. These results will always be the same User Group which is defined by the Administrator.
Another option is to use a Select Dynamic Field and allow the User to select the user group. All that is required, is to define the query of the Select Dynamic Field. In both cases the storage is defined:
Standard | User Group | id to match the settings of the Left Table.