limiting size of query/result set

pdhaigh Oct 3, 2007 10:14 AM

Hi,

I'm trying to create a query to return me 3 random rows from a table. Obviously, I can set up my query like this:

<framework:entity-query name="randomPageImagesSmall">
 <framework:ejbql>from PageImage</framework:ejbql>
 <framework:order>RAND()</framework:order>
 </framework:entity-query>

and then just take the first 3 records.. but what I'd like to do is just return 3 rows (i.e. analagous to SQL: LIMIT 3), as this saves reading the whole lot from the DB..

any advice greatly appreciated.

cheers

phil

1. Re: limiting size of query/result set

trickyvail Oct 3, 2007 10:57 AM (in response to pdhaigh)
Could you try this?
<framework:entity-query name="randomPageImagesSmall" max-results="3" > <framework:ejbql>from PageImage</framework:ejbql> <framework:order>RAND()</framework:order> </framework:entity-query>
Actions
2. Re: limiting size of query/result set

pdhaigh Oct 3, 2007 11:05 AM (in response to pdhaigh)
ah excellent, thanks.

It doesn't really matter, but I noticed that this actually translates to LIMIT <maxResults+1>:

<framework:entity-query name="randomPageImagesSmall" max-results="3" > <framework:ejbql>from PageImage</framework:ejbql> <framework:order>RAND()</framework:order> </framework:entity-query>

=

order by rand() limit 4
Actions
3. Re: limiting size of query/result set

matt.drees Oct 3, 2007 1:41 PM (in response to pdhaigh)

It does that so that, if you're doing paging, you can tell if there's a next page or not (which is a cute trick, by the way). The getResultList() method will only have 3 results.
Actions

Go to original post