CODEWITHSUNDEEP
sqlhibernatejoinhql
Chupacabras
Chupacabras

Reputation: 401

HQL equivalent for additional conditions in LEFT JOIN

I have hibernate entities:

Is there a way to make HQL equivalent for this query?

    select distinct p.id, p.lastAndFirstName
    from organizations o
    left join organization2person op on op.organization_id=o.id
    left join organization2privileged_person opp on opp.organization_id=o.id
    left join sys_persons p on p.id=op.person_id or p.id=opp.person_id
    order by p.lastAndFirstName

I don't know how to create condition p.id=op.person_id or p.id=opp.person_id in the last left join. I have seen WITH keyword, but it creates condition with "AND", not "OR".

Upvotes: 1

Views: 798

Answers (1)

carbontax
carbontax

Reputation: 2184

It could look something like this.

Based on the very scant details it sounds like you want subqueries. But you might consider reverse mapping these associations from the Person side. It might not be more efficient but it might make your life easier.

select p.id, p.lastAndFirstName from Person p
   where p.id in
   (select p2.id from Organization o 
        left join op.persons p2)
   or p.id in
   (select p3.id 
       left join o.privilegedPersons p3)

(PS. I can't figure out what you are trying to do with sys_persons)

Upvotes: 1

Related Questions