Not sorting on formatted string

mysilver

Hi,

I am using PHP 8.3 and DataTables 1.10.18.
The problem is sorting on column 1 doesn't sort on formatted strings which are displayed on Datatables.
It sorts on 'exp_category_id' as described in the following code:

[PHP server-side script]

    array(  
      'db' => 'exp_category_id',   
      'dt' => 'title',
      'formatter' => function($d, $row) {
          return $row['category_slug'];                   
      }
    ),    

[Javascript code]

        "aoColumns": [
            {data : "serial_no"},
            {data : "title"},                          
            {data : "this_month"},
            {data : "this_year"},    
        ],

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here. We don't need the back, just a page showing the data that isn't sorting for you,

Colin

colin

Also, that version of DataTables is about 5 years old, so it would be upgrading to the latest,

Colin

mysilver

I cannot write a test case since there is no way to write server-side PHP script in the test case. I am feeding data from ajax call. However, I can provide debug code generated from DataTables debugger. The debug code is: ayanuw

I tested with latest version of Datatables but the result is the same.
I defined 4 columns but the first column always disappear and replaced by row numbering column even though I did not specify to show row numbering. And the ordering on the column 1 (second column from the left) does not work properly. If I set the column 1 in server-side script without using "formatter=>" (in PHP) to be as follows, it works correctly:

array( 'db' => 'category_slug', 'dt' => 'category_slug' ),

Hoever, it doesn't sort properly when I format it using "formatter=>" in PHP as follows:

    array(  
      'db' => 'exp_category_id',   
      'dt' => 'title',
      'formatter' => function($d, $row) {
           $category = get_the_expense_category($row['exp_category_id']);
           return $category['category_name'];                            
      }
    ),   

And, the first column ("serial_no") defined in the following code disappears, and replaced by row numbering column which I didn't specify to appear.

"aoColumns": [
    {data : "serial_no"},
    {data : "title"},                         
    {data : "this_month"},
    {data : "this_year"},   
],

mysilver

I found out that I specified row numbering column to appear at column 0 (first column). I removed it but the ordering on column 1 ("title") still doesn't work properly.

kthorngren

Its difficult to troubleshoot sorting issues without seeing the actual data. Use the browser's network inspector tool to get a sample of the JSON data. See this technote for instructions. Build a Javascript sourced test case similar to this example using the sample data from the JSON response.

the first column always disappear and replaced by row numbering column even though

Datatables won't create a row numbering column. Maybe you have something like this index column example. Please build a simple test case that shows the issue you are having so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

mysilver

Please use the following debug code: avirab

allan

It looks like you are using the demo SSP class for server-side processing. That means that the sorting is happening in the database, before the formatting is performed on the resulting string.

If you need server-side processing (i.e. you have at least tens of thousands of rows of data), and you need to order by formatted data, you'd need to create a VIEW that does the formatting in SQL, and then query the VIEW.

Allan