Sorting using column names

Sorting using column names

macateemmacateem Posts: 10Questions: 3Answers: 0

I've seen several questions related to this but no definitive answer. I've got an AJAX sourced table, using server-side processing (large data set) and want to use column names instead of column indexes to sort by. Since I'm passing the order[i] parameters to my API, the API will have no idea what order[0][column]=2 means. I need it to read order[0][column]=salary (for example). What's the best way to do that?

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    If you can't alter the API at the server-side to convert the index using the information sent in the columns array (which is the method I would recommend you use), then you'd need to use ajax.data to modify the data that is being sent to the server to do that conversion on the client-side.

    Allan

  • macateemmacateem Posts: 10Questions: 3Answers: 0

    Well I disagree about mapping these on the server. There's no way for the API to know what the 3rd column is. The API should receive column names. So I'll take a look at the ajax.data option. What I did in the meantime was add a sort parameter to data in a xhr.dt event:

    $('#gridProperties').on('preXhr.dt', function (e, settings, data) {
                    sortColumns = [];
                    for (let i = 0; i < data.order.length; i++) {
                        col = data.order[i].column;
                        dir = data.order[i].dir;
                        sortColumns.push((dir === 'desc' ? '-' : '') + data.columns[col].data);
                    }
                    data.sort = sortColumns.join();
                });
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    There's no way for the API to know what the 3rd column is

    It can map it based on the columns.data property that is submitted. This is what the demo SSP script does.

    I agree it isn't the most intuitive though - and I will change that for v2 to include the column name by default in the submitted data for the ordering.

    Thanks,
    Allan

  • macateemmacateem Posts: 10Questions: 3Answers: 0

    OK, sounds good. Thanks.

  • LeonardFrancisLeonardFrancis Posts: 1Questions: 0Answers: 0

    " will change that for v2 to include the column name by default in the submitted data for the ordering."

    Is this implemented ??

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Not yet, and it'll be a while before V2 is release - so Allan's recommend workaround is the way to go for the near-future.

    Colin

  • rivaldidrivaldid Posts: 55Questions: 6Answers: 2

    What about sending columns().visible() in ajax data d array?
    I'm trying
    "ajax": {
    "url": uri_api,
    "method": "POST",
    "data": function(d) {
    $(this).columns(0).visible() === true ? console.log('col 0 visible') : console.log('col 0 not visible');
    return JSON.stringify($.extend(d, { "arguments" : "foo"}));
    }
    }
    I'm getting error,
    Uncaught TypeError: jquery__WEBPACK_IMPORTED_MODULE_0___default(...)(...).columns is not a function

    Maybe my fault have to search, by the way I'm thinking to test every column in my dt object and send the state to the backend, so I can fix the mapping when I'm searching with individual filter that it's column is AFTER an hidden column.
    Sure if I can send an array of the actual state of local table header would be perfect for my fit.

  • rivaldidrivaldid Posts: 55Questions: 6Answers: 2

    I have changed method: same strategy but like this
    d.order[0]['col_name']='foo';
    console.log(table.columns(0).title);
    this in ajax part, I don't understand how to find it, I'm into the constructor. how use myself to print my own columns(0).title?

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin
    edited March 2021

    The ajax.data function isn't executed in the scope of the DataTables instance, so $(this).columns(0).visible() would indeed result in an error. What you need to do to be sure of getting an API instance there is use the second parameter passed into the function:

    ajax: {
      data: function (data, settings) {
        let api = new $.fn.dataTable.Api( settings );
    
        // now use `api.columns().visible()` to get the column
        // visiblity, or loop over the columns object if you want it per column
        for (let i=0; i<data.columns.length; i++) {
          data.columns[i].visibility = api.column(i).visible();
        }
      },
      ...
    

    Allan

This discussion has been closed.