Simple ColdFusion 9 ORM Paging Demo
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.
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:
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:
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:
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:
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:
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:
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.
HQL can do joins. Not sure on subqueries.
@Ray,For a quick ref, here is my post for sql logging :-)
http://www.rupeshk.org/blog/index.php/2009/07/cold...
@Rupesh, thanks for the info on subqueries. I'll have to give it a try.
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()>
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
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.
Kind regards
Tom