Using Editor's libraries for server-side processing

With SearchPanes 1.1 now released and supporting server-side processing we thought it would be worth while to write a blog post detailing how to use server-side processing with the Editor Libraries in place of the demo server-side processing script. Not only are these libraries open source, they support a wide array of features not covered by the demo script.

This blog post will describe how to use the Editor libraries on the server-side without Editor on the client-side and also demonstrate how they operate with SearchPanes. It bears repeating - you do not need an Editor license to use the server-side libraries for Editor!

Server-Side processing with Editor's libraries

Introduction

There are multiple advantages to using the Editor libraries for server-side processing.

• They are open source and can be used without the client-side part of Editor (which has a commercial license)
• They support joins
• They support SearchPanes as of SearchPanes 1.1 as of Editor 1.9.3
• They support PHP, NodeJS and .NET, each with a number of different databases
• They can be used as read only
• It also has a well defined and documented API

While the demo server-side processing PHP script used in the DataTables examples is useful and serves a purpose, the Editor libraries are far more extensive and focussing on these allows us to provide high quality code that we can also maintain to a high standard. Therefore, we have no plans to expand the demo server-side processing script to include support for SearchPanes.

Installing

When setting up server-side processing on the client-side there are a few things that you must first do. Firstly on the server-side you have to download the Editor server-side libraries (.NET | NodeJS | PHP). These are open source and free to use.

Each platform has its own configuration options for connecting to the database - e.g. for PHP you configure the database connection options in config.php. Please refer to the links above for full details.

Writing a Controller

PHP

Writing a controller is very straightforward. You first include the DataTables PHP library.

include("../lib/DataTables.php");

Then alias the Editor classes so that they are easy to use later. First, we will consider a very basic initialisation to grasp the concept, so we only need to use 2 classes.

use
    DataTables\Editor,
    DataTables\Editor\Field;

Next up is to initialise an instance of the Editor class, and the fields we want to use. The fields() method can take as many field instances as you wish to define and can also be called multiple times. Each Field instance takes an argument representing the name of the column to read the data from.

Editor::inst( $db, 'datatables_demo' )
    ->field(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'office' ),
        Field::inst( 'extn' ),
        Field::inst( 'start_date' ),
        Field::inst( 'salary' )
    )

It's then important to disable any write functionality within the Editor class, seeing as we are only doing read only operations without the Editor client-side license. This is done by adding the following method call.

    ->write( false )

When DataTables and Editor make a request to the server to get or update data, they send the data using HTTP parameters. DataTables defaults to GET while POST is the default for Editor - in these examples we'll use POST data. PHP provides this information in its global $_POST (or $_GET if you are using GET) variable which can be given to the Editor instance for processing.

The data is then processed using the process() method of the Editor class, with the data sent from the client being passed into it. So we must now add the following method call.

    ->process( $_POST )

The final step of this process is to send the data back to the client. To keep things simple we will use the json() method of the Editor class.

    ->json();

So now your initialisation should look something like this.

Editor::inst( $db, 'datatables_demo' )
    ->field(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'office' ),
        Field::inst( 'extn' ),
        Field::inst( 'start_date' ),
        Field::inst( 'salary' )
    )
    ->write( false )
    ->process( $_POST )
    ->json();

Combining this with the library inclusions and aliases will give you a complete controller.

NodeJS

The equivalent for NodeJS is as follows.

let editor = new Editor(db, 'datatables_demo')
    .fields(
        new Field('first_name'),
        new Field('last_name'),
        new Field('position'),
        new Field('office'),
        new Field('extn'),
        new Field('start_date'),
        new Field('salary'),
    )
    .write(false);

await editor.process(req.body);
res.json(editor.data());

.NET

The equivalent for .NET is as follows.

var response = new Editor(db, "users")
    .Model<StaffModel>()
    .Field(new Field("first_name"))
    .Field(new Field("last_name"))
    .Field(new Field("position"))
    .Field(new Field("office"))
    .Field(new Field("extn"))
    .Field(new Field("start_date"))
    .Field(new Field("salary"))
    .Process(Request)
    .Data()

return Json(response)

Client-side configuration

Writing a DataTable configuration to use the data returned by the Editor server-side libraries is equally straightforward. Start with your basic DataTables initialisation and add an ajax property which points to the controller that you have just written using the ajax.url property. The ajax.type property must also be set to POST, this is required as the server is looking for POST data.

$('#example').DataTable({
    ajax: {
        url: "../php/exampleController.php",
        type: "POST"
    }
});

Next up is to add the columns configuration. The columns config option is an array of objects - one for each column. In each of these objects we have to define only one property. The columns.data property must be set to the name of the column in the database. You should end up with something along the lines of the following.

$('#example').DataTable({
    ajax: {
        url: "../php/clientSide.php",
        type: "POST"
    },
    columns: [
        { data: "first_name" },
        { data: "last_name" },
        { data: "position" },
        { data: "office" },
        { data: "extn" },
        { data: "start_date" },
        { data: "salary" }
    ]
});

You may also wish to use data renderers to format the data for presentation (e.g. numbers).

Example

Putting this all together gives us the following DataTable.

Name	Position	Office	Salary

Joins

Of course a big benefit of the Editor libraries is that you unlock a large amount of extra functionality that is not available in the demo SSP class. Here I will cover joins, but there are many more options which are detailed in the manual.

So let's take the following client-side configuration and use the Editor libraries to perform a join.

$('#example').DataTable({
    ajax: {
        url: "../php/leftJoinExample.php",
        type: "POST"
    },
    columns: [
        { data: "users.first_name" },
        { data: "users.last_name" },
        { data: "users.phone" },
        { data: "sites.name" }
    ]
});

So here we are taking data from both the users table and the sites table.

PHP

Now create your controller in the same way as before, but when declaring the instances of Field we are also going to have to specify the table where the data is.

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.phone' ),
        Field::inst( 'sites.name' )
    )

We are then going to add a single method call to leftJoin() to perform the join. This should go after the field() method call and before the process() method call. The leftJoin() method takes 4 arguments.

• $table Table name to do a join onto
• $field1 Field from the parent table to use as the join link
• $operator Join condition (=, <, etc)
• $field2 Field from the child table to use as the join link

Knowing this we should end up with a method call that looks something like the following.

    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )

Placing this between the field() and process() methods should give the following controller initialisation.

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.phone' ),
        Field::inst( 'sites.name' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->process($_POST)
    ->json();

NodeJS

The equivalent for NodeJS is

let editor = new Editor(db, 'users')
    .fields(
        new Field('users.first_name'),
        new Field('users.last_name'),
        new Field('users.phone'),
        new Field('sites.name')
    )
    .write(false)
    .leftJoin('sites', 'sites.id', '=', 'users.site');

await editor.process(req.body);
res.json(editor.data());

.NET

The equivalent for .NET is

var response = new Editor(db, "users")
    .Model<StaffModel>()
    .Field(new Field("users.first_name"))
    .Field(new Field("users.last_name"))
    .Field(new Field("users.phone"))
    .Field(new Field("sites.name"))
    .Write(False)
    .LeftJoin("sites", "sites.id", "=", "users.site")
    .Process(Request)
    .Data()

return Json(response)

SearchPanes server-side processing

Seeing as the initial reason for writing this blog post was SearchPanes supporting server-side processing, let's dive into that now!

Client-side

Take a look at this client-side configuration:

$('#example').DataTable( {
    ajax: {
        url: "../php/searchPanes.php",
        type: "POST"
    },
    columns: [
        { data: "users.first_name" },
        { data: "users.last_name" },
        { data: "users.phone" },
        { data: "sites.name" }
    ],
    columnDefs:[{
        searchPanes:{
            show: true,
        },
        targets: '_all',
    }],
    layout: {
        top1: 'searchPanes'
    },
    serverSide: true
} );

There are a couple of different things going on here so let's talk through them.

• layout option is set to determine where SearchPanes should be placed within the page, in this case directly above everything else. For legacy DataTables 1.x use the dom option with the Q letter for SearchPanes positioning.
• ajax option matches our previous use of it, pointing to the controller with HTTP POST used.
• columns option is as before, defining the data to be used for each column in the DataTable.
• columnDefs option is being used to set columns.searchPanes.show to true for all of the panes, meaning that they will all be displayed.

Server-side

PHP

In the controller file we want to use the SearchPaneOptions class in addition to the Editor and Field classes from the libraries, so we add it to the use statement:

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\SearchPaneOptions;

For the controller there are a couple of additions that have to be made. Consider the following controller.

Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.first_name' )
            ->searchPaneOptions( SearchPaneOptions::inst() ),
        Field::inst( 'users.last_name' )
            ->searchPaneOptions( SearchPaneOptions::inst() ),
        Field::inst( 'users.phone' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->table( 'users')
                ->value( 'phone' )
            ),
        Field::inst( 'sites.name' )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->value( 'users.site')
                ->label( 'sites.name' )
                ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
            )
    )
    ->write(false)
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->process($_POST)
    ->json();

Here on each field instance we are initialising a SearchPaneOptions class to instruct the library to determine what options should be shown for each column's search pane.

There are then multiple different way that it can be called to determine what options should be shown in the pane on the client-side. The simplest is just to create a new SearchPaneOptions instance with no options and the software will automatically determine what data to read from the database using the field's name and table. The full set of options are documented for each library:

• PHP Documentation
• .NET Documentation
• Node JS Documentation

If you are using your own server-side processing scripts, rather than the Editor libraries. The SearchPanes documentation details the parameters sent to the server and what it expects back.

NodeJS

If you work in the NodeJS world, the equivalent code is:

let editor = new Editor(db, 'users')
    .fields(
        new Field('users.first_name')
            .searchPaneOptions(new SearchPaneOptions()),
        new Field('users.last_name')
            .validator(Validate.notEmpty())
            .searchPaneOptions(new SearchPaneOptions()),
        new Field('users.phone')
            .searchPaneOptions(
                new SearchPaneOptions()
                    .table('users')
                    .value('phone')
            ),
        new Field('sites.name')
            .searchPaneOptions(
                new SearchPaneOptions()
                    .value('users.site')
                    .label('sites.name')
                    .leftJoin('sites', 'sites.id', '=', 'users.site')
            )
    )
    .leftJoin('sites', 'sites.id', '=', 'users.site');

await editor.process(req.body);
res.json(editor.data());

.NET

And for .NET:

var response = new Editor(db, "users")
    .Model<UploadManyModel>()
    .Field(new Field("users.first_name")
        .SearchPaneOptions( new SearchPaneOptions() )
    )
    .Field(new Field("users.last_name")
        .SearchPaneOptions( new SearchPaneOptions() )
    )
    .Field(new Field("users.phone")
        .SearchPaneOptions(new SearchPaneOptions()
            .Table("users")
            .Value("phone")
        )
    )
    .Field(new Field("sites.name")
        .SearchPaneOptions(new SearchPaneOptions()
            .Label("sites.name")
            .Value("users.site")
            .LeftJoin("sites", "sites.id", "=", "users.site")
        )
    )
    .LeftJoin("sites", "sites.id", "=", "users.site")
    .Process(Request)
    .Data();

return Json(response);

Live example

Putting all of the above together results in the following table:

First name	Last name	Phone	Site

Feedback

As always, we are keen to hear how you are using DataTables, Editor and SearchPanes. Please drop us a message in the forum with how you are getting on with our software, or if you have run into any problems, or have ideas for future enhancements.

Enjoy!