Multi-column sorting in datatables

iamamita

I have a table and it has 8 columns. Suppose their headers viz 'A', 'B'..... 'H'.

I want to apply multi-column sorting like:

Default view: Case1: If user clicks on 'A' then sorts the column as 'A' (desc) - > 'B' (asc) - >'E' (asc) - >.... - > 'H' (asc)

And if user again clicks on 'A' then nothing should happen.

Case 2: If user clicks on 'E' then sorts the column as 'E' (asc) - > 'A' (desc) - > 'B' (asc) - >'F' (asc) - >.... - > 'H' (asc)

And if user again clicks on 'E' then nothing should happen.

Case 3: If user clicks on 'F' then sorts the column as 'F' (asc) - >'A' (desc) - > 'B' (asc) - >'E' (asc) - >.... - > 'H' (asc)

And if user again clicks on 'F' then nothing should happen.

Please help me out as it's my first day on data tables.

Thanks in advance

rf1234

This should be what you are looking for.
https://datatables.net/reference/option/columns.orderData

This part is a duplicate of one of your other posts:
"And if user again clicks on 'A' then nothing should happen.
And if user again clicks on 'F' then nothing should happen.
etc."

Try to avoid duplicate posts please.

iamamita

Thanks for your suggestions. Can you please provide some examples? As I have tried a lot but didn't get successful and I am working on datatables first time.

rf1234

https://datatables.net/examples/basic_init/multi_col_sort

https://datatables.net/examples/
plenty of examples ...

kthorngren

I don't think orderData supports ordering some columns ASC and DESC. You could mess with Orthogonal Data to accomplish what you want but that might provide difficult depending on the data.

I think an easier approach (instead of removing the click listeners) would be to use orderSequence to set the ASC or DESC order you want to always have for the column when its clicked. Then create event handler for the header to determine the column clicked and set the ordering appropriately.

See this example:
http://live.datatables.net/xamojocu/1/edit

Kevin

iamamita

Kevin, I am very glad that you are helping me.
Please have a look at https://s.docworkspace.com/docs/3Hj58r9qB to get clarification on what exactly I want to do.

kthorngren

The key is to set the sort array in the priority order. You will notice that when clicking on the Position column it is listed first in the order() API followed by the Office column. The Offices will each be sorted within each Position.

For the columns you don't want the end user to sort use columns.orderable.

Kevin

iamamita

Hi Kevin, can you please provide me with the examples. I am unable to understand the datatable's documentation examples for my use case.

kthorngren

I updated the example to show using columns.orderable to disable the clicking on the Age column to order.
http://live.datatables.net/xamojocu/2/edit

Is that what you are asking about?

Kevin

iamamita

Only one problem solved with this that is double click doesn't do anything. But my main problem is still there that is multi-column sorting and for that, I have given you the link https://s.docworkspace.com/docs/3Hj58r9qB

kthorngren

My example shows that. As I explained earlier, Click on the Position column and you will see it affects the sorting of both Position and Office. The code for this is in the initComplete function.

Kevin

iamamita

Hello, please look at the code what I have done

$(document).ready(function() {
  window.history.pushState("object or string", "EPAP|AuctionList", contextPath+"/auctions/auctionlist");
  languageList.sEmptyTable = '<spring:message code="auctionlist.emptytable"/>';
      
  auctionTable = $('#datatable').DataTable({      
    language: languageList,
    order: [[0, 'desc']],
    columnDefs: [
        {targets: 0, orderSequence: ['desc']},
        {targets: 1, orderSequence: ['asc']},
        {targets: 4, orderSequence: ['asc']},
        {targets: 5, orderSequence: ['asc']},
        {targets: 6, orderSequence: ['asc']},
        {targets: 7, orderSequence: ['asc']},            
        {type: "datetime-moment",targets: 5}
    ],
     
    initComplete: function () {
        $('#datatable th').on('click', function (event) {

            var auctionTable = $('#datatable').DataTable();
            var th = $(this).closest('th');
            var colIndex = auctionTable.column( th ).index();
            console.log(colIndex);

            if (colIndex === 0) {
                console.log('order col 0 ')
                auctionTable.order([[0, 'desc'], [1, 'asc'], [4, 'asc'], [5, 'asc'],[6, 'asc'],[7, 'asc']]).draw();
            }else if (colIndex === 4) {
                console.log('order col 4 ')
                auctionTable.order([[4, 'asc'],[0, 'desc'], [1, 'asc'], [5, 'asc'],[6, 'asc'],[7, 'asc']]).draw();
                }else if (colIndex === 5) {
                    console.log('order col 5 ')
                    auctionTable.order([[5, 'asc'],[0, 'desc'], [1, 'asc'],[4, 'asc'] ,[6, 'asc'],[7, 'asc']]).draw();
                    }else if (colIndex === 6) {
                        console.log('order col6 ')
                        auctionTable.order([[6, 'asc'],[0, 'desc'], [1, 'asc'], [5, 'asc'],[4, 'asc'],[7, 'asc']]).draw();
                        }else if (colIndex ===7) {
                            console.log('order col 7')
                            auctionTable.order([[7, 'asc'],[0, 'desc'], [1, 'asc'], [5, 'asc'],[4, 'asc'],[6, 'asc']]).draw();
                            }           

        });      
    },   
    
    "aoColumnDefs": [
      { "bSortable": false, "aTargets": [ 2,3,8 ] }
    ] ,

    "ajax": {
      "url": (contextPath + "/auctions/dailyAuctionViewList"),
      "cache": false,
      "asyc":false,
      "dataSrc": ""
    },

And Its output :

What I want?:

1. Begin Time should be in 'asc' order but a user can't apply to sort using begin time.
2. Default view: I am indicating using column index instead of column's name
   [ [0, 'desc'], [1, 'asc'], [4, 'asc'], [5, 'asc'],[6, 'asc'],[7, 'asc'] ]
   First 0 index column sorts then 1 index and so on
3. If user clicks on 4th index then [[4, 'asc'],[0, 'desc'], [1, 'asc'], [5, 'asc'],[6, 'asc'],[7, 'asc']]
4. 5th index, [[5, 'asc'],[0, 'desc'], [1, 'asc'],[4, 'asc'] ,[6, 'asc'],[7, 'asc']] , and same for 6th and 7th index

But the columns in the output image are not correctly sorts.

kthorngren

In line 8 you have columnDefs and in line 46 you have aoColumnDefs. These are the same config option but the aoColumnDefs is the previous version of the option. One option will overwrite the other since they are the same. They configs you want need to be combined into one option. The Conversion Guide explains the old option format and conversion to the new.

Begin Time should be in 'asc' order but a user can't apply to sort using begin time.

You have order: [[0, 'desc']], which will default to ordering the first column decs when the Datatable is initialized. You can change it to order: [[1, 'asc']],. You also need to use the columns.orderable option for this column to trun off the users ability to sort the column.

But the columns in the output image are not correctly sorts.

Thats hard to debug by a screenshot. But it looks like this is the initial load of the table and it is ordered by the config option order: [[0, 'desc']],.

Please post a link to your page or a test case so we can take a look at the data and see how it is behaving.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

iamamita

It's a spring application so the code is with .jsp extension instead of .html.

Still, you can I have the look at live.datatables.net/feguyimi/1/edit

kthorngren

Sorry, that doesn't help much since it doesn't run and doesn't have your data. Can you take an example of your data and use the data option to insert it into the Datatable? This will replace the ajax option you have. Here is an example:
https://datatables.net/examples/data_sources/js_array.html

Kevin

iamamita

I have edited it live.datatables.net/bufemafa/1/edit. Please look into it now and before going through the code please look into this https://s.docworkspace.com/docs/3Hj58r9qB

kthorngren

Thanks for updating the example. It was generating the following error:

Uncaught TypeError: Cannot set property '_DT_CellIndex' of undefined

The error was due to {orderable: false, targets:[1,2,3,8]},. The 8 is referring to the 9th column which you have only 8. I removed it. Here is the updated example:
http://live.datatables.net/rumexeki/1/edit

If I click on Auction Date a quick scan it looks right. If its not working please provide the specifics of issues and steps to recreate the problem.

Kevin

iamamita

It's working here live.datatables.net/bufemafa/1/edit but not in my actual code

kthorngren

Sorry, what is not working in your code?

Do you get errors in the browser's console?

Without seeing or know when the problem is its hard to guess.

Kevin