Replies

• colin Posts: 15,240Questions: 1Answers: 2,599

  April 2020

  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
• Manusori Posts: 9Questions: 0Answers: 0

  April 2020

  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.
• Manusori Posts: 9Questions: 0Answers: 0

  April 2020

  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.
• colin Posts: 15,240Questions: 1Answers: 2,599

  April 2020

  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
• Manusori Posts: 9Questions: 0Answers: 0

  April 2020

  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.
• colin Posts: 15,240Questions: 1Answers: 2,599

  April 2020

  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
• Manusori Posts: 9Questions: 0Answers: 0

  April 2020

  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!!
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 2020

  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
• Manusori Posts: 9Questions: 0Answers: 0

  April 2020

  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.
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 2020

  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
• Manusori Posts: 9Questions: 0Answers: 0

  May 2020

  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.
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  May 2020

  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
• Manusori Posts: 9Questions: 0Answers: 0

  May 2020

  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!
• Manusori Posts: 9Questions: 0Answers: 0

  June 2020

  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