Grails left outer join query HQL or criteria challenge

Question

I am a newbie to hql and tried out several combinations that i could find but i seem unable to construct the correct HQL query for a left join.

I have the following domain model:

class Company {
    static hasMany = [employees: Employee]
}

class Employee {
    static belongsTo = [
            Company
    ]
}

So a employee does not know anything about the companies. Now i would like to create a hql query that gives met the employees without a company. In sql i have successfully created the query using a left join but i seem not to be able to create a criteria or hql query that gives me a correct result.

Any a clue or some tips on how i could achieve the result?

Answer 1

Here you go, this works:

Employee.executeQuery("""
Select e
from Employee e
where e not in (Select ce from Company c left join c.employees ce)
""")