hibernate sql query

Question

hi, i have a strange problem with a hibernate sql query:

The db relations are like follows:

registration has one invoicerecipient
registration has many attendees

i have the persid of an invoicerecipient, so I should get in both following cases the associated registration, but only the second case works. Does anybody know why the first case doesn't work?

select distinct registration from Registration registration, in(registration.attendees) atts where atts.id = :persid or registration.invoicerecipient.id = :persid

select distinct registration from Registration registration where registration.invoicerecipient.id = :persid

Answer 1

You can only use types in the from part. What is ", in(registration.attendees) atts" supposed to be?

The correct solution is to walk the structure from the object you have:

select distinct registration
from Registration registration
where registration.attendees.id = :persid
   or registration.invoicerecipient.id = :persid

Hibernate knows that registration.attendees is a collection, so it will generate the necessary subselect for you.

Answer 2

Hi,

I don't know whether Hibernate allows implicit collection reference in where clause according to Aaron Digulla's answer (registration.attendees.id). JPA specification does not allows it. It makes sense. registration.getAttendees().getId() is illegal in Java language.

But you can compare references according to:

select distinct registration from Registration registration, in(registration.attendees) atts where atts = :anotherAttendee or registration.invoicerecipient.id = :persid

Notice IN(registration.attendees) is similar to INNER JOIN registration.attendees. So a registration needs at least one attendee AND atts.id = :persid should share the same type as said by Aaron Digulla.

regards,