The database join element is an extremely powerful element. It allows you to look up data in another database table and to pre-populate a radio list or drop down list with the data returned from the look up. For example, you could easily make a drop-down list containing all of your Joomla articles, or a list of countries.
Settings:
Options
- Eval - Should the default value be evaluated as a PHP expression. Element default examples.
- Default - the default value to select when showing a new form
- Hidden - is the element shown on the form as a hidden field.
Data
- Render join as- If not hidden do we render the element as a:
- radio list
- drop-down
- autocomplete field
Note: These 3 rendering modes link directly to the chosen table.
OR
- checkboxes
- multi-select list
Note: These 2 rendering modes are many-to-many, and Fabrik creates a new link table named tablename_repeat_elementname to hold the many-to-many references.
- Connection - The Fabrik connection that contains the database table whose data we want to look up to create the list
Note: Fabrik can not join across connections, so the selected connection must be the same as the list's connection!
- Table - The database table that contains the lookup data (populated once a connection has been selected)
- Key - The database table field to use as the value for each of the list Options
- Label - The database table field to use as the label for each of the list Options
- Or Concat label- Alternatively you can select multiple fields from the database table by supplying a mySQL concat string e.g.
Or Concat label examples
Using
{thistable} : {thistable} (litterally, don't replace with table name) is a placeholder which Fabrik will replace with a table alias. Necessary if you have multiple dbjoins to the same table etc. (otherwise you'll get aMySQL error like "not unique column"), it doesn't hurt to use it always.
Simple column & string concat
If your fields contain NULL values you would need to convert these to empty strings.
Code:
ISNULL(FirstName,''),'',ISNULL(LastName,'')
Subqueries
If you wish to set a label from a data value that is not an available placeholder, you can use MySQL Concat to retrieve the value by embedding a subquery within the CONTACT Label field:
Code:
(SELECT `column_containing_desired_value` FROM other_table WHERE `id` = {thistable}.field_containing_foreign_key)
Dates
When concatenating dates, please be aware that they will be returned as the GMT date, without your Joomla timezone applied to them, To set them to the correct timezone you can do the following (setting the timezone offset to +2 hours)
SQL:
DATE_FORMAT(CONVERT_TZ(gs_list_talks.talk_date,'+00:00','+2:00'), '%d-%m-%Y'),' - ',gs_list_talks.talk_name
Joining to dbjoin elements
If the label of your dbjoin is a dbjoin value itself get it's label with
SQL:
(SELECT name FROM table2 WHERE table2.id = {thistable}.element)
Data - where
- Joins where statement (sql) - OPTIONAL - An SQL Select "Where" clause which filters the returned data. For example, to show only records with published = 1:
Or to show only a set of users who belong to the group id 14:
SQL:
WHERE {thistable}.`username`
IN ( SELECT jos_users.username FROM jos_users, jos_user_usergroup_map
WHERE jos_users.id = jos_user_usergroup_map.user_id
AND jos_user_usergroup_map.group_id = 14)
- You can also add an ORDER BY to this
SQL:
WHERE {thistable}.published = '1' ORDER BY {thistable}.somefield ASC
- If you need to add an order by but don't need a WHERE clause, you can just use some clause which always returns true, like ...
SQL:
WHERE 1=1 ORDER BY {thistable}.somefield ASC
- Use the placeholder {thistable} to reference the table you are joining, rather than the actual table name. This is because if you have multiple joins to the same table, Fabrik uses aliases to differentiate the joins, like "SELECT yourtable AS yourtable_0", and there is no way of knowing which alias a given join will have. So Fabrik will replace {thistable} with the appropriate alias.
- Apply where to - Select which user view level will have the join where statement applied to them.
- Apply where when - when should the where filter be applied to the database join's element's query.
- AJAX Update - If you use element Placeholders in your WHERE statement, enable this to force the fiter to update when you change any of the referring Elements. Without having AJAX update on, the filter is based on the value of the referred to placeholder on load. If that placeholder is empty you may not get the results you want.
- Ajax update is applied based on the setting in "Apply where when"
- Example. With AJAX Update
If I have a table of zip codes by county and I want to present only the zip codes within a selected county {that county is in element "table_name___selected_county" then this code will present only those zipcodes from that county.
-
SQL:
where county = '{table_name___selected_county_raw}'
- Don't forget the raw....
- Note: In this simple example it might be easier to use a cascading dropdown.
Notes on using AJAX Update.
- One of the challenges of AJAX update is you may need to 'fire' the event that causes the dbjoin to be populated on page load. The 'controlling' element is not 'changed' on load so the AJAX update never fires to populate the dropdown. If the first user step is to populate/choose something in the controlling element then the change event will fire and this dbjoin element will be populated.
- However, perhaps you want the "controlling" element to be set to default on load. That means that, on load, the controlling element is set to a default value that the user need not select, so no change event is triggered and the dbjoin will not populate. The user would be required to change the 'default' value of the controlling element - which defeats the purpose of having a default.
- The AJAX default (see below) can be used, in some cases, to set the default for the dbjoin, but if you don't want a default value, but you do want the (filtered) list to be populated, this would require the user to deselect and re-select the value in the controlling element.
- What you can do is fire a change event for the controlling element on load of the dbjoin element.
- Example:
- If the dbjoin field you want to populate is "table___dbjoin" and the controlling field is "table___controlling" then the following code will cause fire a 'change' event for the controlling field on form load. This will cause the dbjoin field to populate as if the user had changed the controlling field.
- Add "onLoad" Javascript event to the "table___dbjoin" element (really it does not matter what element you use, just so the element is loaded so the onload java is fired).
- In the Javascript box add the following. Note: modified to use jQuery instead of mootools. bg
- This will call a function in the form_x.js file (where _x is the form number).
- That function should be:
function loadDBList(el) {var usedID = jQuery('#table___controlling' );usedID[0].fireEvent('change');
- This will cause a 'change' event of the controlling element just as if the user had changed it - so the dbjoin element will be populated. If you set the default in the controlling element, then the dbjoin will be filtered by the default value.
- If the controlling field and dbjoin field are in a repeated group - you will need to modify the code to account for the _x added onto the field name in the repeated group. The code is then.
function loadDBList(el) {var repeat = el.getRepeatNum();var usedID = jQuery('table___controlling' + repeat); [/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT]usedID[0].fireEvent('change');
- Slightly more elegant version. If you call the Javascript from the "controlling" element, you can avoid some of the hassle of building the element name. This is particularly nice if you are working in repeated Groups where you need to deal with repeatNum.
function loadStateList(el) { var elementName = '#' + el['strElement']; [/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT]var usedID = jQuery(elementName);usedID[0].fireEvent('change');
or
function loadStateList(el) {var usedID = jQuery('#' + el['strElement']); [/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT]usedID[0].fireEvent('change');
- In this case el contains the element name in ['strElement']. You can add the "#" on and fire the event.
Note - often cascading drop downs will be less hassle - but they may not work in all cases.
- AJAX Default. Optional PHP code to return a default when update through AJAX. Should return a single value.
- Filter Where - OPTIONAL - similar to "Joins where statement", but adds a where clause to the query used to build the list of Options when this element is used as a list filter. Currently only applied if you are using the "Show All" method for your filter Options, whereby all rows from the joined table are included in the filter list. Do not prefix this with WHERE, AND, OR, etc. So for instance, to restrict your dropdown filter to only those rows with a field called show_in_filter set to 1, you would use:
SQL:
{thistable}.show_in_filter = '1'
Tip:
If you are joining a list that has an element that stores a view_level, you can filter the available drop-down
Options for the logged in user based on the view_level stored in that element of your joined list.
The example query would look like this:
Code:
{thistable}.view_level IN (SELECT DISTINCT `#__viewlevels`.`id`
FROM #__user_usergroup_map
LEFT JOIN #__viewlevels ON REPLACE(REPLACE(rules,'[',','),']',',') LIKE CONCAT('%,',`group_id`,',%')
WHERE user_id ='{$my->id}')
In the above example, the {thistable}.view_level is the element of your
Lists element that stores the Joomla viewlevels id. The query references the Joomla viewlevels table and the Joomla user_usergroup_map table to determine which records in the joined table match the current logged in user's authorized view levels. This removes from the drop-down of the database join's filter, any records that the user is not authorized to see.
Please select
- Value of please select option - The text to record in the database if no option selected from the element. Defaults to .
- Label of please select option - The label to show for the 'Please select' option - if none entered this defaults to 'Please select'. To modify the label shown in the dropdown filter (default "All") append it with ::, so
Please select a city::Show all cities
- Show please select - If set to render as a dropdown you can toggle whether a please select option is included in the list. Does not effect radio / checkbox Lists
Add option in front end
If you have created a Fabrik table pointing to the database table you selected for this database join element then you can decide if you want your users to be able to add records into that form. The element will then appear with an "add" button like this:
- Clicking on "Add" will open an AJAX Fabrik form as such:
- Entering Holland and pressing "Save" will post the form via AJAX to the Fabrik form processing code. Once processed the pop-up window will close and your database join element will be updated to contain the new value:
- Popup form - Select the form that corresponds to the database table the join is connected to. This is the form that is loaded in the popup window when the "add" button is pressed.
- Add option in front end - If a 'Popup form' selected, then an add button is added to the element. When clicked on this will open a form to add in new records.
- Popup window width - the popup window width in pixels
- Link to joined record - Applies when in a detailed view, or if the element's access levels mean that it is set to read only when in a form. If this option is selected, then the read only value is encased in a link. This link points to a detailed view of the record referenced by the database join element's data.
- Front end select - Provides a button which pops up the joined list, allowing the user to search for the record they want.
NOTE - you must also select a form from the 'Popup form' selection above, even if you are not using the 'Add option in front end'.
NOTE: The element's 'Value' MUST be set to the primary key for this option to work.
Layout
- Enhanced Dropdowns - Enhances Dropdown behaviour using jQuery Chosen (similar to Select2), which e.g. provides a search box if there are more than 10 items and provides deletable widgets for multi-select.
- Note: For Enhanced Dropdowns to work you must have turned on Enhanced Dropdowns in the Fabrik Options Forms Tab. You can turn Enhanced Dropdowns off or on globally, or per element. (Note 8/26/2020: the wiki page for this tab is out of date. I will update when I can and revise)
- Options per row - When rendering as a radio or checkbox list, this defines how many radio buttons to show per row, defaults to 1.
- Multi select max - Work In Progress: experimental feature to allow specifying a maximum number of selections when using Enhanced Dropdowns, in multi-select mode
- Multilist size - If the join is rendered as a multi list, then this integer value specifies the list height
- Auto-complete width - Value of size attribute for auto-complete input / display field. Only effects visible size if not within a Bootstrap template. Otherwise use the "Bootstrap class" option
- Auto-complete rows - Set the maximum number of auto-complete Options to display in the dropdown menu
- Bootstrap class - Use this to define the width of the field, if your site uses a Bootstrap template
Advanced
- Eval options - PHP code to run to alter the element Options. Your code is called repeatedly, once for each option. Each option is an object and can be referenced in the eval code with the variable $opt. It has two properties you can change, $opt->value and $opt->text. You may also set $opt->disable to true, which will disable that option in a dropdown context (although it will still work as the currently selected value when editing). Return false to remove the option.
This is an extremely powerful feature. You may use $row to get values from other Elements in the row - and to remove an option, simply return false.
For example, in this code I am checking 2 other
Elements which determine whether a certain option should be available. And I want to change the label of that same option otherwise.
PHP:
if($opt->value == 1) {[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT] if( (int) $data['fb_conglomerates___hosp_count_raw'] > 0 && (int) $data['fb_conglomerates___part_of_hospital_system_raw'] <> 2 ){[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT] return false;[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT] } else {[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT] $opt->text = 'Hospital';[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT] }[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT]}
Here is an example of use: if you join your element to a date field, the values displayed are in MySQL format. That is, 'December 7, 2014' would display as '2014-12-07 00:00:00', which is not very user friendly. So if you want to show readable dates, use the following code:
-
PHP:
$date = new DateTime($opt->text);
$opt->text = $date->format('l j F Y');
Note: the following code would return only the date in English. If you have a multilingual site, use the JDate function instead (please notice the uppercase "F" in "Format"):
PHP:
$date = new JDate($opt->text);
$opt->text = $date->Format('l j F Y');
Note: this would work in Joomla 3. For Joomla 2.5 use 'toFormat' instead of 'Format'.
- Description field - Select a field from the joined table that contains an additional description. This will be shown next to the element and will be updated with the related content each time the user selects a different option.
- Auto-complete how - For auto-complete joins, controls whether choices are all entries containing the supplied string, or only those starting with the string, or those containing all the words individually.
- Trim empty concats - Used when you are using the 'data->Or Concat label' option with string constants, e.g. <pre>'ref:',{thistable}.field</pre>. If this option is off, and {thistable}.ref is an empty value, then Fabrik shows 'ref:' as the concatinated label. By turning this option on the 'ref:' will be stripped from the concat label
Validation (empty/is not)
To check if an option is selectet, add a "is not"
Validation.
If dbjoin is not multiselect
- Add a default value to the "Please select" option
- In "is not" Validation put this value in "is not" field
If dbjoin is multiselect
In "is not"
Validation leave "is not" field empty
Filter List issue with Ajax Where Clause
If dropdown is not working in the List filter for database join element, then add following in the "
Filter Where" option of the Element