Select Top n with CF9 ORM

July 28, 2009

This is just a quick post for anyone who wants to select the top n records using hibernate. Hibernate HQL doesn't support the "top" keyword, luckily the guys at Adobe have thought of this and given us the maxresults argument. Here's how you use it:

HQL select top 5 records:


<!--- get 5 artists ordered by first name. Note HQL is case sensitive --->
<cfset artists = ORMExecuteQuery( 'from Artist order by firstname asc', false, {maxresults=5} ) />

EntityLoad top 5 records:


<!--- get 5 artists ordered by first name, note the second argument is a blank filter --->
<cfset artists = EntityLoad( 'Artist', {}, "firstname asc", {maxresults=5} ) />

OK, so that's pretty neat, now what if I want to do a bit of paging. Well, that's easy to!

HQL get 5 records at a time:


<!--- get 5 artists ordered by first name. Note HQL is case sensitive --->
<cfparam name="url.offset" default="1" />
<cfset artists = ORMExecuteQuery( 'from Artist order by firstname asc', false, {maxresults=5, offset=url.offset} ) />

EntityLoad get 5 records at a time:


<!--- get 5 artists ordered by first name, note the second argument is a blank filter --->
<cfparam name="url.offset" default="1" />
<cfset artists = EntityLoad( 'Artist', {}, "firstname asc", {maxresults=5, offset=url.offset} ) />

The offset argument simply tells ColdFusion which item you want to start from, so passing in url.offset=5, means you'll get records 5 to 10

4 comments

  1. I'm really curious. Have you looked behind the scenes to see what sort of query is actually executed? This seems like it could be a database killer if a large number of records is returned.

    Comment by Terry Schmitt – July 28, 2009
  2. Hi Terry, that's a great question and one I'd pondered myself!

    I set hibernate to log to a text file so that I could see what was happening and sure enough the SQL query gets all records, but only the first n records seem to be turned into objects.

    Rupesh Kumar has posted a great article on how to set up the logging if you want to try it yourself here:
    coldfused.blogspot.com/2009/07/coldfusion-orm-how-to-log-sql.html

    Comment by John Whish – July 28, 2009
  3. Great tips. The whole ORM stuff is something I've been slow to get into. Your posts have been making it look easy.

    Comment by Ben Nadel – July 28, 2009
  4. Hey Ben. Thanks!

    I think that the hibernate/ORM stuff has been really well implemented, and a lot of people will really like writing apps with it. Well, I do anyway :)

    Comment by John Whish – July 29, 2009

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.