One-to-many Joins

Editor's LeftJoin() method is excellent when joining data which has a 1:1 relationship, however, often your data structure will require a one-to-many relationship. An example of this that is often used in the computing world is access privileges: a single user can have access to n systems (where n >= 0). To provide one-to-many support the Editor .NET libraries include an MJoin class (i.e. many join).

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.

The 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, a model, etc). A new MJoin class is attached to an Editor instance using the MJoin method - for example, consider the following C# code:

DtResponse response = new Editor(WebApiApplication.Db, "users")
    .Model<StaffModel>()
    .MJoin(
        new MJoin("access")
            .Model<AccessModel>()
            .Order( ... )
            .Link( ... )
    )
    .Process(formData)
    .Data();

It is worth noting that the code above is not complete as the Link() and Order() methods are not fully defined - it is used to create the links between the tables. This is an important topic and is covered in the Direct link and Link table sections below.

Taking the code line-by-line:

• Line 1: Creates a new Editor instance for the table users.
• Line 2: Line 2 defines a model that describes the fields for the main Editor instance.
• 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 model AccessModel is used to define the fields for the linked table, or your could use the Field() method in the same way as you can for the main Editor class.
• Line 6: Define the order of the joined data (optional)
• line 7: The Link() method is used to define how the users and access tables should be joined
• 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 staff members and information about their access authorisation and make those access rights 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:

DtResponse response = new Editor(WebApiApplication.Db, "users")
    .Model<UserModel>()
    .MJoin(new MJoin("permission")
        .Link("users.id", "user_permission.user_id")
        .Link("permission.id", "user_permission.access_id")
        .Model<AccessModel>()
        .Order("permission.name")
        .Field(new Field("id")
            .Options("permission", "id", "name")
        )
    )
    .Process(formData)
    .Data();

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 passed 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 Field() method is used to define the id field that makes use of the Options() method. This will obtain a list of options which 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!

The two models used above are shown below. Note that the UserModel uses a nested class to define both the table (users) and the fields, while the PermissionModel does not. The nesting of the MJoined data is handled automatically by Editor.

public class StaffModel : EditorModel
{
    public class users : EditorModel
    {
        public string first_name { get; set; }

        public string role { get; set; }
    }
}

public class PermissionModel : EditorModel
{
    public string id { get; set; }

    public string name { get; set; }
}

This code forms the basis for the .NET one-to-many join example that is included in the Editor download package.

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() - Validation.MjoinMinCount() and Validation.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. The Editor instance that the function is being executed for.
   2. The action being performed - this will be one of:
   • Editor.Action.Create - Create a new record
   • Editor.Action.Edit - Edit existing data
   1. 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 array, with the same property name as the database table (although this can be configured using the Name() method). The above example will return JSON in the following format for each row:

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

Note that the access property is an array of objects, where each object has the values read from the database as properties.

In the DataTables initialisation to show the data from this object we might use:

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 join example for the full Javascript code in an example using this method.