JQuery Datatables & ColdFusion - How do I get the array source data in that format?

Question

I'm following the example on this page to setup an Ajax source for JQuery Datatables. http://datatables.net/release-datatables/examples/ajax/objects.html

I can recreate this just fine if I start out with the source file just like their example array, in the following format.

{
  "aaData": [
    {
       "engine": "Trident",
       "browser": "Internet Explorer 4.0",
       "platform": "Win 95+",
       "version": "4",
       "grade": "X"
    },
    {
       "engine": "Trident",
       "browser": "Internet Explorer 5.0",
       "platform": "Win 95+",
       "version": "5",
       "grade": "C"
    }
  ]
 }

However, I'm using ColdFusion and I want my source data to come from a query. So, I followed another example about populating an array from a cfquery. http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec09f0b-7fea.html

That will put my sample data array in the following format.

RecordID: 2, Name: Al
RecordID: 1, Name: Mike
RecordID: 3, Name: Bill

What can I do to run a cfquery that will populate my source file in the proper format needed for the sAjaxSource attribute?

Adrian J. Moreno · Accepted Answer · 2014-04-14 17:10:01Z

4

I wrote a JSON converter specifically for this:

<cfset rs.dt = new DataTables() />
<cfset rs.dt.setData( yourQuery ) /> <!--- yourQuery = ColdFusion query --->
<cfset rs.dt.setDataFormat( "struct" ) />
<cfreturn rs.dt.$renderData() />

Converts your ColdFusion query into the jQuery DataTables format.

DataTables.cfc requires ArrayCollection.cfc, which is in the same repository.

answered Apr 14, 2014 at 17:10

Adrian J. Moreno

14.9k1 gold badge41 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HPWD Over a year ago

The KungFoo sample apps (there are two) are helpful resources to learn from, thanks for sharing.

HPWD Over a year ago

You even provided an apache-derby db and provide a link to a db viewer! Awesome-ness!

madvora · Accepted Answer · 2014-04-15 17:21:19Z

Thanks for the replies. I ended up using pieces of a some code I found online to get this to work.

This is my source table (DataSource.cfm)

<cfquery name="Query1" datasource="x">
    Select RecordID, NAME
    From TestTable
</cfquery>

<cfset data = [] />

<cfoutput query="Query1">
    <cfset obj = {
        "RecordID" = RecordID,
        "NAME" = Name
     } />
    <cfset arrayAppend(data, obj) />
</cfoutput>


<cfprocessingdirective suppresswhitespace="Yes">
    <cfoutput>
        {"aaData":
        #serializeJSON(data)#
        }
    </cfoutput>
</cfprocessingdirective>

<cfsetting enablecfoutputonly="No" showdebugoutput="No">

and this is the JQuery

<script>
$(document).ready(function() {
    var oTable = $('#example').dataTable( {
    "bJQueryUI": true,
        "bProcessing": true,
    "bServerSide": false,
    "sAjaxSource": "DataSource.cfm",
    "aoColumns": [
         { "mData": "RecordID" },
         { "mData": "NAME" }
    ]
         });
    });
 </script>

and here's the body of the page

<form>
        <div id ="div1" class="dataTables_wrapper">
            <table id="example">
                <thead>
                    <tr>
                        <th>RecordID</th>
                        <th>NAME</th>
                    </tr>
                </thead>
                <tbody>
                </tbody>
            </table>
        </div>
    </form>

nice job detailing this answer, thanks. Works perfectly. Now all I need to do is tweak it to make it an editable table.

Scott Stroz · Accepted Answer · 2014-04-11 15:10:44Z

2

Create a variable that is a structure (lets call it myVar).
Create a key in that structure named appData and set it to an empty array.
Loop over the query and inside the loop, create a structure (lets call it myStruct)
Still inside the loop, create keys for myStruct named engine, browser, etc. and populate these keys based on the relative data in your query row.
Still inside the loop, append myStruct to the myVar.appData array.
After the loop, myVar should have a structure similar to your example, if it does not, you made an error somewhere.

answered Apr 11, 2014 at 15:10

Scott Stroz

7,5192 gold badges23 silver badges26 bronze badges

Collectives™ on Stack Overflow

JQuery Datatables & ColdFusion - How do I get the array source data in that format?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related