• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Database join element

  • Views Views: 55,605
  • Last updated Last updated:

Navigation

      Access element (+)
      Birthday element
      Button element
      Calculation element
      Captcha element
      Checkbox element
      Colour Picker element
      Count element (+)
      Database join element
      Date element
      Digg element
      Display text element
      Dropdown element
      Facebook Like element
      Field element
      File Upload element
      Folder element
      Google Map element
      Image element
         Image databese join
      Internal id element
      IP element
      J!Date element
      Kaltura element
      Link element
      Notes element
      OpenStreetMap element
      Picklist element
      Radio Button element
      Rating element
      Sequence element
      Slider element
      Tags element
      Textarea element
      Thumbs element
      Time element
      Timer element
      Timestamp element
      Total element
      User element
      User group element
      Video element
      View level element
      YesNo element
      Youtube element
      Akismet validation
      Is Email validation
      Is Not validation
      Is Numeric validation
      Not empty validation
      PHP validation
      Rsa id
  • 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​

    dbjoin-options.png

    • 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​

    dbjoin-data.png

    • 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​

    Code:
    lastname, ' ', firstname

    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.png
    • Joins where statement (sql) - OPTIONAL - An SQL Select "Where" clause which filters the returned data. For example, to show only records with published = 1:
      SQL:
      where `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.png


    • 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.
    1. 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).
    2. In the Javascript box add the following. Note: modified to use jQuery instead of mootools. bg
     loadDBList(this);
    • 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]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]var usedID = jQuery(elementName);usedID[0].fireEvent('change');
    or​
    function loadStateList(el) {var usedID = jQuery('#' + el['strElement']); [/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.png

    • AJAX Default. Optional PHP code to return a default when update through AJAX. Should return a single value.
    • Additional join statements - "OPTIONAL - a standard MySQL JOIN clause, using any number of JOIN clauses, such as 'LEFT JOIN foo ON {thistable}.id = foo.parent_id RIGHT OUTER JOIN bar ON bar.foo_id = foo.id'. Fields form these joins will be available in your CONCAT Label or WHERE statements. IMPORTANT NOTE - does not work in List views, this feature is being deprecated, and will be removed from future versions of Fabrik. ADDITIONAL NOTE - this feature has been removed as of 2/24/2015.
    • 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​

    dbjoin-default.png

    • 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​


    dbjoin-front-end.png

    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:
      Databasejoin-add-form.png
    • 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:
      Databasejoin-form-result.png
    • 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​

    upload_2020-3-11_17-47-53.png

    • 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​


    dbjoin-advanced.png

    • 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] if( (int) $data['fb_conglomerates___hosp_count_raw'] > 0 && (int) $data['fb_conglomerates___part_of_hospital_system_raw'] <> 2 ){[/INDENT]
    [INDENT] return false;[/INDENT]
    [INDENT] } else {[/INDENT]
    [INDENT] $opt->text = 'Hospital';[/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
Back
Top