Sort of complex render

rpmccormick

My datatable display is fairly complex.

I have spent many hours on many different methods to try to enable date-sorting, and have had no luck. I even tried the moment plugin which would be perfect as I already use moment to render, but I cannot get it to work.

Actually, more perfect than the moment plug-in would just be to let me specify 3 different column values for display vs. sort vs. search.

Here is my display code:

{"data": null, "render": function (data, type, row) {return paintCIN(data.CheckinDate, data.CheckinStatus, data.CheckinType)}},

Here is the function it calls:

function paintCIN(CIDate, CIStat, CIType)
{var Color = "purple";
var CI = moment(CIDate);
var CIT = "<br />       <small><i><span style=\"color: #aaa;\">";

if (CIType == 1) {CIT += "via call</span></i></small>"}
else if (CIType == 2) {CIT += "walk-in</span></i></small>"}
else if (CIType == 3) {CIT += "via web</span></i></small>"}
else if (CIType == 4) {CIT = ""}
else {CIT += CIType + "</span></i></small>"}

if (CIStat == 0) {Color = "red"}
else if (CIStat == 1) {Color = "navy"}

if (moment().diff(CI, 'days') == 0) {Color = "green"}
else if (moment().diff(CI, 'days') > 0) {Color = "#aaa"}

return moment(CIDate).format("[<span style=\"color: " + Color + ";\"><small>]ddd[,</small> <b>]MMM[ ]Do[</b> <small>]YYYY[</small></span>]") + CIT;
}

Here is a sample of the column display:

Fri, Jun 12th 2015
via web

Here is what I tried with the plugin:

$.fn.dataTable.moment( "ddd, MMM Do YYYY" );

And I tried making every possible full combo as well:

$.fn.dataTable.moment( "[<span style=\"color: #aaa;\"><small>]ddd[,</small> <b>]MMM[ ]Do[</b> <small>]YYYY[</small></span>]walk-in</span></i></small>]");

I have also tried to make it sort from a different member of data, and from a different hidden column.

NOTE: This is all on normal client-side tables (I learned that for my SSP tables I have to disable sorting and searching on basically all but one column... or it locks up the mysql server with the length of the query - separate issue, already solved, kinda)

allan

I even tried the moment plugin which would be perfect as I already use moment to render, but I cannot get it to work.

Perhaps you can link to a page showing the issue so we can debug it please?

I learned that for my SSP tables

If you are using server-side processing then the Moment plug-in won't help since that is client-side and with server-side processing all the ordering is done at the server-side.

If you are loading data via Ajax for client-side processing I would very much suggest using orthogonal data rather than mixing HTML formatting into the moment plug-in, which looks very complicated and rather fragile.

Allan

rpmccormick

This sorting issue is for my non-SSP tables. I use both.

I have tried to use orthogonal data but do not understand... it seems the sort data must be included in your JSON, but I am using your PHP libraries for that and do not know how to add sorting-data.

Really, I already have all the sort-fields in the JSON already... the issue is that my columns are rendered from multiple fields with formatting, and I do not understand how to tell DT just to sort by one of them with no formatting.

EXAMPLE: For a single column in a DT table, checkin-date is rendered with moment on first line of cell, and checkin-type is rendered on second line of cell.

RENDER = moment(data.checkin-date) + <br> + formatType(data.checkin-type).
All I need to do is set SORT = data.checkin-date... how do I do that?

rpmccormick

Here is a picture of my table

SORT PICKUP | Pickup Date | Other columns

 141208           |     Mon. Dec 8th     |     ...
   05:47                     at 5:47 am

My goal is to get rid of the SORT PICKUP column, and to make the Pickup Date column sort correctly.

$('#Fares').dataTable(
 {"dom": "Tfrtlip",
  "ajax": "/DataTables/media/php/table.OWLFares.php",
  "columns": [ {"data": "FarePickupTime", "render": function (data, type, row) {return moment(data).format('YYMMDD[<br /><i><small>]HH:mm[</small></i>]')}},
               {"data": "FarePickupTime", "orderable": false, "render": function(data, type, row) {return paintPickupTime(data)}},
               {"data": null, "orderable": false, "render": function (data, type, row) {return paint...}},
             ],
  "tableTools": {"sRowSelect": "os", "aButtons": [ {"sExtends": "editor_create", "editor": fareedit},
                                                   {"sExtends": "editor_edit",   "editor": fareedit},
                                                   {"sExtends": "editor_remove", "editor": fareedit},
                                                   {"sExtends": "collection", "sButtonText": "Export Table", "sButtonClass": "save-collection", "aButtons": ['copy', 'csv', 'xls', 'pdf']},
                                                   {"sExtends": "print", "sButtonText": "Print Table", "sMessage": "One Way Limo"}
                                                 ]
                },
  "responsive": { "details": {"type": "column"} },
  "order": [0, 'desc'],
  "deferRender": true,
 } );

allan

What is FarePickupTime? Is it an integer timestamp? In which case you could just use that for the sorting value:

render: function ( data, type, row ) {
  if ( type === 'sort' || type === 'type' ) {
    return data;
  }
  ... else do Moment rendering for display
}

Allan

rpmccormick

Awesome, that worked!

I had tried that method before, but I mistakenly had only tried it on a ServerSide table which didn't work.

I am totally happy I got a solution to the non-SSP tables. Thanks!

Now how can I specify the sort for my SSP tables? Here is an example:

$('#Tasks').dataTable( 
 {"dom": "Trtlip",
  "processing": true,
  "serverSide": true,
  "ajax": "/DataTables/media/php/table.WorkOrders.Lazy.php",
  "columns": [ {"data": null,
                "searchable": false, 
                "orderable": false, 
                "render": function (data, type, row)
                           {if ( type === 'display' || type === 'filter' || type === 'type')
                             {return paintWOStatus(data.close_workorder) + ' ' + paintWOType(data.order_type)}
                            else
                             {return data.WorkStatus}     //for sorting
               },

I want the column to display color-coded data from 2 fields, but when they click I the header to sort that column, I want SSP to sort based on the name of one of those fields (WorkStatus). I am using your ssp.class.php...

table.WorkOrders.Lazy.php

<?php

include_once('config.php');

$table = 'DT_WorkOrders';
$primaryKey = 'WorkID';
 
$columns = array(
    array( 'db' => 'WorkID',     'dt' => 'WorkID'     ),
    array( 'db' => 'WorkType',   'dt' => 'WorkType'   ),
    array( 'db' => 'WorkStatus', 'dt' => 'WorkStatus' ),
    array( 'db' => 'WorkRooms',  'dt' => 'WorkRooms'  ),
    array( 'db' => 'LastUpdate', 'dt' => 'LastUpdate' )
);
 
session_start();
 
//Get Data from Table
require( 'lib/ssp.class.php' );
$result=SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns ); 

//Decode Rooms from other table
foreach($result['data'] as $key=> $singleOrder)
{$workOrder[$key]=$singleOrder;
 $worder='';
 $order_ary=explode(',',$singleOrder['WorkRooms']);
 if(is_array($order_ary))
  {foreach($order_ary as $oid)
    {$worder.=$assets[$oid].', ';}
  }
 $workOrder[$key]['WorkRooms']=$worder;
}

//Return
$result['data']=$workOrder;
echo json_encode($result);

Thanks again Allan! -Ryan

allan

I mistakenly had only tried it on a ServerSide table which didn't work.

So the reason it didn't work for server-side processing is that the render method is a client-side construct, but with server-side processing the ordering is done at the server-side. Therefore, it is the data at the server that would need to be modified. Thus, you would need to update whatever script you are using at the server-side to cope with orthogonal data - the SSP::simple demo script is not designed to handle such cases.

Allan