Basic relationship sorting question

So, I have a relationship in one of my objects:

property name="myFavorites" fieldtype="many-to-many" CFC="Beer"
linktable="myBeers" FKColumn="userID" inversejoincolumn="beerID"
lazy="true" cascade="all" orderby="beerName";

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"

Shan

You basically have 3 options:

1) write pure sql and populate objects by id (ick)
2) HQL like what you list (a lot of text manipulation meh)
3) use the Criteria API http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html

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');

criteria.add( restriction.like("userName", "%Cool%") )
           .addOrder( order.desc('beerStyle') )
           .createCriteria('myFavorites')
           .add( restriction.eq("beerScore", javacast('double',"50") )
           .setMaxResults(50)
           .setFirstResult(1) );

arrayOfBeer = criteria.list();

The pagination might not work depending on your DB though.

 HTH

Jon

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--
Bob Silverberg
www.silverwareconsulting.com

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.

Thanks Ray.  I'll add it to the ever-growing list of things that I plan to blog about ;-)--
Bob Silverberg
www.silverwareconsulting.com

Hi Guys