Twitter: raymondcamden


Address: Lafayette, LA, USA

Simple ColdFusion 9 ORM Paging Demo

08-14-2009 9,073 views ColdFusion 10 Comments

I whipped up a quick demo today of how you can do simple paging with ColdFusion 9 ORM code. It isn't incredibly pretty, but hopefully it will be useful for folks to see an example. Let me begin with my model. Yesterday I was doing some speed test on CFC creation time with ORM entities. I made a slightly 'fat' CFC for the model. It isn't really related to this demo itself so I'm going to chop some of the lines out, but when you download the zip, don't be too surprised if it doesn't match exactly with the CFC you see here. Ok, so with that out of the day, here is a simple Person CFC I created for my model.

view plain print about
1component persistent="true" {
2    property name="id" generator="native" sqltype="integer" fieldtype="id";
3    property name="firstname" ormtype="string";
4    property name="lastname" ormtype="string";
5    property name="email" ormtype="string";
6    property name="salary" ormtype="float";    
7}

People are defined as having an ID, first and lastname, email, and salary. I whipped up a quick demo to insert a few (60,000+) records so I had some data to play with. I opened up my MySQL Query tool and confirmed I had a database full of people.

Now let's look at the paging code itself. When I write these up I typically begin with some simple parameters to handle the current start row, the sort, sort direction, and page size:

view plain print about
1<cfparam name="url.start" default="1">
2<cfparam name="url.sort" default="lastname">
3<cfparam name="url.sortdir" default="asc">
4
5<cfset pageSize = 10>

The sort and pageSize was totally arbitrary. Next up let's actually get our entities. ColdFusion 9's entityLoad function allows you to specify both a starting row and a max number of rows. Note though that entityLoad wants you to specify an offset. In other words - a 0 based index. Given that url.start=1 represents a 1 based index, I used simple subtraction to handle setting the offset:

view plain print about
1<cfset items = entityLoad("person", {},url.sort & " " & url.sortdir, {maxresults=pageSize,offset=url.start-1})>

Two things to note here. The second argument, the empty struct, is my filter. As I'm not filtering, I left it blank. Next - the CFML9 reference guide does not refer to the offset and has a type for maxresults. The CFML9 developer guide has the proper values and they are shown above.

The next problem is a bit more tricky. How do we get the total number of records? Without that we won't know how often to present a "Next" link in our pagination. This ended up being pretty obvious - using count. In MySQL I could do a simple select count(id), and the exact same SQL worked fine via HQL as well. HQL is Hibernate Query Language, and is perfect for things like this when there isn't a native function to get what you need:

view plain print about
1<cfset total = ormExecuteQuery("select count(id) from person", true)>

By the way, the true at the end tells the function to treat the result as a single entity. Normally I'd get an array of values, but when I pass true, then I just get a number.

And um... that's it. The rest is fairly standard (i.e. boring) HTML. I did do something a bit kind of fun in the header:

view plain print about
1<cfoutput>
2<table border="1">
3    <tr>
4        <th>
5            <cfif url.sort neq "lastname">
6                <a href="list.cfm?start=1&sort=lastname">Name</a>
7            <cfelse>
8                <a href="list.cfm?start=1&sort=lastname&sortdir=#url.sortdir eq 'asc'?'desc':'asc'#">Name</a>
9            </cfif>
10        </th>
11        <th>
12            <cfif url.sort neq "email">
13                <a href="list.cfm?start=1&sort=email">Email</a>
14            <cfelse>
15                <a href="list.cfm?start=1&sort=email&sortdir=#url.sortdir eq 'asc'?'desc':'asc'#">Email</a>
16            </cfif>
17        </th>
18        <th>
19            <cfif url.sort neq "salary">
20                <a href="list.cfm?start=1&sort=salary">Salary</a>
21            <cfelse>
22                <a href="list.cfm?start=1&sort=salary&sortdir=#url.sortdir eq 'asc'?'desc':'asc'#">Salary</a>
23            </cfif>
24        </th>
25    </tr>
26</cfoutput>

Notice the use of a ternary operator to handle "flipping" the sort direction. I used to write a simple UDF for that but ColdFusion 9 lets me do it a bit slicker.

Now that I've output my header, here is the display of the actual data itself:

view plain print about
1<cfloop index="person" array="#items#">
2    <cfoutput>
3        <tr>
4            <td>#person.getLastName()#, #person.getFirstName()#</td>
5            <td>#person.getEmail()#</td>
6            <td>#numberFormat(person.getSalary())#</td>
7        </tr>
8    </cfoutput>
9</cfloop>

I wish that was a bit more complex (joking), but I'll have to live with it as is. The rest of the file is no different than any other pagination code you have seen before. If we are past the first index, show a previous link, and then show a next link if we aren't on the last page:

view plain print about
1<cfoutput>
2<cfif url.start gt 1>
3    <a href="list.cfm?sort=#url.sort#&sortdir=#url.sortdir#&start=#url.start-pageSize#">Previous</a>
4<cfelse>
5    Previous
6</cfif>
7/
8<cfif url.start+pagesize lt total>
9    <a href="list.cfm?sort=#url.sort#&sortdir=#url.sortdir#&start=#url.start+pageSize#">Next</a>
10<cfelse>
11    Next
12</cfif>

I've attached the code to the zip for folks to play around with. I used a MySQL datasource but - obviously - it should work in any database supported by ColdFusion 9.

Download attached file

10 Comments

  • Susan #
    Commented on 08-14-2009 at 8:49 PM
    Does this ORM paging fix the problem with Maxrows in cfquery? Can ORM handle retrieving joins or subqueries?
  • Commented on 08-15-2009 at 6:15 AM
    I don't think it is a problem in cfquery. It is expected behavior (now that I've learned about it working at the JDBC level). But as to how the paging is done, I'm not sure. I need to get a tool to inspect the queries coming into MySQL - or start logging SQL via CF9 (which is possible, but I can't remember the setting yet - will blog it when I find it).

    HQL can do joins. Not sure on subqueries.
  • Commented on 08-15-2009 at 6:17 AM
    Bam: http://docs.jboss.org/hibernate/stable/core/refere...
  • Commented on 08-17-2009 at 2:07 AM
    @Susan, ORM does not use the driver's maxrows but generates the DB specific query for maxrows. So the filtering happens at the database level instead of the driver. Also, hql can handle joins as well as subqueries.

    @Ray,For a quick ref, here is my post for sql logging :-)
    http://www.rupeshk.org/blog/index.php/2009/07/cold...
  • Commented on 08-17-2009 at 4:56 PM
    Thanks for sharing this Rupesh. I knew you had blogged it.
  • Susan #
    Commented on 08-19-2009 at 7:04 AM
    Ray, thanks for the ORM demo. I can't wait until CF9 is out.

    @Rupesh, thanks for the info on subqueries. I'll have to give it a try.
  • Commented on 05-26-2010 at 6:17 AM
    Hi Ray,
    nice demo. Would be a nice feature if the cfc could return all entries. Instead of <cfset total = ormExecuteQuery("select count(id) from person", true)> there should be something like
    <cfset items = entityLoad("person", {},url.sort & " " & url.sortdir, {maxresults=pageSize,offset=url.start-1})>
    <cfset total= items.getCount()>
  • Commented on 10-16-2012 at 5:57 AM
    Hi Ray

    Sorry to bump this article from the grave but I was wondering if you could help with something kind of related.

    I have two tables, constructed via ORM property's and linked with one-to-many fieldtype.

    On my output I select the EntityLoad:

    <cfset exhibitorObj=EntityLoad("obj_Addon_EX_Exhibitors", ExID ,true)>

    Check to see if it has records belonging to it:

    <cfif exhibitorObj.hasContractsObj()>

    and then loop through those items

    <cfloop array="#exhibitorObj.getContractsObj()#" index="contract">

    The problem comes from trying to paginate these attached records as it simply pulls back everything ... which could be hundreds. Is there a simple way of only pulling back X amount of records or would I have to manually sort through the one-to-many array that is returned and pull back what I wish to show e.g 10 items per page?

    Hope you can help

    Kind regards

    Tom Jenkins
  • Commented on 10-16-2012 at 6:07 AM
    Please take this with a grain of salt. It has actually been a long time since I've worked in CF9 ORM so I'm a bit rusty.

    I checked the docs, and while we offer options for lazy loading and batching in properties, none of these do what you want.

    I think you would need to write a method on the CFC, like getMyContracts, that allows for a page number. The code behind this would need to use HQL to do the paging. Basically from contracts where owner=me.
  • Commented on 10-16-2012 at 6:53 AM
    That's what I thought Ray, thanks for confirming it (pinch of salt confirmation at least).

    Kind regards

    Tom

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty