Answers

• crwdzr Posts: 31Questions: 5Answers: 6

  September 2017 edited September 2017

  I saw this the other day which was relevant:
  https://datatables.net/forums/discussion/44770/can-ajax-handle-5gb-json-file-which-contains-20-millions-of-records#latest

  also relevant:
  https://stackoverflow.com/questions/1262376/is-there-a-limit-on-how-much-json-can-hold

  The take-away from the second link is that both the server and browser may be limiting the size its going to allow you to handle
• bindrid Posts: 730Questions: 0Answers: 119

  September 2017

  It was not a limit set or intrinsic to JSON. It was a default setting on IIS that threw an exception after a certain size. We uppped the limit and were good to go
• allan Posts: 63,356Questions: 1Answers: 10,444 Site admin

  September 2017

  With that many rows, it would be worth enabling server-side processing. The Editor libraries you are using will automatically detect the server-side processing request and act on it.

  Allan
• rdm Posts: 194Questions: 55Answers: 4

  September 2017

  @Allan -- I tried serverSide: true yesterday. That by itself did not solve the problem. Since I don't actually want to present the entire table -- I want the editor to page the results as it normally does -- I don't see the need to send the entire database table to client side.

  So the question is why serverSide: true doesn't appear to work in my case.
• rdm Posts: 194Questions: 55Answers: 4

  September 2017

  @bindrid -- What technique did you use? I found several different approaches (using web.config and javascript.serialization settings in the controller action, and it didn't resolve the issue).
• rdm Posts: 194Questions: 55Answers: 4

  September 2017

  Just to make sure that I didn't have any errors on my view page, I artificially limited the size of the query results and I got no invalid Json errors. But I don't need to send the entire dataset over. I just need to send the requested page results. So maybe there's something in the response statement I need to add?

  var response = new Editor(db, "PlanningSubset", "Id")
                      .Field(new Field("FridayPlanningRosterMembersId"))
                      .Field(new Field("QuarterNumber"))
                      .Field(new Field("WeekNumber"))
                      .Where("QuarterNumber",1)
                      .Where("WeekNumber", 1)
                      .Process(formData)
                      .Data();
  

  Just in case there is a relevant clue in my jQuery code, I'm including that. You can see that I have even set paging as well as serverSide to true.

  <script>
          var editor;
          $(() => {
              editor = new $.fn.dataTable.Editor({
                  ajax: "@Url.Action("JoinedTableTest")",
                  table: "#example",
                  fields: [
                      { label: "FridayPlanningRosterMembersId", name: "FridayPlanningRosterMembersId" },
                      { label: "QuarterNumber", name: "QuarterNumber" },
                      { label: "WeekNumber", name: "WeekNumber" }
                  ]
              });
  
              $('#example').DataTable({
                  dom: "Bfrtip",
                  ajax: {
                      url: "@Url.Action("JoinedTableTest")",
                      data: { campus: "@Model.Campus" }
                  },
                  serverSide: true,
                  paging: true,
                  pageLength: 5,
                  columns: [
                      {
                          data: null,
                          defaultContent: '',
                          className: 'select-checkbox',
                          orderable: false
                      },
                      { data: "FridayPlanningRosterMembersId" },
                      { data: "QuarterNumber" },
                      { data: "WeekNumber" }
                  ],
                  select: {
                      style: 'os',
                      selector: 'td:first-child'
                  },
                  buttons: [
                      { extend: "create", editor: editor },
                      { extend: "edit", editor: editor },
                      { extend: "remove", editor: editor }
                  ]
              });
  
              // Activate an inline edit on click of a table cell
              $('#example').on('click',
                  'tbody td:not(:first-child)',
                  function (e) {
                      editor.inline(this);
                  });
          });
      </script>
  
• rdm Posts: 194Questions: 55Answers: 4

  September 2017 edited September 2017

  So I forgot to add declare type: "POST". I added that

  $('#example').DataTable({
                  dom: "Bfrtip",
                  ajax: {
                      url: "@Url.Action("JoinedTableTest")",
                      data: { campus: "@Model.Campus" },
                      type: "POST"
                  },
                  serverSide: true,
       [...]
  

  Now I'm getting a new error: Unknown field: (index 0)
• rdm Posts: 194Questions: 55Answers: 4

  September 2017

  This might be the culprit. The advice on this page is to make sure the checkbox is not orderable. That is still the case, but still does not resolve the problem.

  columns: [
                      {
                          data: null,
                          defaultContent: '',
                          className: 'select-checkbox',
                          orderable: false
                      },
  
• rdm Posts: 194Questions: 55Answers: 4

  September 2017

  Here's a mystery: when I move the checkbox column to the final column, I don't have any issues. That is a problem, however, because my users insist that the checkbox needs to be first because that's how they think.

  So I'm back to square one: how do I get serverside to work with a selection checkbox in the first column?
• allan Posts: 63,356Questions: 1Answers: 10,444 Site admin

  September 2017 Answer ✓

  Add order: [[ 1, 'asc' ]] to your initialisation.

  Even although you've marked the column as orderable: false, that actually for the end user's ability to order by that column only. So the default order of [[ 0, 'asc' ]] will cause issues.

  You'll also want to add columns.searchable and set it to be false for that column.

  Allan
• rdm Posts: 194Questions: 55Answers: 4

  September 2017

  @allan -- That did the trick. The matter is now resolved. Thank you