Database join element

Aug 9, 2017
Database join element
  • 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 (top)




    Options (top)


    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(top)

    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: There 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(top)
    Code (Text):
    lastname, ' ', firstname
    If your fields contain NULL values you would need to convert these to empty strings.
    Code (Text):
    ISNULL(FirstName,''),'',ISNULL(LastName,'')
    When concatinating 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)
    Code (Text):
    DATE_FORMAT(CONVERT_TZ(gs_list_talks.talk_date,'+00:00','+2:00'), '%d-%m-%Y'),' - ',gs_list_talks.talk_name
    If the label of your dbjoin is a dbjoin value itself get it's label with
    Code (Text):
    (SELECT name FROM table2 WHERE table2.id = {thistable}.element)

    Data - where(top)


    dbjoin-data-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:
      Code (SQL):
      WHERE `published` = 1
      Or to show only a set of users who belong to the group id 14:
      Code (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
      Code (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 ...
      Code (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.
    • 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:
      Code (SQL):
      {thistable}.show_in_filter = '1'

    Please select(top)

    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 (top)


    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:
      [​IMG]
    • 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:[​IMG]
    • 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(top)

    dbjoin-layout.png
    • Options per row - When rendering as a radio or checkbox list, this defines how many radio buttons to show per row, defaults to 1.
    • 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
    • Bootstrap class - Use this to define the width of the field, if your site uses a Bootstrap template

    Advanced (top)


    dbjoin-advanced.png
    • Eval options- PHP code to modify the element's options. Each option is an object contained called $opt with $opt->value and $opt->text properties. The $opt object is passed by reference, so to alter it, rather than returning a new value, you update the object itself.

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

    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) (top)

    To check if an option is selectet, add a "is not" validation.

    If dbjoin is not multiselect (top)

    • Add a default value to the "Please select" option
    • In "is not" validation put this value in "is not" field

    If dbjoin is multiselect (top)

    In "is not" validation leave "is not" field empty
  • Loading...
MeAppBuilder and gans like this.