Joins
A cornerstone of CRUD applications is the ability to combine information from multiple SQL tables, representing the combined data as a common data set that the end user can easily understand and manipulate. Relational databases are, after all, designed for exactly this sort of data referencing. Often in CRUD applications, working with such joined data can significantly increase the complexity of the application and increase development time, Editor makes working with such join tables extremely easy through its leftJoin() method. Complex joins with full CRUD support can be added in just minutes.
Left Join
Editor presents a leftJoin() method as an SQL Left (outer) Join is the most common type of join performed when working in CRUD applications. The end result is that the focus is on a single table that is being edited, with additional (potentially optional) data added to it. If you are already comfortable with an SQL left join, skip over this section, but if not, it is important to understand the data manipulation being performed.
An SQL left join returns all rows from the left table (in the case of Editor, the table that the Editor class is initialised with), even if there are no matches in the right table (the table being joined on). If there is no data in the right table, null values will be used for the columns being read from that table.
Consider for example the following two tables:
Table: staff Table: sites
+----+---------+-------+------+ +----+-----------+
| id | name | title | site | | id | name |
+----+---------+-------+------+ +----+-----------+
| 1 | Allan | CEO | 1 | | 1 | London |
| 2 | Charlie | CTO | 1 | | 2 | Edinburgh |
| 3 | Fred | CFO | null | +----+-----------+
+----+---------+-------+------+
If we perform the following join query:
SELECT staff.name, sites.name
FROM staff
LEFT JOIN sites ON staff.site = sites.id`
The result set will be:
+------------+------------+
| staff.name | sites.name |
+------------+------------+
| Allan | London |
| Charlie | London |
| Fred | null |
+------------+------------+
For a more detailed explanation of left joins, and the other join options that SQL has, please review Jeff Atwood's excellent A Visual Explanation of SQL Joins.
Editor.leftJoin method
The Editor leftJoin() method is as similar as possible to the standard SQL JOIN ON syntax. Specifically it typically takes four parameters:
- The table to join onto (optionally with an alias)
- The first join column name
- The join operator (
=,>=, etc.) - The second join column name.
Consider for example a table users which has a column site which points to an id column in a sites table and we want to include information from the site table. In SQL the Join syntax would be:
LEFT JOIN sites ON sites.id = users.site
In the Node.JS Editor class, the leftJoin() method is:
leftJoin( 'sites', 'sites.id', '=', 'users.site' )
With the join in place, to read information form the joined table is as trivial as adding the field to the Editor instance' field list. For example, to read the name column from the site table use new Field( 'sites.name' ).
Complex left joins
Editor 2.0 added support for complex join expressions to the leftJoin(). In this case it uses just two parameters:
- The table to join onto (optionally with an alias)
- The join function - this is a Knex.js Join callback which allows complex join expressions, including sub-selects to be used.
For example the above join could be written as:
.leftJoin( 'sites', function () {
this.on('sites.id', '=', 'users.site')
})
Please refer to the Knex.js documentation for the full range of options available for the join statements.
Table aliases
It can sometimes be useful to perform multiple left joins to the same table so you can read different, but like information from the joined table. For example, in our staff tables above we could have a Main site and a Backup site (called main_site and backup_site in the users table, respectively), rather than just a single one. The site information would still need to come from the sites table, but the value would be different for each of the two fields.
In SQL this can be done with an alias - effectively renaming the joined table (i.e. aliasing it to a different name to ensure that is can be uniquely identified). We can do this in Editor as well using the as key word in the first parameter given to the leftJoin method.
For example:
new Editor(db, 'users')
.fields([
new Field('users.main_site'),
new Field('users.backup_site'),
new Field('mainSite.name'),
new Field('backupSite.name')
])
.leftJoin( 'sites as mainSite', 'mainSite.id', '=', 'users.main_site' )
.leftJoin( 'sites as backupSite', 'backupSite.id', '=', 'users.backup_site' );
Note that the alias name (mainSite and backupSite is used in the join condition and the field name. The client-side code (columns.data and fields.name) would also refer to the alias name.
Options
Inevitably when you are working with an editable joined table, you will wish to present the end user with a list of options that they can select as the value for the field. This list of options will be defined by the data in the joined table - continuing the above example, this is the list of sites that the staff member might be assigned to.
Editor's Node.js libraries provide an Options class which is used to define the list of options for use in a field such as select, datatable, tags and others. The most simple use of the options class is to specify the table and two columns (label and value) from where the options should be read:
new Field('users.site')
.options(new Options()
.table('sites')
.value('id')
.label('name')
);
Please see the Options documentation for full details on how to use the Options class to get and display the options to show the end user for a field.
Validation
Editor's libraries provide a number of useful validation methods that can easily be used to ensure that the data submitted from the client-side is valid. When a join is being used that validation should ensure that referential integrity is retained by checking that the value to write to the database exists in the joined table before using it. This can be done using the Validation.dbValues validation method.
By default Validation.dbValues`` will attempt to use the table and value column defined by theField.options()` method (described above). If this is not possible (either the options haven't been defined or a closure was used) the options can be passed in using the [validator's configuration options](validation#Database](validation#Database). As a result, in most cases, validating the joined data is as simple as using:
new Field('users.site')
.options('sites', 'id', 'name')
.validator(Validate.dbValues(
new Validate.Options({
empty: false
})
));
Note that when using this method you will likely wish to use Validate.Options to tell the validator that an empty input is not valid (it is by default). There will be times when this is not required (for example you wish a empty value to insert a null into a database, thereby signifying that there is no join relationship between rows), but typically this will be disabled for this option (in case the user submits an empty value, which can easily be done if you are using the placeholder option of select).
Example
The Editor examples contain and example of a join table and here we will consider the code from that example in detail. The example uses the two SQL tables defined above to present a list of staff with a location that we wish to be editable.
Server-side
In the Javascript we use the leftJoin() method for the Editor instance:
router.all('/api/join', async function(req, res) {
let editor = new Editor(db, 'users')
.fields(
new Field('users.first_name'),
new Field('users.last_name'),
new Field('users.phone'),
new Field('users.site').options(
new Options().table('sites').value('id').label('name')
),
new Field('sites.name')
)
.leftJoin('sites', 'sites.id', '=', 'users.site');
await editor.process(req.body);
res.json(editor.data());
});
You'll also likely notice that the dot separator also very conveniently is used in Javascript as the object parameter accessor and the same basic format can be used to access the data on the client-side.
The above initialisation will generate JSON data in the format for each row:
{
"users": {
"first_name": "Quynn",
"last_name": "Contreras",
"phone": "1-971-977-4681",
"site": "1"
},
"sites": {
"name": "Edinburgh"
}
}
Client-side
On the client-side we would use the following script - notice in particular the use of the Javascript dotted object notation in the fields.name and columns.data options:
$(document).ready(function() {
var editor = new DataTable.Editor( {
ajax: "/api/join",
table: "#example",
fields: [ {
label: "First name:",
name: "users.first_name"
}, {
label: "Last name:",
name: "users.last_name"
}, {
label: "Phone #:",
name: "users.phone"
}, {
label: "Site:",
name: "users.site",
type: "select"
}
]
} );
$('#example').DataTable( {
dom: "Bfrtip",
ajax: {
url: "/api/join",
type: 'POST'
},
columns: [
{ data: "users.first_name" },
{ data: "users.last_name" },
{ data: "users.phone" },
{ data: "sites.name" }
],
select: true,
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
]
} );
} );
This particular example can be seen running here (note that the example uses PHP on the server-side, due to this server's hosting environment).