Posted in ColdFusion | Posted on 08-14-2009 | 5,720 views
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()>
[Add Comment] [Subscribe to Comments]