One-to-many joins

The Editor class' leftJoin() method is excellent when using a 1:1 relationship between joined tables and is optimised for that data structure. However, it doesn't support a one-to-many option due to the formatting of the generated SQL statement. To provide one-to-many support the Editor PHP libraries include a Mjoin class.

Join types

There are several ways of joining SQL data, but in this case we want to consider only one-to-many data references:

• Direct reference foreign key, multiple items per parent
  • The parent row may have multiple child row references, mandating the use of an array to represent all of the data available to a parent row.
• Link table, multiple items per parent
  • Multiple child row references per parent again, but this time with a link table between them.

It can be easier to conceptualise this using an illustration like that shown below:

Note how in the direct reference case record 3 in the parent table is referenced twice in the child table. Similarly in the link table case, record 2 in the parent table has two child records it links to.

Mjoin class

The Mjoin class provides Editor with one-to-many support and provides many of the same options as the main Editor class (definition of fields, table name, etc). A new Mjoin class is attached to an Editor instance using the Editor->join method - for example, consider the following PHP code:

Editor::inst( $db, 'users' )
    ->field( ... )
    ->join(
        Mjoin::inst( 'access' )
            ->link( ... )
            ->order( ... )
            ->fields( ... )
    )
    ->process($_POST)
    ->json();

It is worth noting that the code above is obviously not complete as the field(), order() and link() methods are not fully defined, but it shows the structure of how to define a one-to-many join in Editor.

Taking the code line-by-line:

• Line 1: Creates a new Editor instance for the table users.
• Line 2: Defines the fields to be read from that table.
• Line 3: Attach a new Mjoin class instance
• Line 4: Create the Mjoin instance, which links to the table name given in the class constructor - in this case access.
• Line 5: The link() method is used to define how the users and access tables should be joined
• Line 6: Define the order of the joined data (optional)
• line 7: Give the field / columns names to read from the joined table.
• Lines 9-10: Process the request from the client and gather the data to send back.

This is the basic essence of how to use the Mjoin class, but as noted above, we also need to define the all important link (also called the reference) between the parent table and the child tables.

Direct link

The direct link method is where the child table contains a direct reference to the parent table. This is a relatively unusual structure for a one-to-many reference due to its lack of flexibility, where a link table is usually preferred, but it is possible to use Mjoin in this manner.

To do so use the link() method of the Mjoin class - it takes two arguments, both strings, which define the two fields in the database that make the link. You must give the table name and the field name for each field (for example "access.user_id"), but the order of fields doesn't matter, Mjoin will use the table name in the field to determine which field belongs to which table.

Please note that the only join operation that Mjoin can use is a direct comparison (i.e. =). This is suitable for foreign key references as used in SQL. Additionally, please be aware that when Editor does an update for one-to-many linked data, it will delete all of the old references before inserting the new ones required by the edited data. This is another reason a link table is the preferred approach for the SQL data structure.

Link table

A more typical one-to-many join is performed using a link table, as described above. In this case, editing the relationship between the linked items will not affect the linked items themselves, making it very attractive and flexible for joining data.

Consider the following tables which we will use to build a demonstration:

Table: users                 Table: user_permission
+----+------------+-------+  +----------+-----------+
| id | first_name | role  |  | user_id  | access_id |
+----+------------+-------+  +----------+-----------+
| 1  | Allan      | CEO   |  | 1        | 2         |
| 2  | Charlie    | CTO   |  | 2        | 1         |
| 3  | Fiona      | COO   |  | 2        | 3         |
| 4  | Richard    | CFO   |  | 3        | 1         |
+----+------------+-------+  | 4        | 1         |
                             +----------+-----------+

Table: permission
+----+----------+
| id | name     |
+----+----------+
| 1  | Printers |
| 2  | Web-site |
| 3  | VM       |
+----+----------+

We want to create a DataTable that will show users and information about their access authorisation (i.e. permissions) - making those permissions editable. For example "Allan" has access to only "Web-site", while "Charlie" has access to both "Printers" and "Virtual Machines".

We start with the Editor instance, and an Mjoin class that links to the permission table, defining the two links (users to user_permission and user_permission to permission using the link() method:

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.first_name' )
    )
    ->join(
        Mjoin::inst( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.access_id' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name' )
            )
    )
    ->process($_POST)
    ->json();

The link() method creates the links by taking the table and field names of the fields that define the join in SQL. Here we use link() twice as we have two different links to be created. The order of the two fields that define the link is not important to the link() method - it will automatically determine which field belongs to which table.

Ordering of the joined data is defined by the string passed to the order() method (1.5.6). You can optionally not include this method, in which case the data will be returned in database order, but you will typically wish to define an order to make it easier for your end users to read. The string to be given is just the same as that used for an SQL ORDER BY clause - i.e. the table and column name plus the sorting direction.

The fields() method is used to define the two fields that will be read from the joined table: the id field which makes use of the options() method and also the name. The options() method for the id will obtain a list of options that the Javascript for Editor will use to populate the list of options based on the data available in the permission table. This isn't required, but it does ensure data integrity!

This code forms the basis for the one-to-many join example where you can see the above code in action.

Validation

Similar to the top level Editor instance, Mjoin instances can also validate submitted data as a group, not just at the field level. This is accomplished using the Mjoin->validator() method which takes two parameters:

1. The client-side field name for the field where the error message should be shown (this is required as the field name can't be reliably determined if data from multiple fields are loaded).
2. The validation function. There are two built in validation methods for Mjoin->validator() - Validate::mjoinMinCount() and Validate::mjoinMaxCount() which can be used to ensure that a minimum and maximum number of items / values are selected by the end user. The validation functions used have the following signature:
   1. $editor - The Editor instance that the function is being executed for.
   2. $action - The action being performed - this will be one of:
   • Editor::ACTION_CREATE - Create a new record
   • Editor::ACTION_EDIT - Edit existing data
   1. $data - The data submitted by the client for this join instance.

Client-side

With the server-side script in place, now consider the client-side component of Editor. Specifically the fact that the JSON data structure reflects the joined data (just like with the left join example).

The data from the join tables is given as a JSON object or array (depending on the type given to the Join instance, with the same property name as the database table. For example, the above example will return JSON in the following format:

{
  "DT_RowId": "row_12",
  "users": {
    "first_name": "Allan",
    "role": "CEO"
  },
  "permission": [ {
      "id": "1",
      "name": "Printers"
    }, {
      "id": "3",
      "name": "VM"
    }
  ]
}

Note that the permission property is an array of objects, each with the fields as properties. The DataTables columns.data option can be used to read such nested JSON data.

columns: [
    { data: "users.first_name" },
    { data: "permission", render: "[, ].name" }
]

This example shows how DataTables can easily show data obtained from arrays - in this case the name from the access array of objects. This is done through the use of columns.render which will render the data. In this case the square brackets indicate an array that will be traversed over (the access array in this case), while the , in-between the two square brackets tells DataTables to concatenate the values found from the array with those two characters (i.e. we get a comma separated list) and finally the .name part picks up the name parameter from the objects in the array.

The Editor initialisation, through the fields.name option can also use the same dotted JSON syntax as DataTables to retrieve data. Please see the one-to-many example for the full initialisation code used.

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.