11. Advanced examples
Custom Tables for Joomla allows the creation of complex data structures and relations. Here's how to make a LEFT JOIN using the Table Join field type:
Determine the primary and secondary tables you want to join. The primary table is where you'll add the Table Join field, and the secondary table contains the related information.
In your primary table, add a new field and select the "Table Join" type to create a relationship between the tables.
Configure the field with the following options:
- Table: Choose the secondary table.
- Field: Choose the corresponding field in the secondary table.
The extension will generate the necessary SQL queries to perform the LEFT JOIN operation based on your configurations.
The joined data can be accessed in your Joomla site's frontend. Custom Tables utilizes Twig templating for data rendering. You can use Twig tags to display the values from the joined tables. For example, if you have a field from the secondary table you wish to display, you can use the following Twig tag:
{{ record.getvalue("FieldNameFromSecondaryTable") }}Replace FieldNameFromSecondaryTable with the actual field name you want to retrieve the value from.
Example: {{ tables.getrecords("CountriesPage","population>1000000","name",20) }}
Where CountriesPage is the layout name.
"population>1000000" where the condition or the filter
"name" - the field to sort the records by. Descending order is also possible "name desc"
20 - number of records (limit)
If you want a functional catalog embedded in a Layout or an article, use the HTML IFrame tag. You can provide some URL query parameter to filter catalog records.
Below is an example of a Catalog in side Edit Record Layout:
Where "/index.php/subjects-list" is the link to a menu item that has Custom Tables - Records menu type
"type=3" is a query parameter that we want to pass as a filter parameter.
Where "3" is the ID of the current record, if the page where you want to have a functional catalog is Add/Edit Record or Record Details type.
"tmpl=component" is to not avoid rendering the front-end template.
This is Custom Tables - Records type menu item.
I passed parameter type with the URL query. Now we will use it to filter catalog records.
I have a field "type" in my table. I want to show all the records that have the type id equal to type URL parameter.
{{ url.getint("query parameter" }} is the tag to get URL parameter value.
Add menu type: "Custom Tables - Catalog"
Click on the Filter tab
and the parameter "Search Parameter", set it to "authoruser={{ user.id }}", where "user" is the name field in your custom table where user ID is stored.
{{ user.id }} will be replaced by the current user id automatically.
This is equal to MySQL query example:
SELECT * FROM #__customtables_table_myfirsttable WHERE es_user=200042
"myfirsttable" is the name of the table, for example.
Layout tag example:
{{ document.layout("LayoutName"}}
The Included layout code and parent layout code will be processed as a single layout.
Works as the "include" method in other computer languages.
This helps to structure layouts.
The {{ document.layout("LayoutName"}} tag takes all the text/code/markup that exists in the specified layout and copies it into the parent layout that uses the "layout" tag.
Including layouts is very useful when, for example, you want to insert the same Javascript, HTML, or text to multiple pages of a website.
It works recursively.