hibernate - HQL joins on many clauses

Question

I've been reading Hibernate documentation, but I haven't found anything that would explain how to do the following.

I have the following SQL code that I'm trying to convert to HQL:

SELECT {msg.*}, {cmd.*} 
FROM Schema.Messages AS msg 
  LEFT OUTER JOIN schema.send_commands AS cmd 
    ON cmd.message_key = msg.unique_key 
    AND ( lower(cmd.status) IN (lower('failed') ) ) 
WHERE msg.sequence_received < 10";

The mainissue I'm having is that I'm unable to have two clauses on a LEFT OUTER JOIN. HQL allows me to have ON cmd.message_key = msg.unique_key , but how do I add the AND clause 2?

Answer 1

You can add extra join conditions using with keyword, something like this (depends on your mapping):

SELECT m, c 
FROM Message m LEFT JOIN m.commands c WITH (lower(c.status) = 'failed')
WHERE m.sequenceReceived < 10

See also:

• 16.3. Associations and joins