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.
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 Editor, 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 expression. This is raw SQL that will not be parsed by the libraries, but rather just passed to the SQL database. It may include multiple join conditions with logical expressions and / or sub-selects.
For example the above join could be written as:
.LeftJoin( "sites", "sites.id = users.site" )
More complex expressions can be used, e.g. do a standard join, but only show details about joined tables that match the sub-select:
.LeftJoin(
"sites",
"sites.id = users.site AND sites.id IN (SELECT id FROM sites WHERE name LIKE \"L%\")"
);
It is important to note that because Editor does not perform any parsing on the complex join expression, if you have any user input in the condition it must be fully validated before being used, otherwise you leave yourself open to an SQL injection attack.
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(WebApiApplication.Db, "users")
.Field(new Field("users.main_site"))
.Field(new Field("users.backup_site"))
.Field(new Field("mainSite.name"))
.Field(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 three 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
- Legacy: With a list of parameters that the
Fieldclass will read from the database. This method call is deprecated as of v1.6 and theOptionsclass is now preferred as it offers additional functionality as well as ease of use.
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 )- closure function that will apply conditions to the list of options (i.e. aWHEREstatement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - theQueryclass being used, providing access to its methods such asQuery.Where(),Query.AndWhere()andQuery.OrWhere()(refer to the .NET API documentation for full details on theQueryclass and its methods).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(q => q.Where("name", "L%", "LIKE") )
);
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(new []{"name", "country"})
.Render(row => row["name"]+" ("+row["country"]+")")
);
Closure - custom function
As a closure 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( () => new List<Dictionary<string, object>>{
new Dictionary<string, object>{ {"value", "EDI"}, {"label", "Edinburgh"} },
new Dictionary<string, object>{ {"value", "LON"}, {"label", "London"} },
new Dictionary<string, object>{ {"value", "NEW"}, {"label", "New York"} },
new Dictionary<string, object>{ {"value", "SAN"}, {"label", "San Francisco"} }
} );
Legacy: List of parameters
The Field.Options() method accepts up to five parameters:
string- The database table name that the options should be read fromstring- The column name that contains the value for the list of optionsstring|IEnumerable- The column(s) that contain the label for the list of options (i.e. this is what the user sees in theselectlist)Action<Query>(optional) - A closure function that will apply conditions to the list of options (i.e. aWHEREstatement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - theQueryclass being used, providing access to its methods such asQuery.Where(),Query.AndWhere()andQuery.OrWhere()(refer to the .NET API documentation for full details on theQueryclass and its methods).Func<Dictionary<string, object>, string>(optional) - 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.
Although 1.6 retains this capability for backwards compatibility, it is recommended that you use the Options class instead of this method now.
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 (note this requires Editor 1.5.4 or newer).
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(Validation.DbValues(new ValidationOpts { Empty = false }));
Note that when using this method you will likely wish to use ValidationOpts 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 C# we use the LeftJoin() method for the Editor instance:
DtResponse response = new Editor(WebApiApplication.Db, "users")
.Model<JoinModelUsers>("users")
.Model<JoinModelSites>("sites")
.Field(new Field("users.site")
.Options("sites", "id", "name")
.Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
)
.LeftJoin("sites", "sites.id", "=", "users.site")
.Process(formData)
.Data();
return Json(response);
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 Editor.Model() method is used with its optional string parameter to give the table name that the fields in the class relate to. It is thus called twice, once for each table / class to be referenced. The following shows the two classes used in this example:
namespace WebApiExamples.Models
{
public class JoinModelUsers
{
public string first_name { get; set; }
public string last_name { get; set; }
public string phone { get; set; }
public int site { get; set; }
}
public class JoinModelSites
{
public string name { get; set; }
}
}
The above model and controller 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).