Replacing CFGrid with DataTables, Part 1: Basic Display 6


If you follow a lot of ColdFusion blogs, you’ve probably seen the on-going movement to get CF developers to drop the use of the hideous UI elements that are part of ColdFusion, culminating in the launch of ColdFusion UI the Right Way.  At one time they were “useful” for folks first learning web stuff, but at this point they are hideously outdated, result in rather ugly/bloated looking code, and you can do so much better using a good library, like jQuery, Ext, MooTools, etc. Our shop dropped all UI tag usage many years ago.  Suffice to say, this is a movement I agree with wholeheartedly.

Anyway, a recent comment here asked for some aid in how to replace CFGRID usages with the jQuery plug-in DataTables.  There isn’t a chapter in the project yet on that, so I’ll be doing a couple of posts on this topic.  This one will cover replacing a regular ColdFusion query driven CFGRID for pure data display with a dataTable implementation.  I’ll look at more advanced things, like pulling via Ajax, in another post.  For these examples I’m using a simple set of data – a slightly dated list of the video games in my collection.

This is the query I’ll use for both examples, and before anyone asks about the seeming structure, I am querying a view that joins my tables to make these examples simpler :-P:

<cfquery name="qVideoGames" datasource="#variables.dsn#">
    SELECT videogameid, title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname
    FROM videogames
</cfquery>

Now, if we used CFGRID to present our table of data, here is what we might do. Again, for simplicity, I’m not going to go all nuts with the styling et all.


     
        
        
        
        
        
        
        
        
        
        
    

As someone who had not actually used CFGRID before, it took me a ridiculously long time to get a working example using the documentation – half the settings only apply to this format or the other. The default format, applet, didn’t work at all due to Java security errors.  The Flash one came out weird looking.  I finally settled on HTML format since it is closest to what we’ll do with dataTables anyway.  Also notice that even though I’m not doing a form, I HAVE to include the code in a CFFORM because its a requirement to use the tag.

Anyway, said code gives us:

CFGRID Basic Display Example?

As far as I could figured out from the docs, there is no way to tell the grid to paginate the results nicely for a “static” table (i.e. non-Ajax).  I had to manually set the widths on everything to get stuff looking decent and there seem to be very few options for prettying up the display.  You may notice that it is completely ignoring some of my settings – such as the strip color (though it is at least striping) and for some reason I have an extra column appearing that I can’t get rid of.  Googling didn’t yield any explanation for it. If you view source the resulting code…it’s ugly.  Pure ugly…but it gives us a tabular display of the data with sorting columns.

Now, how would I do this using DataTables? Again, for simplicity, I’m going to pretend I did this all in one file. In reality, I’d use a template for the site which would do the JS and CSS includes and all the head stuff, while the rest would be in the page view (i.e. modify this for your framework/coding schema).

So first, of course, download jQuery and DataTables if you haven’t already.  Then add the appropriate code to the head of the page to include both (you can also put the JS includes just above the </body> tag but again, being simple here).


    Blah blah
    
    
    
    
    
    

Then in the body, we do the ColdFusion to build our table:


        
Title Date Acquired # Discs ESRB Publisher Genre Platform Series Name
#title# #DateFormat(dateacquired, "mm/dd/yy")# #num_discs# #esrb_rating# #publisher# #genre# #platform# #seriesname#

And finally add on the bit of JavaScript at the bottom that ties it all together. To really show the difference, I’m just going to do the most basic if initiation without configuring anything.

And magic:

Tabular data with DataTables

Notice the dataTables option not only has the sorting of the CFGRID, but also filtering options, pagination, much greater flexibility in the column contents, and that’s out of the box – in less time than the CFGRID!  Want to restyle everything, it’s a snap – just do whatever regular CSS and HTML you would to style the table.

Wait you say, what about the aligning you did in the first one? Or setting widths?  Well, as you can see, unlike CFGRID, DataTables auto width calculations actually work pretty well for the data we have.  However, if we did want to set the widths or change the alignments or do other styling, it’s easy to do.  You can either do it in the HTML itself (NOT best practice) or through the DataTables configuration using CSS and other options.

<script type="text/javascript">
    $(document).ready(function(){
        $('#gameList').dataTable({
            "bJQueryUI": true,
            "aoColumnDefs": [
                { "sWidth": 120, "sClass": "alignCenter", "aTargets": [ 1,3 ] },
                { "sWidth": 120, "sClass": "alignRight", "aTargets": [ 2 ] }
            ]
        });
    });
</script>

You can also have the dataTables match your jQueryUI stylings by setting the bJQueryUI option (as shown above) and updating your head to include jQueryUI, the desired theme (in this case, we’re using Redmond), and to switch the dataTables CSS to the themeroller one.

<head>
    <title>Blah blah</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <meta name="description" content="Blahdy blah blah" />
    <meta name="robots" content="index, follow" />
    <link type="text/css" href="/assets/libraries/dataTables-1.9.4/css/jquery.dataTables_themeroller.css" rel="stylesheet" />
    <link type="text/css" href="/assets/themes/redmond-1-10-2/jquery-ui-1.10.2.custom.min.css" rel="stylesheet" />
    <script type="text/javascript" src="/assets/js/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" src="/assets/js/jquery-ui-1.10.2.min.js"></script>
    <script type="text/javascript" src="/assets/libraries/dataTables-1.9.4/jquery.dataTables.min.js"></script>
</head>

And then our table gets even nicer!

Can you do much of the same with CFGRID? Yes, with a lot more coding in both ColdFusion and JS…which then means it saved you how much time & code, exactly? But this is sorting, filtering/searching, and pagination with DataTables out of the box with a single line of JS.  And that’s without even getting into all the configuration options DataTables offers, much less expanding it’s feature set with plug-ins.

In the next post on this topic, I’ll look at doing these types of data with Ajax driven data – ideal when dealing with 1000s of records.

  • Cutter

    I wrote a similar article a few years back, including a plugin for handling native CF query return data. It’s probably a little dated, but still relavent http://www.cutterscrossing.com/index.cfm/2011/2/7/Using-The-DataTables-JQuery-Plugin

    • Cool. Your example is a bit more complex than what I’ll be aiming for here for the Ajax side, but will be helpful for those who mostly do CFSCRIPT rather than CFML 🙂

      And yeah, three years back would be a little dated…it is from quite a few jQuery and DataTables versions ago 😉

  • mandy

    Try to make this work with my application, do you mind share with me your database structure?

    • For this example, it is essentially just a table called videogames with 9 columns videogameid (INT/SERIAL), title (VARCHAR), dateacquired (DATE), num_discs (SMALLINT), esrb_rating (VARCHAR), publisher (VARCHAR), genre (VARCHAR), platform (VARCHAR), seriesname (VARCHAR)

  • I’m using cfgrid atm but I might try this out. I do have a question, is it possible to put 2 database columns in 1 cfgridcolumn? for example, say you are a chef in one of your games. You want to list, your char name, your craft and what level/proficiency it is like this
    Game Name Chef: 90k

    In a table the craft and the proficiency would be 2 different columns. How could you combine them so they show up in one column in a cfgrid?

    • The only way I know of would be to combine the two fields you need in the database query using a concat statement. So it would be something like SELECT CONCAT(videogamename, ” “, craft, “: “, currentlevel) as displayname