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

List Joins

  • Views Views: 28,641
  • 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