Hi,

I have a class customer that has a one-to-many relationship with class CustomerValue. Both classes have field for version. When I load customer values I want to load ONLY values with the latest version number of the CustomerValue.

So, my mapping for Customer looks like:

<hibernate-mapping>

<class name="Customer" table="customer">
<id name="Id" column="customer_id" type="long">
<generator class="native"/>
</id>


<property name="version" column="version" type="integer"/>


<bag name="values" inverse="true" lazy="true" order-by="version desc"
cascade="all-delete-orphan" where="version = (select max(value.version) from CustomerValue value)">
<key column="customer_id"/>
<index column="version"/>

<one-to-many class="CustomerValue"/>
</bag>



</class>
</hibernate-mapping>

Note the WHERE clause: where="version = (select max(value.version) from CustomerValue value)"

This however blowes up with a message that relation CustomerValue does not exist. I also tried to put the actual table name in the WHERE clause like this:

where="version = (select max(version) from customervariablevalue)"

, customervariablevalue is the name of DB table where customer values are stored.

This WHERE clause compiles but DOES NOT work -- it still returns all elements in the collection with all versions.

Please help to get this WHERE clause to return only customer values with the latest (or maximum) version number.

Thanks a lot
Alex

The second approach is correct, it is an SQL fragment, not a HQL fragment that is required.

What does the generated SQL look like?

The generated SQL looks like:

18:48:42,558 DEBUG [SessionFactoryImpl] prepared statement get: select customer0
_.variable_id as variable1___, customer0_.variable_id as variable_id, customer0_
.variable_value as variable2_, customer0_.version as version, customer0_.company
_variable_set_id as company_4_, customer0_.company_variable_set_group_id as comp
any_5_, customer0_.company_variable_id as company_6_, customer0_.customer_id as
customer7_ from customervariablevalue customer0_ where customer0_.customer_id=?
and customer0_.version = (select max(customer0_.version) from customervariablevalue) order by customer0_.version desc

I converted this to something more readable like:

select * from customervariablevalue customer_0 where customer_0.customer_id = 1 and
customer_0.version = (select max(customer_0.version) from customervariablevalue)

AND It DOES have a problem -- it returns all elements. But if you correct the table alias in the subquery to customer_0 and get this:

select * from customervariablevalue customer_0 where customer_0.customer_id = 1 and
customer_0.version = (select max(customer_0.version) from customervariablevalue customer_0)

THEN it works correctly and returns the max version. So, now I know what the problem is but how do I fix it? I do not want to guess an alias name in my mapping file. Is there a way to tell Hibernate not to use alias?

Thanks a lot
Alex

Use:




should add this to doco somewhere

This worked great! Thanks. But as I turned out the select itself is not completely correct. I need to see a max version not just across the whole table but for THIS customer so my where should look something like:

where="version = (select max(cv.version) from customervariablevalue cv where CUSTOMER_ID = 3577 )"

I am showing cutstomer id number as an example -- it actually has to be dynamic -- but how can I do that in Hybernate? Can you put a dynamic parameter into the WHERE clause.

Thanks
Alex

Doesn't this work:

No actually, the genarated SQL becomes something like:

select * from customeroffer customer0 where customer0.customer_id = 3577 and
version = (select max(cv.version) from customeroffer cv where cv.customer_id = customer_id)

(never mind customeroffer -- it is the same thing just a different table) because the alias is problem again. Hmm is there a way to specify the name if the alias -- maybe a WHERE clause should be a separate XML element in a mapping file like:

<where clause="version = (select max(cv.version) from customervariablevalue cv where cv.customer_id = myalias.customer_id)" aliashint="myalias" >

Just a thought. Can we make it work any other way?

Thanks a lot -- I appreciate your help
Alex

Are you sure??

I believe it would be:



are you certain you actually ran this?

Sorry -- my fault. You are right -- the correct alias is prepended. I am all set. Thanks again
Alex