Hibernate conditional HQL join

Question

I am using Hibernate to map to a legacy data model that I am not able to change. For a particular query I'm trying to construct some HQL that uses a join condition that encompasses more than one property. I have a horribly contrived example that illustrates what I am trying to achieve:

Consider the class Consumer and its two properties - both of type Gadget:

@Table(name = "consumer")
class Consumer {
  @Column(name = "mp3_player")
  Gadget mp3Player;
  @Column(name = "mobile_phone")
  Gadget mobilePhone;
  ...

Notice that the consumer table has two foreign keys to the gadget table - not ideal - but it's what I have to work with. I want to obtain a list of Gadgets named 'iphone' that the Consumer 'bob' uses as either an MP3 player or Phone. With postgres I can write:

select gadget.*
from consumer join gadget on (
  consumer.mp3_player = gadget.id or consumer.mobile_phone = gadget.id
)
where consumer.name = 'bob' and gadget.name = 'iphone';

I have tried to express this query with HQL but it appears to demand that the join clause contain only one condition. So how can I express a similar query in HQL?

JB Nizet JB Nizet · Accepted Answer · 2012-03-15T17:42:11

select consumer from Consumer consumer
left join consumer.mp3Player mp3Player
left join consumer.mobilePhone mobilePhone
where consumer.name = 'Bob' 
and (mp3Player.name = 'iphone' or mobilePhone.name = 'iphone')

Hibernate conditional HQL join

1 Answers