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.
component persistent="true" {
property name="id" generator="native" sqltype="integer" fieldtype="id";
property name="firstname" ormtype="string";
property name="lastname" ormtype="string";
property name="email" ormtype="string";
property name="salary" ormtype="float";
}
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:
<cfparam name="url.start" default="1">
<cfparam name="url.sort" default="lastname">
<cfparam name="url.sortdir" default="asc">
<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:
<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:
<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:
<cfoutput>
<table border="1">
<tr>
<th>
<cfif url.sort neq "lastname">
<a href="list.cfm?start=1&sort=lastname">Name</a>
<cfelse>
<a href="list.cfm?start=1&sort=lastname&sortdir=#url.sortdir eq 'asc'?'desc':'asc'#">Name</a>
</cfif>
</th>
<th>
<cfif url.sort neq "email">
<a href="list.cfm?start=1&sort=email">Email</a>
<cfelse>
<a href="list.cfm?start=1&sort=email&sortdir=#url.sortdir eq 'asc'?'desc':'asc'#">Email</a>
</cfif>
</th>
<th>
<cfif url.sort neq "salary">
<a href="list.cfm?start=1&sort=salary">Salary</a>
<cfelse>
<a href="list.cfm?start=1&sort=salary&sortdir=#url.sortdir eq 'asc'?'desc':'asc'#">Salary</a>
</cfif>
</th>
</tr>
</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:
<cfloop index="person" array="#items#">
<cfoutput>
<tr>
<td>#person.getLastName()#, #person.getFirstName()#</td>
<td>#person.getEmail()#</td>
<td>#numberFormat(person.getSalary())#</td>
</tr>
</cfoutput>
</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:
<cfoutput>
<cfif url.start gt 1>
<a href="list.cfm?sort=#url.sort#&sortdir=#url.sortdir#&start=#url.start-pageSize#">Previous</a>
<cfelse>
Previous
</cfif>
/
<cfif url.start+pagesize lt total>
<a href="list.cfm?sort=#url.sort#&sortdir=#url.sortdir#&start=#url.start+pageSize#">Next</a>
<cfelse>
Next
</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.
Archived Comments
Does this ORM paging fix the problem with Maxrows in cfquery? Can ORM handle retrieving joins or subqueries?
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.
Bam: http://docs.jboss.org/hiber...
@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...
Thanks for sharing this Rupesh. I knew you had blogged it.
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.
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()>
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
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.
That's what I thought Ray, thanks for confirming it (pinch of salt confirmation at least).
Kind regards
Tom