DataTables: Hoping to load the first 10 records for the user while other records render in the background

[markw707]

Hello. Not really a CF question per se. But I'm wondering if anyone knows how to load the first 10 records for the user while other records render in the background using DataTables. I've tried various things without luck. Here is the basic code. Thank you in advance for your help.

<!--- Table Definition --->

$(document).ready( function () {

$('#example')

.DataTable( {

responsive: true,

order: [ 1, "desc" ],

columnDefs: [

{ targets: [-2, -3, -4, -5], className: 'dt-body-center' },

{ targets: [-1], className: 'dt-body-right' }

],

"oLanguage": {

   "sSearch": "Filter: "

 },

 "iDisplayLength": 25

} );

});

<!--- Ajax Call --->

        $(document).ready( function () {

function showResult() {

        $.ajax({

  url: "myFile.cfm",

  method: "POST",

  data:$('#Frm1').serialize(),

  success: function(result){

        $('#response').html(result);

    }

  });

        }

      });

The code works to ajax the recordset into the page and put it into the DataTable correctly, but it is slow if the recordset is huge.

Thank you!

[Jim Priest]

Just to be clear -  you want to initially fetch 10 records and display them. Then when the user clicks the pagination button - you want to load the next 10 records?

Jim

--
Upcoming events: http://www.meetup.com/Triangle-Area-ColdFusion-Users-Group/
Follow us on Twitter: @tacfug
---
You received this message because you are subscribed to the Google Groups "TACFUG" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tacfug+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[markw707]

Hi Jim, Long time no see. I hadn't thought of it working like that, but yes, it could probably work that way. I was thinking of it more like the first ten records are rendered and displayed to the user in the DataTable, then the other records are rendered, so the user doesn't have to wait. As you may know, there is a time lag when DataTable converts the raw recordset into a DataTable, especially for large recordsets. Thanks again for your help.

To unsubscribe from this group and stop receiving emails from it, send an email to tacfug+un...@googlegroups.com.

[Denard Springle]

You would have to use serverside processing of the records and handle the pagination yourself, as Jim suggested, by returning only X records (in your case 10, but could be user selectable) to render per 'view' of the datatable. How you do that in SQL depends on if you're using MySQL or MSSQL and what version, but there's plenty of good articles on how to do pagination with both databases (and others) online so I'll skip that here.

Example JS code I use:

$('#entity-table').DataTable( {

'processing': true,

'serverSide': true,

'responsive': true,

'ajax': {

'url': '\link\to\cfm\cfc\or\controller\that\returns\json',

'type': 'POST'

},

"columns": [

{ "data": "columnName1" },

{ "data": "columnName2" },

{ "data": "columnName3" },

{ "data": "COLUMNNAME1" },

{ "data": "COLUMNNAME2" },

{ "data": "COLUMNNAME3" }

]

} );

I, peronally, loop over my returned queries and build an array of structs using struct[ 'columnName1' ] = qGetData.columnName1;, but you can also just have CF return an array of structs and remember (by default) CF returns column names in UPPERCASE and so serializeJSON() of a query will return UPPERCASE column names (so put both in my example).

serverside sends a POST (or GET if you set AJAX to GET, but don't) and you can dump out the form scope to see all their fields it sends in. The drawback to going serverside is that it requires you to handle everything (number of records returned, which page, search, single and multi-column sorting, etc.). The upside is datatables only renders data when it needs it. I do this a lot for tables of data that are encrypted in the database (users, for example) where large datasets would be horribly slow.

datatables in serverside mode expects JSON returned with the following data:

draw - this is the same as passed in by datatables and can be passed through (the number of 'rows' to 'draw' in the table)

recordsTotal - this is the total number of records

recordsFiltered - this is the number of records, out of the total records, that are filtered (by search, typically) - combines with recordsTotal to display 'Showing X number of records out of Y total' in the datatable.

data - this is your array of structs with column names matching those specified in your JS

These names *must* be in lowercase so use struct[ 'draw' ] = x, struct[ 'recordsTotal' ] = y, struct[ 'recordsFiltered' ] = z, and struct[ 'data' ] = [your array of structs] prior to serializeJSON() (or other rendering pipeline, e.g. fw/1 or ColdBox renderData())

See: https://datatables.net/examples/data_sources/server_side and https://datatables.net/manual/server-side for further information on using serverside.

HTH

-- Denny

[markw707]

Thank you. Very helpful. I will try it.