List Joins

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')

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​

Example - Joined form​

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​

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.