Absolute custom sorting based on list

lordterrin

I'm trying to figure out a way to sort my datatable by an absolute list, but I don't see any documentation on how to do that.

What's an absolute list, you ask? Well I just made it up, so I'll explain:

I'm doing a serverside table that uses the column headers to perform filtering of the table based on that column. If the user wants to sort the "name" column and search for four names, "John, Mike, Bob, Andy", he would type "John|Mike|Bob|Andy" into the "Name" column header.

I want the results to be sorted BY those names, in the order that the user typed them.

First, all of the results for John, then all of the results for Mike, then Bob, and lastly, Andy.

Is this possible?

lordterrin

Okay, I figured out a solution.

First, I created a new sorting variable in PHP when the user searches for multiple things:

$sort = 0;
foreach ( $column_ar as $c ) {
  $sort++;
  $sorting_array[$c]  = $sort;
}

This returns something like this:

$sorting_array['John'] = 1, $sorting_array['Mike'] = 2 etc etc...

Then, since DataTables server-side implementation won't allow me to actually SORT on a field that doesn't exist in the database, (or maybe it's the database that won't return values if I try to "sort descending by a column that doesn't exist", if the user chooses to sort by this custom column, I change the sort to something else, run everything, then resort before I display out to the user:

$filter_on_column_number  = $_POST['order'][0]['column'];
$filter_on_column_value   = $_POST['columns'][$filter_on_column_number]['data'];

if ( $filter_on_column_number == 15 ) {   #15 is my "custom sort" field
  $multi_search = true;
  $filter_on_column_number  = 10;
  $filter_on_column_value   = 'date_string';  # doesn't matter what this is - as long as it's something that will cause the search to work.
}  

Once the query runs successfully, I add the sorting data manually into the array. My output array that would normally be echoed back to datatables is called $list, so:

if ( $sorting_array ) {
  foreach ( $list as &$l ) {    
    $l['custom_sort'] = $sorting_array[$l['part_number_s']];  
  }
  unset($l);

  /* since DataTables server-side implementation cannot sort by a field a that doesn't actually exist, we need to resort the array in 
     the user's custom "copy and paste" order: */ 
  if ( $multi_search ) {
    usort($list, function($a, $b) {
      return $a['custom_sort'] - $b['custom_sort'];
    });
  }
}

Then I output the newly modified $list out back to DataTables, which is none the wiser that I've circumvented its defaults.

I hope this helps someone who may be looking to do something similar!

allan

Thanks for posting back. Great to hear you've got this working and thanks for sharing your solution.

Allan