• Fabrik V4.4.1 is now available.

    This version corrects the Admin issue introduced by V4.4. V4.4.1 is available through the Joomla Updater or for download through your My Downloads area of our website.

    Turns out a code change intended for our 5.0dev branch inadvertantly got pushed to the 4.x branch (by me, duh!). The javascript structure in 5.0 will change considerably and part of that change took effect with the inadvertant code change.

    We have reverted the code change and released 4.4.1. V4.4 has been retracted.

    Sorry for any inconvenience.

  • A new version of Full Calendar is now available.

    See the details here

List Joins

  • Views Views: 29,860
  • Last updated Last updated:

Navigation

  • Joins​

    Joins allow you to create relationships between your list and other database tables.

    one-to-many​

    For example say you have two Lists 'countries' and 'regions'. We can say that
    • A 'region' belongs to one 'country'
    • A 'country' can have many 'regions'
    One-to-many video (old Fabrik version, but showing the principles)

    many-to-many​

    For example say you have two Lists 'students' and 'classes'. We can say that
    • A 'student' can attend many 'classes'
    • A 'class' can have many 'students'
    Many-to-many video

    Adding a Join​

    Within Fabrik the 'belongs to' part of the relationship is created by having a field in the 'regions' list which store's its county's id. For this purpose we would strongly recommend to create a hidden Field element set to store data as integers called 'country_id'. This field is called the foreign key element.
    Then edit the 'countries' list and go to its data->joins section.
    Press the 'Add' button to create a new join entry.
    • Join type : inner
    • From: countries (the source database table)
    • To: regions (the target database table - this is the child of the From table)
    • From column: id (the from table's primary key)
    • To column: country id (the to table's foreign key)
    • Repeatable: yes (as we have decided that a 'country' can have many 'regions')
    list-joins.png

    Note:"from" means here always the parent data. In form view the "from column" (usually primary key) data would be written into "to column". Joining vice versa (from FK of parent to PK of child) is possible but not recommended unless all data is read-only.
    In Fabrik one of the join columns must be a PK, you can't join some-column to some-other-column even if they are unique.

    • Delete joined data - If set to yes, and a record is deleted from the list then the joined record will also be deleted.

    Examples​


    Example - Display mode : Each row separately​

    list-join-example-1.png


    Example - Joined form​

    list-join-form-example.png


    Note: As we have the join set to 'Repeatable' the plus/minus icons are shown, allow users to create/remove records in the regions table, which are linked to the country currently being edited

    Also from a layout view we have the regions group 's repeat->template set to 'table', which rendered all Elements in a single repeating group in a single HTML table row.

    Example - Display mode : Merge rows​

    list-join-example.png


    In the previous list example, you could see that the countries rows were duplicated for each region they contained. If you set the join 'display mode' to 'merge rows' or 'merge rows and reduce data' then the above display is used, where we show one row per country, with a sub-list of the regions data.

    Please note that if you use list ordering, then you must order first on the list's main fields, before ordering on any field in the merged data.
Back
Top