Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
2.1, Oracle 9

I essentially have the Item and Bid relationship described in Hibernate in Action. I'm trying to return a [b]paged[b] set of Items ordered by the highest bid.

Because I'm paging, using a HashSet to remove non-distinct items after the query isn't an option. I need the HQL to make them distinct and then return the correct page of distinct objects.

Here's a working query that works, but doesn't return distinct Items.

select i from Item i
left join fetch s.bids bid
order by bid.amount

To make it distinct, I tried in the distinct keyword.

select distinct i from Item i
left join fetch s.bids bid
order by bid.amount

This however fails with the error (one of my "Bid" columns is a CLOB):
18:00:24,403 WARN JDBCExceptionReporter:38 - SQL Error: 932, SQLState: 42000
18:00:24,403 ERROR JDBCExceptionReporter:46 - ORA-00932: inconsistent datatypes: expected - got CLOB

I can't do the paging unless I can make distinct work (no point in paging non-distinct values). I'm assuming it's an issue with trying this against Orcale (saw a similar thread on DB2 issues). I really don't want to do the distinct and paging in Java though as that'd be woefully ineffecient compared to the database, not to mention retrieving way more data than I need.

Any ideas? A possible thought is only joining with the set of Bids that contain the highest bid for each Item, which would effectively make it distinct, but as of yet I can't figure out how to create that query.

There is no way to do what you are asking for using SQL. Think about it.

So use a LinkedHashSet, or a TreeSet with a comparator.

But there is still no way to get a constant page size. SQL just can't do it.

Thanks for the quick answer. I know the basics, but I'm not particularly SQL knowledgable so I didn't realize this wasn't possible (figured some form of subquery could do it).

I really want sorting/paging behind the scenes for performance reasons (sucking in bazillions of Item objects and sorting them in memory would be bad). Maybe I'll update Item with something like a "highestBidAmount" column. Denormalizes things somewhat, but it'd be worth it.

Well, it may be possible for some HQL queries if you use some extremely complex query with a subselect to choose the wanted pks, but I've never seen anyone write SQL queries like that in practice.

First get a list of collection of pairs (item and the biggest bid amount for each group of items with same id). The list is sorted by the biggest bid amount. So we have a sorted list of items with one extra column "bid.amount".

List tempItemList =
session().createQuery(
"select item, max(bid.amount) " +
"from Item item left outer join item.bids bid " +
"group by item.id, item.name, item.description, " +
"item.initialPrice, item.reservePrice, item.startDate, " +
"item.endDate, item.created " +
"order by max(bid.amount) desc")
.setFirstResult( startIndex )
.setMaxResults( size )
.list();

Next to cut off the extra column "bid.amount" from the list.

ArrayList <Item>itemList = new ArrayList<Item>();
for (int i=0; i<tempItemList.size(); i++) {
Object [] pair = (Object[]) tempItemList.get(i);
itemList.add((Item) pair[0]);
}

The itemList is the desired list of items sorted by the biggest bid amount.

In this way we do selection and sorting inside database, which is very efficient. We also keep the good Hibernate features of "setFirstResult( startIndex )", "setMaxResults( size )", etc.

One thing is worthy saying. I have to list all item's fields in the "GROUP BY" clause. If I could write "group by item order by..." which is similar to the "select item..." it would be much nicer.

I may be wrong but it should be a nice enhancement. I am using Hibernate version: 2.1.