Server-side setup rather than Client-side setup

kevou84

Hi all, hi allan,

I am looking for a way to set up columns/fields of Datatables/Editor on Server-side rather than Client-side to build a generic display controller of any database table.

The trick with Datatables was that you could apply the script on existing table (for my purpose the columns) in html DOM. You had to rely on its type guesser though... And there were few other drawbacks... But I managed to build a generic display of any table based on that trick :-)

But now combined with Editor which I bought recently (awesome), I have no trick anymore lol... Is there a way to "inject" columns definition through PHP controller instead of relying on Javascript configuration ? I had hope maybe you authorize this through an AJAX call ? For both Datatables and Editor ?

Thanks guys for your reply.

Kev. A Datatables old friend now ^^

allan

Hi Kev,

The way to do this is to make an Ajax call to get the configuration object and then construct the Editor / DataTables instance's in the callback for the Ajax call:

$.ajax( {
  url: 'getConfig',
  dataType: 'json',
  success: function ( json ) {
    var editor = new $.fn.dataTable.Editor( json.editor ); // or perhaps json.fields for just the fields array.
    // similar for DataTables config.
  }
} );

The one big disadvantage of this method is that you can't have functions in the JSON data - since JSON doesn't allow that.

Allan

kevou84

Many thanks for this hint Allan, I get the idea and will follow that lead... There are disadvantages for all solutions ain't it ;-)

kevou84

Dear Allan,

So here I am again ^^ In a server-side processing context... I rewrote your SSP class into a Doctrine QueryBuilder one... Yeah yeah yeah :D

It's working fine until I use the search field... I understood you did some kind of trick with the bindings and all in order to filter any type of data as strings... Which I'll try to do in doctrine later... So maybe TTYL lol

I have one little feedback though now that i went into your code... I noticed that when adding a DT_RowId record in the data sent to client-side, it is sent back by $request->request->get('columns') when filtering, like this...

0 = {array} [5]
data = ""
name = ""
searchable = "true"
orderable = "false"

I don't think this is a good behaviour for two reasons :
- The DT_RowId record is not a column and shouldn't be treated as is, if someone wants to filter on ids then one would display it as a column
- The searchable option cannot be set to false because it is part of data, not of columns defintiion

So right now i'll filter on $column['dt'] != 'DT_RowId' in SSP Doctrine class but I wanted to share that point in case you think it is a relevant remark for further modifications of your code.

Cheers Allan !

allan

$request->request->get('columns')

Could you possibly clarify where that code is for me please? Certainly, the primary key shouldn't be searchable by default (it doesn't appear to be so here).

Thanks,
Allan

kevou84

Sorry it's specific way to access request parameters from the PHP framework I am using.

The exact location in SSP class you are looking for is where $request['columns'] appears in filter function, it includes an extra record corresponding to DT_RowId added server-side :

static function filter ( $request, $columns, &$bindings )
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck( $columns, 'dt' );
        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            $str = $request['search']['value'];
            for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];
                if ( $requestColumn['searchable'] == 'true' ) {
                            [...]
            }
        }

As a proof, I have added $column['dt'] != 'DT_RowId' in the if $requestColumn['searchable'] == 'true' and now works well, but it doesn't feel right, the extra record shouldn't be in $request['columns'][0].

The link you provide me with doesn't seem to make usage of ssp.class.php as in other examples, and the headers are defined in the HTML code instead of server-side like in my case. The rest looks the same.

Note that I verified data in/out and everything follows the required format. This means I send correct "columns" information to client-side (with name, title, etc...), then I send correct "data" to client-side (DT_RowId, col1, col2, etc...). Then if I make a search I receive the extra column in the request server-side, although it was never declared as such.

Cheers !

allan

Oh I see, this is in the SSP class, rather than in Editor.

Although with the SSP class and the ids being added using DT_RowId in this example it doesn't search the row id. Search for 31 for example. Only row id 14` is found.

Allan

kevou84

I looked and it has another behavior indeed...
... But I used the exact same tutorial :

$columns = array(
    array(
        'db' => 'id',
        'dt' => 'DT_RowId',
        'formatter' => function( $d, $row ) {
            // Technically a DOM id cannot start with an integer, so we prefix
            // a string. This can also be useful if you have multiple tables
            // to ensure that the id is unique with a different prefix
            return 'row_'.$d;
        }
    ),

This comes back as first column (id) in $request['columns'][0]...
So, as I checked my data and used the tutorials, I figure this might come from the fact I am using jQuery 2.1.* instead jQuery 1.11.*. Seems like the most probable source of difference in datatables javascript behavior. Keep this in mind once you migrate to jQuery 2.x then ^^ Thanks for your time and consideration.

allan

I don't see how jQuery versions would effect this, but I'll certainly keep an eye out for it.

Allan

kevou84

You never know, since there are two versions maintained, there may be some BC breaks on some use cases. I can assure you I did everything by the book so I can't see no other mistake I could do on my side ^^ Cheers!