Sorting a column AFTER render fuction with server-side.

Sorting a column AFTER render fuction with server-side.

ManusoriManusori Posts: 9Questions: 0Answers: 0

Hi all !

I have been trying to make a data reorder work for several days, but even though I have given up, I am not losing hope.

I've prepared a table with five columns. Each column extracts the data from a DB and, specifically in two columns, I need to reconvert the data I extract from the database to a more friendly format (The data is saved in seconds, and I convert it to "HH:mm").

I've seen many discussions in this forum with similar problems, even one in stackoverflow with exactly my same problem, but I must be doing something wrong because I can't get the seconds converted to the local time format of the visiting client to reorder them from 00:00 am.

I explain myself, I have a table with 48 lines, each one of them is the price it has every half hour. However, the time has to be reconverted to the local time of the visiting customer. The server is hosted in Houston (Texas) and therefore 00:00 am will be 07:00 am in Valencia (Spain). That means, the first record that has to appear (In Spain), is the 17:00 in Houston (Which converted to local time in Spain are 00:00am, but not reorder the column). It keeps the order with the original data of the table, so it is ALWAYS ordered from 00:00am in Houston.

The time conversion is a very simple function that works correctly within the 'render' option of dataTables, but dataTables does not reorder my data once converted, and I do not know what to do anymore.

I tried to replace the render result with random numbers, and it doesn't reorder them either. Maybe the problem is something very simple that I can't see. I hope you can help me.

I copy the last code that gives me the data ordered in columns 0, 1 and 2, but column 2 reorders it from the natural values of the DB.

function convertTableHour(data, type, row, meta){
    if ( type === 'display' || type === 'filter' ){
        var finalLocalHour = convertZeroDateToLocalDate(moment.tz("1983-07-05 "+(new Date(data * 1000).toISOString().substr(11,8)), "America/Chicago"), "HH:mm");
        return finalLocalHour;
    }
    return data;
}

var table = $("#timescheduleparam").DataTable({
        ajax: $.fn.dataTable.pipeline( {
            url: Routing.generate('timescheduletable', {"tollingsegmentid": $("#tollingsegmentid").val(), "daytypeid" : $("#daytypeid").val(), "dayofweektimeofdaygroupid": $("#dayofweektimeofdaygroupid").val() }),
            pages: 25
        }),
        columnDefs: [{
                targets: 0,
                data: "daydescription"
            },{
                targets: 1,
                data: "description"
            },{
                targets: 2,
                data: "starttime", render: convertTableHour
            },{
                targets: 3,
                data: "endtime",
                render: function (data, type, row, meta) {
                    var finalLocalHour = convertZeroDateToLocalDate(moment.tz("1983-07-05 "+(new Date(data * 1000).toISOString().substr(11,8)), "America/Chicago"), "HH:mm");
                    return finalLocalHour;
                }
            },{
                targets: 4,
                data: "tollamount",
                render: function (data, type, row, meta) {
                    return data != null ? "$ " + (data / 100).toFixed(2) : "";
                }
            }],
        autoFill: true,
        keys: true,
        lengthMenu: [[25, 50, 100, 500], ['25', '50', '100', "500"]],
        pageLength: 25,
        pagination: true,
        searching: false,
        ordering: true,
        serverSide: true,
        processing: true,
        order: [[ 0, "asc" ], [ 1, "asc" ], [2, "asc"]],
        initComplete : function () {
            console.log("Table loaded in " + (performance.now() - t0).toFixed(2) + " milliseconds.");
            attachButtons();
            resetTables();
            updateTables();
        },
        dom: 'Blfrtip'
        //buttons: [ 'copy', 'csv', 'excel', 'pdf', 'print']
    });

Replies

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

    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.

    Cheers,

    Colin

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    I will try to replicate the data in the "live.datatables" in case I manage to condense the problem a bit and focus it on what is important.
    However, I won't be able to replicate it in the same conditions, since I'm facing a "server-side / pipeline" construction (It's an internal application, it needs VPN, etc...) and, if I have the data written in an HTML page, it might respond well and I wouldn't be able to solve the problem.

    As soon as I have it finished I will publish it in this post.

    Thank you very much for your time.

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    Hello again, @colin !!

    I managed to bring the data without being "server-side" and the error still persists. Which is good, because then we can all try some solution.

    The address of the js.bin is:
    https://jsbin.com/zuzacox/edit?html,js,output

    The problem is this: I have a table with 5 columns, which are arranged in an ascending order from the first, second and third columns. The problem comes in the third column.
    The datatables plugin sorts it according to the original data and not according to the resultant data that it gets after "render".

    In the "render" parameter I have put a function to take the original value in seconds, convert it to the time of the time zone in Chicago and then convert it to the time of the geographical area of the client that visits the web. But it keeps ordering the table as if it started from 00:00 with the converted time.
    So, the first line of the table (While I am in Valencia, SPAIN), is like this according to datatables:
    Normal Day | TS10 | 07:00 | 07:30 | 0.00€

    When in fact, it should be the first line like this:
    Normal Day | TS10 | 00:00 | 00:30 | 17.00€

    Because when it's 00:00 in Spain, it's 17:00 in Chicago (DST at the moment). I have put in the price (Chicago DST) the value of the hour so that it can be seen more clearly.

    I guess it's silly, but I can't find out which one. If you can help me, I'll be very grateful.

    Thank you for your time once again.

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

    I think you just need to always return that formatted data - see here. That way the type will be correct for order and type too in the columns.render function.

    Hope that does the trick,

    Colin

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    Unfortunately it didn't work for me.
    In fact, in my initial code that I put in this post, it's both ways. The function with the "type" mode and directly developed in the "render" parameter.
    Although right now, it's like in "js.bin":
    render: convertTableHour (Without type parameter).

    The version of DataTables I use is 1.10.20 (The last one).
    Do you think that there is a possibility to process the data before using it in the column?

    Thanks once more, Colin.

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

    I'm a bit confused - is the example I posted above not working? Or it's not working when you put it back into your code?

    If the example isn't, please could you say what you would expect the sorting to be?
    If it's your code, can you link to your page, please.

    Colin

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    I can't get a link because the server is internal and I need a VPN.
    Same goes for the database.

    I pasted your changes into the page code and when I said it didn't work, it was because it didn't work with the changes made. But then I remembered that that option (Your option) was the one I started with and that's why it was written in one of the columns in the first post I put.
    But it doesn't matter because one way or another it doesn't work.

    I paste exactly the code as I have it in my view. By the way, I don't know if I mentioned that this is a twig template for Symfony 2.4. So far everything I have in Javascript works quite well with Symfony.

    function convertTableHour(data, type, row, meta){
            var finalLocalHour = convertZeroDateToLocalDate(moment.tz("1983-07-05 "+(new Date(data * 1000).toISOString().substr(11,8)), "America/Chicago"), "HH:mm");
            return finalLocalHour;
    }
    
    $(document).ready(function() {
        var table = $("#timescheduleparam").DataTable({
            ajax: $.fn.dataTable.pipeline( {
                url: Routing.generate('timescheduletable', {"tollingsegmentid": $("#tollingsegmentid").val(), "daytypeid" : $("#daytypeid").val(), "dayofweektimeofdaygroupid": $("#dayofweektimeofdaygroupid").val() }),
                pages: 25
            }),
            columnDefs: [{
                    targets: 0,
                    data: "daydescription"
                },{
                    targets: 1,
                    data: "description"
                },{
                    targets: 2,
                    data: "starttime", 
                    render: convertTableHour
                },{
                    targets: 3,
                    data: "endtime",
                    render: convertTableHour
                },{
                    targets: 4,
                    data: "tollamount",
                    render: function (data, type, row, meta) {
                        return data != null ? "$ " + (data / 100).toFixed(2) : "";
                    }
                },{
                    visible: false,
                    targets: 5,
                    className: "text-center",
                    sortable: false,
                    data: null,
                    render: function (data, type, row, meta) {
                        return '<button class="btn btn-primary btn-sm" onClick="edit(' + row.dayofweektimeofdayscheduleid + ', 1, 1)"><span class="glyphicon glyphicon-pencil"</span></button>';
                    }
                },{
                    visible: false,
                    targets: 6,
                    className: "text-center",
                    sortable: false,
                    data: null,
                    render: function (data, type, row, meta) {
                        return '<button class="btn btn-danger btn-sm" onClick="tryDelete(' + row.dayofweektimeofdayscheduleid + ')"><span class="glyphicon glyphicon-remove"</span></button>';
                    }
                }
            ],
            autoFill: true,
            keys: true,
            lengthMenu: [[25, 50, 100, 500], ['25', '50', '100', "500"]],
            pageLength: 25,
            pagination: true,
            searching: false,
            ordering: true,
            serverSide: true,
            processing: true,
            order: [[ 0, "asc" ], [ 1, "asc" ], [2, "asc"]],
            initComplete : function () {
                console.log("Table loaded in " + (performance.now() - t0).toFixed(2) + " milliseconds.");
                attachButtons();
                resetTables();
                updateTables();
            },
            dom: 'Blfrtip'
            buttons: [ 'copy', 'csv', 'excel', 'pdf', 'print']
        });
    

    I hope we can do something. If not, then I'll resign myself to keeping the hours arranged according to another region hahaha

    Thank you, Colin!!

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Maybe I'm missing the problem but since you are using server side processing the server script is responsible for the order. The columns.render function won't affect the order just what is displayed.

    I have a table with 48 lines,

    If that is all the data you have then remove serverSide: true, to let the client side perform the sorting. See this FAQ about when to use server side processing.

    Kevin

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    Right, I said I had 48 records in a table, but that was to generate the assumption and write an example. The reality is that on the same website, I have pages with up to 482,000 records.

    The order option is working very well in the first column and in the second column. It groups the concepts first by "Type of day", then by "Segment" and, finally the third column orders it from the hour 0 seconds (00:00am ) to the hour 84600 seconds (23:30) but always with respect to the time region of "America/Chicago" (even if it does well the conversion in render).

    Hence my doubt. If the "order" option in Datatables really didn't work correctly, in tables with so many records and so disordered, it would have been very evident.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Not sure if there is still a question but if so what are you using for your server script query?

    Can you save your timestamps in UTC instead of the Houston timezone? I think it might be easier for the server query to properly sort the data then the client can display the time in local time.

    Again I might not understand the problem but it sounds like the server's DB query needs to perform the timezone conversion as part of the query in order to get the sorting correct. If the Datatable is in the wrong order it needs to be fixed in the server script not with columns.render. Maybe you can post what you have for the server script.

    Kevin

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    The content of the table in the database, is in seconds and recorded with respect to Houston time. That is, if you extract a record from that table, you are extracting time information about Houston. Whether it is recorded in UTC or in American/Houston time does not change things because, for example, if I make a query from Spain, it has to add two hours (in summer) with respect to UTC and seven hours (in summer) with respect to Houston. I will always have to do a conversion.

    My problem is that Datatables does not sort (Server-side) the third column if you have a script that multiplies a record previously extracted from the database. It doesn't matter the database query script, because the first column does sort it (it doesn't have any script in "columns.render" to multiply the record) and the second column also sorts it (it doesn't have a record to multiply the result either). On the other hand, the third column that must be multiplied according to the local time, sorts the column according to the data received in the database (Exactly the same as in column 1 and column 2), but not according to the script that converts the time obtained to local time, which is what I want.

    That's why I'm here, to see if anyone has had a similar problem and ask them how they solved it. If the problem was the query with PHP or Symfony I would have solved it already. But the script doesn't sort any data. It's just a SELECT with the variables needed to shorten at 00:00 am at the beginning and end of the query.

    I hope I made myself clear.

    If you can help me, I will be grateful (I am sure that in the future there will be people with this problem and they will be able to come here to apply a solution). If it can't be, I will talk with my responsibles in case we can leave it like that or change Datatables for a more manual method.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Are you using a Datatables provided server side script?

    With server side processing enabled all sorting, searching and paging functions are to be performed by the server script. The client side Datatables will send parameters to the server with sorting and searching directives as documented here. You server script will need to handle this requirement:

    the third column that must be multiplied according to the local time, sorts the column according to the data received in the database (Exactly the same as in column 1 and column 2), but not according to the script that converts the time obtained to local time, which is what I want.

    And return the proper data. You wont be able to do this by using columns.render unless you disable server side processing.

    Kevin

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    Okay,
    I'm going to study up on how to do it. The query scripts were programmed by my predecessors. If I can find a good formula, I'll publish it here.

    Thank you!

  • ManusoriManusori Posts: 9Questions: 0Answers: 0

    Hello again.
    I managed to solve the problem by transporting the time zone offset to the page controller (This project, unfortunately, is in Symfony).

    The first thing, is in the .twig page, collect in a variable the time region of the client computer (For this, I used a plug-in called "moment"), and I include it to transport it with the table:

    $(document).ready(function() {
        var t0 = performance.now();
    
        var now = new Date();
        var dayofyear = moment(now).format("DDD");
        var year = moment(now).format("YYYY") - 1970;
        var offsetZone = moment().utcOffset(); <-----
    
        var table = $("#timescheduleparam").DataTable({
            ajax: $.fn.dataTable.pipeline( {
                url: Routing.generate('timescheduletable', {
                    "tollingsegmentid": $("#tollingsegmentid").val(), 
                    "daytypeid" : $("#daytypeid").val(), 
                    "dayofweektimeofdaygroupid": $("#dayofweektimeofdaygroupid").val() ,
                    "offsetZone" : offsetZone <-----
                    }),
                pages: 25
    

    In the "timescheduletableAction" function of the controller, I pick up the Offset:

    $offsetZone = $request->get("offsetZone");
    

    And with that offset, I pick up all the array that the repository returns to me, where the DQL that makes the query from the page is included. I modify the values of the column "starttime" and "endtime", and I reorder all the array taking into account "starttime" as the last value to order in ascending order.

    //Obtain the offset in minutes (1 Hour = 60 minutes) and multiply it by seconds that have one minute
            $offsetZone = $offsetZone*60;
    
            // I add 5 hours difference between Houston and UTC
            $offsetZone = $offsetZone + (5*60*60);
    
            //Modify the "Starttime" and "Endttime" columns to adjust them with offset to the visiting client's time zone.
            //86400 = Seconds in 24 hours.
            for ($i = 0; $i < count($data); $i++){
                $data[$i]['starttime'] = $data[$i]['starttime'] + $offsetZone;
                if ($data[$i]['starttime'] >= 86400) {
                    $data[$i]['starttime'] = $data[$i]['starttime'] - 86400;
                } elseif ($data[$i]['starttime'] < 0) {
                    $data[$i]['starttime'] =$data[$i]['starttime'] + 86400;
                }
                $data[$i]['endtime'] = $data[$i]['endtime'] + $offsetZone;
                if ($data[$i]['endtime'] >= 86400) {
                    $data[$i]['endtime'] = $data[$i]['endtime'] - 86400;
                } elseif ($data[$i]['endtime'] < 0) {
                    $data[$i]['endtime'] =$data[$i]['endtime'] + 86400;
                }
            }
    
            // We rearranged the entire array:
            //Re-sort the data of the array as it is and pass it to an array_multisort
            $sort = array();
            foreach($data as $k=>$v) {
                $sort['daydescription'][$k] = $v['daydescription'];
                $sort['description'][$k] = $v['description'];
                $sort['starttime'][$k] = $v['starttime'];
            }
            //Order by daydescription ASC, description ASC and starttime ASC
            array_multisort($sort['daydescription'], SORT_ASC, $sort['description'], SORT_ASC, $sort['starttime'], SORT_ASC, $data);
    

    Finally, I send the array back to the twig page to be painted by DataTables:

    $response = array("draw" => $draw, "recordsTotal" => $count, "recordsFiltered" => $count, "data" => $data);
    
    return new Response(json_encode($response));
    

    Works pretty well.

    I hope this idea can be useful for the next one who has a similar problem.
    Greetings to all! I'll be back with more challenges for sure. JAJAJA

This discussion has been closed.