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.
The list of options might be shown to the end user using a select, radio or checkbox input type.
To make the population of the list of options available as easy as possible, the Field class provides an Field.options() method. It is very flexible and can be called in any one of two different ways:
- With an
Optionsclass instance which defines the table and columns to read from the database - With a closure function you define that will be executed and return a list of options
Options class
The Options class provides a simple API to define the information that Editor requires to read the options from the database, as well as customisation options. It provides the following chainable methods:
Options.table( string )- The database table name that the options should be read fromOptions.value( string )- The column name that contains the value for the list of optionsOptions.label( string|string[] )- The column(s) that contain the label for the list of options (i.e. this is what the user sees in theselectlist)Options.Where( function )- Function that will apply conditions to the list of options (i.e. aWHEREstatement), allowing only a sub-set of the options to be selected. Please refer to the Knex.JS documentation for full details of the conditional methods that can be used in this function.Options.render( function )- A formatting function that will be applied to each label read from the database - this is particularly useful if you pass in an array for the third parameter and wish to customise how those fields are shown. They will simply be concatenated with a single space character if no function is provided.Options.order( string )- Specify an order by clause that will determine the order of the options. If this method is not used the ordering will be automatically performed on the rendered data.Options.limit( int )- Limit the number of results that are returned. If this method is not used, no limit will be imposed.
Consider the following three use cases:
Simplest possible use - get a list of options from the sites table, where id is the value and name the label:
new Field('users.site')
.options(new Options()
.table('sites')
.value('id')
.label('name')
);
Apply a condition to the list of options - in this case getting only names which start with the letter 'L':
new Field('users.site')
.options(new Options()
.table('sites')
.value('id')
.label('name')
.where(function() {
this.where('name', 'LIKE', 'L%')
})
);
Get multiple fields for the label and format them using a function. In this case the resulting format is: 'name (country)' - e.g.: 'Edinburgh (UK)':
new Field('users.site')
.options(new Options()
.table('sites')
.value('id')
.label(['name', 'country'])
.render(row => {
return row.name + ' (' + row.country + ')';
})
);
Custom function
As a custom function, the Fields.options() method provides the ability to get the data for the field options from virtually anywhere (files, arrays, web-services, database, etc) - you define the code that will return an array of options. By default the returned array should contain value/label options for each entry (although this can be customised using the options of the fields such as select).
The following example shows a static array of values being returned:
new Field('users.site')
.options( () => {
return [
{ value: 'EDI', label: 'Edinburgh' },
{ value: 'LON', label: 'London' },
{ value: 'NEW', label: 'New York' },
{ value: 'SAN', label: 'San Francisco' },
];
} );
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).