Understanding Search Join

By default, SEBLOD search functionality retrieves and displays information from a single database table. The "Search Join" field enables you to combine data from different tables, which are not managed by a unique content type. A “search join” field can be used instead of a multi-stage search when the relationship between steps is not established by identifiers.

The principal is to relate the columns of different tables. The configuration of a “search join” is composed of several “Left Join” lines.These lines are read from right to left. On each line, the right-hand table and column must be known already in the query. So, in the example below, we have a Search & List type based on the the User Content Type. By default, creating this search type is querying the #__users table, so we are able to use that in the Left Join (1) row on the right hand side.


A worked example

The following example will step through using the Search Join field to create a user manager in the front-end of your website.
  1. Add the field “CCK” to the search form view and select variation “hidden”.


  2. Click on button “2” and assign the live value “user” to the “CCK” field


  3. Create a “select simple” field which we will map to the Joomla core field group_id. We do this so that we can have control over the user group options that are available in the search, rather than the JForm Group ID field, which has ALL user groups. The groups we want for the query are the Registered and Manager user groups. The mapping is achieved by using the storage that is shown in the screenshot below:

  4. Create a “left join” field and call it “User Group Join" and add it to the search form view. Complete the fields as below:



    5. What the above example is doing is:

    • Left Join (1): This line is looking at the User ID (id) in the #__users table, and then looking up this same User ID in the user_id column #__user_usergroup_map table 
    • Left Join (2): Following on from the previous line, this line is then looking at the group_id that is associated with the user_id in the #__user_usergroup_map table. With this group_id value, it is then looking in the #__usergroups table and finding the associated id within this table.
    • So, we've queried the database to find a User's ID, and then we've found the User Group ID that's related to that user. 

The following steps aren't related to the Search Join Field, but assist you in completing your front-end User Manager

  1. Add a button to create a new User using the Button Free field

  2. Add a keyword search field so that you can search all user fields (within the #__users table) using 1 field using the Search Generic Field

  3. Add some user-defined search ordering functionality using the Search Ordering field.