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 provides 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:

1. The table to join onto (optionally with an alias)
2. The first join column name
3. The join operator (=, >=, etc.)
4. 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 Field::inst( '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:

1. The table to join onto (optionally with an alias)
2. 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 it 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:

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.main_site' ),
        Field::inst( 'users.backup_site' )
        Field::inst( 'mainSite.name' ),
        Field::inst( '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 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 a Field->options() method. It is very flexible and can be called in any one of three ways:

• With an Options class 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 Field class will read from the database. This method call is deprecated as of v1.6 and the Options class 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 is constructed using Options::inst() (or new Options() if you are using PHP 5.4+) and provides the following chainable methods:

• Options->table( string ) - The database table name that the options should be read from
• Options->value( string ) - The column name that contains the value for the list of options
• Options->label( string|string[] ) - The column(s) that contain the label for the list of options (i.e. this is what the user sees in the select list)
• Options->where( function ) - closure function that will apply conditions to the list of options (i.e. a WHERE statement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - the Query class being used, providing access to its methods such as Query->where(), Query->and_where() and Query->or_where() (refer to the PHP API documentation for full details on the Query class 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( integer ) - 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:

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->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':

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( 'name' )
        ->where( function ($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)':

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( array('name', 'country') )
        ->render( function ( $row ) {
            return $row['name'].' ('.$row['country'].')';
        } )
    );

Closure - custom function

As a closure function, the Fields->options() 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:

Field::inst( 'users.site' )
    ->options( function () {
        return array(
            array( 'value' => 'EDI', 'label' => 'Edinburgh' ),
            array( 'value' => 'LON', 'label' => 'London' ),
            array( 'value' => 'NEW', 'label' => 'New York' ),
            array( 'value' => 'SAN', 'label' => 'San Francisco' )
        );
    } );

Legacy: List of parameters

Prior to Editor 1.6 the Options class didn't exist and the information required to read options from a database was provided through parameters passed to Fields->options(). This method accepts up to five parameters:

1. string - The database table name that the options should be read from
2. string - The column name that contains the value for the list of options
3. string|array - The column(s) that contain the label for the list of options (i.e. this is what the user sees in the select list)
4. function (optional) - A closure function that will apply conditions to the list of options (i.e. a WHERE statement), allowing only a sub-set of the options to be selected. A single parameter is passed into the function - the Query class being used, providing access to its methods such as Query->where(), Query->and_where() and Query->or_where() (refer to the PHP API documentation for full details on the Query class and its methods).
5. function (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 Validate::dbValues validation method (note this requires Editor 1.5.4 or newer).

By default Validate::dbValues will attempt to use the table and value column defined by the Field->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:

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( 'name' )
    )
    ->validator( 'Validate::dbValues' );

Example

The Editor examples contain an 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 PHP we use the leftJoin() method for the Editor instance:

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.phone' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'sites.name' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->process($_POST)
    ->json();

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 PHP 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: "../php/join.php",
        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: "../php/join.php",
            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.

PHP API documentation

The PHP API developer documentation for the Editor PHP classes is available for detailed and technical discussion about the methods and classes discussed above.