Rebind jQuery Tablesorter


#1

Hi,
I have a CFDIV that contains a html table with a Jquery plugin called “Table sorter”
Upon 1st load the table sorter doesn’t initialize as the document.ready$ function has fired before the Cfdiv has loaded the table.

Ok so if I create a test button and run the code to call the plugin.
i:e:

$("#datatable2").tablesorter();

all works fine, however I can seem to find a place or event on my page to initialize this automatically after the cfdiv has rendered the table.

I have tried Ajax.Complete & Ajax.Stop and they both run before the CFDiv has finished.

Any help?


#2

I don’t have a solution for cfdiv to offer you, as I’ve not used any of the built-in UI bits from CFML in years. I do, however, want to recommend an alternative strategy altogether…

https://datatables.net/

Specifically the server-side options: https://datatables.net/examples/server_side/

This way you can eliminate the cfdiv altogether, return data to your front end in JSON format and have all the options you need for sorting, etc. built-in to datatables so you don’t need tablesorter at all.

If you’re using either fw/1 or ColdBox you can easily return the data you need to populate the table with renderData( 'json', [data] ) in either framework.

If not using a framework then you can simply return the results of serializeJSON( [data] ) instead.

HTH

– Denny


#3

thanks for this Denny.
I must admit I am struggling with datatables and cannot get the correct format from my CFC.
I have tried this CFC:


#4

got it sorted, had to specify ajax DATA node…
sorry & thanks for help…


#5

Ah, yeah I suppose I could have provided an example :stuck_out_tongue_winking_eye:

Datatables requires a struct of data (the metadata, as it were) within which is an array of structs (the actual table data to render) is placed, so you’ll need to do a couple of things.

First, you’ll need to convert your query to an array of structs - The format of JSON returned by serializing a query directly is not one datatables will be able to cope with (and it just sucks as JSON anyway IMHO). EDIT: I stand corrected then if datatables can handle the format returned by CFML when serializing a query (honestly, I didn’t think it could lol). You can find a function to do this on Ben Nadel’s blog (https://www.bennadel.com/blog/124-ask-ben-converting-a-query-to-an-array.htm). Note that this code maintains UPPERCASE column names so you’ll have to account for that in your JavaScript. I, personally, just loop through the query and build my own array of structs, something like:

var returnStruct = {};
var returnArray = [];
var ix = 0;

for( var row in query ) {

    ix++;

    var rowStruct = {};

    rowStruct[ 'column1' ] = row.column1;
    rowStruct[ 'column2' ] = row.column2;
    etc.

    returnArray[ ix ] = rowStruct;

}

Then you add your array of structs to the returnStruct with the additional fields datatables requires in the JSON:

returnStruct[ 'draw' ] = form.draw; // comes from datatables when making the request and must be returned
returnStruct[ 'recordsTotal' ] = totalRecords // this is the total records available (before filtering with search, etc.)
returnStruct[ 'recordsFiltered' ] = query.recordCount; // total filtered records
returnStruct[ 'data' ] = returnArray; // this is the actual table data as an array of structs

Then you can serializeJson( returnStruct ) to return the data to datatables.

In the view (where datatables will live) you need to tell datatables you want to use processing and serverside options, along with the columns it should expect in the order it should expect them. For example:

$('#my-table').DataTable( {
    'processing': true,
    'serverSide': true,
    'ajax': {
        'url': '[URL to CFC to get the JSON data]',
        'type': 'POST'
    },
    'columns': [
        { 'data': 'column1' },
        { 'data': 'column2' }
    ]
} );

That’s the basics - you can get more elaborate with it beyond that, of course. Dump out the FORM scope when datatables makes a request to see how it formats the data being sent in (sorting, search, etc.) so you can take appropriate action (sorting the query using ORDER BY based on the column(s) the user chooses to sort on, for example).

For example:

FORM[ 'search[value]' ] is how the above code will present any value being searched for
FORM[ 'order[0][dir]' ] is the order (asc or desc) for the first column being sorted in the table
FORM[ 'order[0][column]' ] is the first column number (zero based) in the table being sorted to sort on

HTH

– Denny

P.S. Since you’re using Lucee, you can also eliminate the array indexing by using the append() member function, for example:

for( var row in query ) {

    var rowStruct = {};

    rowStruct[ 'column1' ] = row.column1;
    rowStruct[ 'column2' ] = row.column2;
    etc.

    returnArray.append( rowStruct );

}

#6

Thanks so much, I think I know why I have confused things.
I was testing this on a local CF instance, 2016 version and the cfquery has an attribute ReturnFormat=JSON.
I guess lucee doesn’t support this, this is why my data table actually works when that is specified.
I can use your examples to get it done anyway in lucee so thanks a lot.

I just hadn’t got around to redoing my server to lucee but I will as that will be the deployment one…

Thanks


#7

Since you’re using Lucee you can convert the Query to and Array of Structs like so:

retArray = [];
for (var row in query)
  retArray.append(row);

Example:


#8

Thanks so Much, I am converting to Lucee as we speak…


#9

Yup, that works too if you’re fine with column names being UPPERCASE by default (unless set otherwise in the admin, ofc). :slight_smile:


#10

UPPERCASE yuck!@# :astonished: (can’t find the throw-up emoji). That’s the first thing I change after setting up a new Lucee server.

In Lucee 5.1 or later you can set the Preserve Case with a Java property:

-Dlucee.preserve.case=true

Or with an Environment variable:

set LUCEE_PRESERVE_CASE=true

or

export LUCEE_PRESERVE_CASE=true

depending on your OS.

I personally would love to see the Preserve Case set to true by default.

See https://luceeserver.atlassian.net/browse/LDEV-996


#11

Hi all,
just to update everone on this tip for using jQuery DataTables.
I probably confused things with this post but Lucee can simply service up data to datatables without any other code than this:

> <!--- Get Customers DT --->
> <cffunction name="GetCustomersDT" access="remote" returntype="Any" returnformat="JSON" >
>   <cfquery name="QryCustomersDT" datasource="advancesoftware">
>     SELECT ID,Customer_Name,Status
>     FROM tb_Customer
>     </cfquery>
>   <cfreturn (QryCustomersDT)>
> </cffunction>

Just watch out for the calling Jquery code to include the node.

and that’s it…
Just a handful of lines…


#12

or just use returnType="array" to natively get an array of structs from a Lucee query…


#13

In my particular case the code I pulled my example from had a requirement to a) not have to change any of the Lucee defaults in the admin for deployment purposes, b) also be compatible with ACF and c) use lowercase column names in the returned JSON… so I ended up rolling my own array of structs to get the preferred format and compatibility.

But yeah, in general I’d use returnType if I didn’t have those constraints.


#14

I am migrating a database from MS SQL Server to Postgres and while mapping the data types to generate DDL scripts I was reminded of this thread, so sharing here how I’m doing that part using query returnType="array", arrayMap(), and the Elvis operator ?::

function camelToSnakeCase(input){
	return lCase(REReplace(input, "([^_A-Z])([A-Z])", "\1_\2", "all"));
}

typeMapping = {         // incomplete list, only the types I had in the source db
	 "__"               : ""
//	,"bigint"           : ""
	,"bit"              : "boolean"
//	,"char"             : ""
//	,"date"             : ""
	,"datetime"         : "timestamp"
	,"float"            : "real"
//	,"int"              : ""
	,"money"            : "numeric"
	,"ntext"            : "text"
	,"nvarchar"         : "text"
//	,"real"             : ""
	,"smalldatetime"    : "timestamp"
	,"smallint"         : "int"
	,"smallmoney"       : "numeric"
//	,"text"             : ""
	,"tinyint"          : "int"
	,"uniqueidentifier" : "text"
	,"varchar"          : "text"
}

query name="arrColumns" returnType="array" { echo("
	SELECT 	 C.TABLE_SCHEMA
			,C.TABLE_NAME
			,C.COLUMN_NAME
			,C.IS_NULLABLE
			,C.DATA_TYPE
			,C.CHARACTER_MAXIMUM_LENGTH
			,C.NUMERIC_PRECISION
			,C.NUMERIC_PRECISION_RADIX
	FROM 	INFORMATION_SCHEMA.COLUMNS C
		JOIN INFORMATION_SCHEMA.TABLES T ON
				T.TABLE_SCHEMA  = C.TABLE_SCHEMA
			AND T.TABLE_NAME    = C.TABLE_NAME
			AND T.TABLE_CATALOG = C.TABLE_CATALOG
	WHERE 	    T.TABLE_TYPE  = 'BASE TABLE'
			AND T.TABLE_NAME != 'dtproperties'
	ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, C.ORDINAL_POSITION;
"); }

arrColumns = arrColumns.map(
	function(el){
		el.PG_DATA_TYPE   = typeMapping[el.DATA_TYPE] ?: el.DATA_TYPE;
		el.PG_TABLE_NAME  = camelToSnakeCase(el.TABLE_NAME);
		el.PG_COLUMN_NAME = camelToSnakeCase(el.COLUMN_NAME);
		return el;
	}
);

Cfquery table name appears to be case sensitive (how to fix?)