This was fine and dandy for a first version, but now I want to be able
to get this list of beers according to my many-to-many relationship,
and I'd like to be able to change the sorting on the fly, and I would
also like to pass start and maxRows for pagination purposes.
Any ideas how I can do this? Is my only option generating a big ol'
dynamic HQL statement something like:
"FROM Beer JOIN User ON (Beer.beerID = myBeers.beerID AND User.userID
= myBeers.userID) ORDER BY beerStyle desc"
Personally I like using the Criteria, especially for parametrized searching.
I've heard that Criteria queries are less performant then HQL, but unless I
ran into a specific problem I personally prefer the interface to writing
HQL. You will have to do some javacasting unfortunately, unless you use
cfgroovy or native java outside cf.
something like this (property names are clearly made up I don't know your
model):
restriction = createObject('java',"org.hibernate.criterion.Restrictions");
order = createObject('java',"org.hibernate.criterion.Order");
criteria = ormGetSession().createCriteria('Event');
> This was fine and dandy for a first version, but now I want to be able
> to get this list of beers according to my many-to-many relationship,
> and I'd like to be able to change the sorting on the fly, and I would
> also like to pass start and maxRows for pagination purposes.
> Any ideas how I can do this? Is my only option generating a big ol'
> dynamic HQL statement something like:
> "FROM Beer JOIN User ON (Beer.beerID = myBeers.beerID AND User.userID
> = myBeers.userID) ORDER BY beerStyle desc"
Cool stuff, Jon. I have another couple of suggestions as well:
1. If using HQL, you can avoid the manual join by using "member of", like
so:
"select b from Beer b, User u where u.id = :UserId and b member of
u.myFavorites order by b.beerStyle desc"
2. You could use a collection filter. These are not supported via CF
integration, but you can create them yourself using the Hibernate session
(similar to what Jon did with the Criteria object). Here's an example that
I think will give you what you want:
User = entityLoadByPK("User",1);
filter = ormGetSession().createFilter(User.getmyFavorites(),"order by
this.beerStyle desc").setFirstResult(0).setMaxResults(20);
beersArray = filter.list();
One issue with this is that you need to have a persistent User object first
(hence the first line call to entityLoadByPK). The criteria approach might
work better if you just want to run a query, without having a User object
already, and it also allows you to isolate your order by criteria a bit
more. These filters are very cool, though, as they basically allow you to
run queries against your collection properties, so, for example, you could
paginate a collection, or change sort sequence, or subselect.
I'm going to write a post about them with a bit more detail, when I can find
the time. ;-)
Oh, one more thing. You misspelled favourite. ;-)
Cheers,
Bob
On Tue, Oct 20, 2009 at 10:44 AM, Jon Messer <sylvan.mes...@gmail.com>wrote:
> Personally I like using the Criteria, especially for parametrized
> searching. I've heard that Criteria queries are less performant then HQL,
> but unless I ran into a specific problem I personally prefer the interface
> to writing HQL. You will have to do some javacasting unfortunately, unless
> you use cfgroovy or native java outside cf.
> something like this (property names are clearly made up I don't know your
> model):
>> This was fine and dandy for a first version, but now I want to be able
>> to get this list of beers according to my many-to-many relationship,
>> and I'd like to be able to change the sorting on the fly, and I would
>> also like to pass start and maxRows for pagination purposes.
>> Any ideas how I can do this? Is my only option generating a big ol'
>> dynamic HQL statement something like:
>> "FROM Beer JOIN User ON (Beer.beerID = myBeers.beerID AND User.userID
>> = myBeers.userID) ORDER BY beerStyle desc"
Thanks for sharing that "member of" tip there. This is the second HQL "oddity" I've seen (I'm only calling it odd as I've never seen anything like it in SQL). I'd love to see a blog post on HQL "extensions" to normal SQL like this.
<bob.silverb...@gmail.com> wrote: > Cool stuff, Jon. I have another couple of suggestions as well:
> 1. If using HQL, you can avoid the manual join by using "member of", like > so:
> "select b from Beer b, User u where u.id = :UserId and b member of > u.myFavorites order by b.beerStyle desc"
> 2. You could use a collection filter. These are not supported via CF > integration, but you can create them yourself using the Hibernate session > (similar to what Jon did with the Criteria object). Here's an example that > I think will give you what you want:
On Tue, Oct 20, 2009 at 11:25 AM, Raymond Camden <rcam...@gmail.com> wrote:
> Thanks for sharing that "member of" tip there. This is the second HQL
> "oddity" I've seen (I'm only calling it odd as I've never seen
> anything like it in SQL). I'd love to see a blog post on HQL
> "extensions" to normal SQL like this.
> On Tue, Oct 20, 2009 at 9:55 AM, Bob Silverberg
> <bob.silverb...@gmail.com> wrote:
> > Cool stuff, Jon. I have another couple of suggestions as well:
> > 1. If using HQL, you can avoid the manual join by using "member of", like
> > so:
> > "select b from Beer b, User u where u.id = :UserId and b member of
> > u.myFavorites order by b.beerStyle desc"
> > 2. You could use a collection filter. These are not supported via CF
> > integration, but you can create them yourself using the Hibernate session
> > (similar to what Jon did with the Criteria object). Here's an example
> that
> > I think will give you what you want:
I know this thread is old but I just found the need to do this also. The 'member of' syntax is really cool and I was totally unaware of it. I'm using it now but has there been a blog post about it since?
