Hibernate Criteria Order By

Question

I have a table called Gift, which has a one-to-many relationship to a table called ClickThrough - which indicates how many times that particular Gift has been clicked. I need to query for all of the Gift objects, ordered by ClickThrough count. I do not need the ClickThrough count returned, as I don't need anything done with it, I just want to use it for purposes of ordering.

I need the query to return a List of Gift objects directly, just ordered by ClickThrough count. How do I do this using the Criteria API? I can find a lot of documentation out there on similar information to this, but nothing quite like what I need.

Zuckerman

Could you post your Entities for the classes? – Cassareep

Answer 1

If you want to return a list of entities or properties in combination with a count you will need a group by. In criteria this is done through ProjectionList and Projections. e.g.

    final ProjectionList props = Projections.projectionList();
    props.add(Projections.groupProperty("giftID"));
    props.add(Projections.groupProperty("giftName"));
    props.add(Projections.count("giftID"), "count"); //create an alias for the count
    crit.setProjection(props);

    crit.add(Order.desc("count")); //use the count alias to order by

However, since you're using ProjectionList you will also need to use AliasToBeanConstructorResultTransformer.

Mane

I haven't had a chance to try it yet, but this certainly sounds reasonable. I'll report back once I get a chance to try it out. Thanks! – Zuckerman

I tried it out, and I get a "PropertyNotFoundException: Could not find setter for count", I am guessing because "count" is not mapped in my Gift object. I was hoping that AliasToBeanConstructorResultTransformer would ignore anything it couldn't find a setter for, but I guess not. – Zuckerman

No, a setter for count will be required. The cleanest way would be the create a class GiftView which only has those properties of Gift that you actually need + count. This is then more like a DTO then an entity. Or you could add the count as a transient property of Gift. – Mane

OK, so I added a setter for count, and I am now making some headway. I was getting back a list of Gift objects, but they were all empty except for the count attribute. Turns out that instead of props.add(Projections.groupProperty("giftID")) I had to do props.add(Projections.groupProperty("giftID"), "giftID") to make that transformer work. – Zuckerman

However, now it only retrieves the attributes that I include in the projections list. That is fine except for the collection attributes for any 1-* relationships from gift. Those all now get set with an empty Set object, and lazy instantiation does not occur. How do I get around this? – Zuckerman

Lazy loading means that hibernate will execute separate queries to load the 1-* relationship entities (when needed). So if you would simply get those entities yourself (getBlaBlaBlaForGift) there's no performance cost. However, it does make things a bit more complicated (performance is not always all that matters). Another approach all together might be simply loading all your gifts (with the 1-*) and afterwards (per gift) get the count of ClickThoughs per gift and set this as a @Transient property of the gift. – Mane

Answer 2

Note for anyone else that comes through here looking to order by a property/column:

When using the approaches mentioned here, no results were found. The fix was to use criteria.addOrder(Order.asc(property)); instead. Notice the difference is to use addOrder, rather than add;

I've had this issue several times after running here for a quick reference.

Cohbert

This worked for me. The accepted answer would require you to cast Order.desc to Criterion which didn't make sense. – Pricefixing

Answer 3

You have a one-to-many relationship from Gift to ClickThrough so I assume each ClickThrough is a record with some datetime or other information associated with it. In this case, I would add a "count" field to your mapping file and attach the ordering to the criterion:

criterion.add(Order.asc(count)))

The mapping property looks something like:

<property name="count" type="integer" formula="(select count(*) from Gift g inner join ClickThrough ct on ct.gift_id=g.id where g.id=ID)"/>

If you can't or don't want to change the mapping file, you could use Collections.sort() with a Comparator though it seems less efficient having to return so much data from the DB.

Sibella