Hibernate version: 2.1

Mappings :

I have two tables: product table with primary key ID column (and a bunch of other properties). Another table has price rates for products,
it's a many-to-many relationship table with properties
(product_id,price_type_id,price)
It's mapped inside product hbm files as follows:
<map name="priceRates" table="RATES" lazy="false" cascade="all">
<key column="PRODUCT_ID"></key>
<index column="PRICE_TYPE_ID" type="int" />
<element column="PRICE" type="double" unique="false"/>
</map>

I use criteria api to do sorting on most properties of the product that live in the product table (name, etc.)
But now I need to be able to sort products by price depending on price type.
Example:
Product 1 has $1 for price type 1, $2 for price type 2
Product 2 has $2 for price type 1, $1 for price type 2
Product 3 has $3 for price type 1, $3 for price type 2


So if user is to sort (asc) by price type 1, the result should be
1,2,3
and if sort is by price type 2, the result should be
2,1,3
Any ideas on how I can achieve this?

Any ideas how I can achieve this?

Ideas:
#1 implement custom comparator and sort collection in your code;
#2 use SQL;

try with case expression (see chapter 14.9 in documentation)

I'd like to see more about it, however I cannot find chapter 14.9
in either hibernate 3 or hibernate 2 documentation

--------------------------------------------------------------------------------

Ideas:

This is not a practical approach since I need to use paging (retrieve
25 rather large objects instead of 10s of thousands.)



I am exploring this, but it kind of defeats the whole purpose of
using hibernate.
JeffP

Not really IMO. ORM deals with object mapping and operations when select is somehow edge case. When we do selects we often do not want objects, but some subset of information about those object to make a decision and then proceed with some operations on chosen object(s). Therefore I tend to see selects as data sets, not object sets.
Not sure if that is the case for you.

Anyway, you may explore SQL support in Hibernate, it will do mapping from resultset to objects if you need to
http://www.hibernate.org/hib_docs/v3/re ... rysql.html

i see 3.1rc1 documentation
this is chapter 'Expression' - there isn't too many information - only hql recognize case clause
you have to try it

case exists in standard sql you can see in postgresql documentation for example

I don't sure can you use case in order by - you have to try

Ok, I managed to express my query in SQL, and I am using
hibernate createSQLQuery to execute it.

However, I lose a lot in performance, because with criteria
I could specify fetchMode(EAGER), and with native sql
a query runs for each object that needs attributes
loaded from another table. If I need 25 results per page,
it executes 26 queries instead of 2 with criteria api.

So... my original question still stands. Can what I need be
done using Criteria?

i don't beleive that you can do it with criteria, but you can try with hql (no native sql) and hql
can be only quicker than criteria - you can set FetchMode u hql