JPA left join to find unused entries

Question

I'm sure I'm being stupid but I can't seem to figure this one out...

I have two tables:

department( did, name ) employee( eid, first, last, did )

they have corresponding entities JPA managed entites Department and Employee. Employee has a Deparment field, Department doesn't maintain an Employee list. What I want to do though is find all the Departments that have no Employees. Using plain old SQL this is easy with a left join:

SELECT d.* 
FROM department as d LEFT OUTER JOIN employee as e
ON d.did = e.did
WHERE e.did IS NULL

I can't see how to translate this query into JPQL though. All the examples I've found for JPQL left joins traverse the link the other way, for example.

SELECT e FROM Employee e LEFT JOIN e.departmert d

Whereas I need something more like

SELECT d FROM Department d LEFT JOIN d.???? WHERE e.department IS NULL

but the department doesn't maintain a reference to it's employees (in my application it's not departments and employees obviously). Is this even possible in JPQL?

Answer 1

To do what you are trying to do, you would need to setup a mapping from Departments -> Employees (using your example entities). You could used the mappedBy attribute of @OneToMany, which will most likely not disrupt your schema, e.g.,

@Entity
class Department {
    ...
    @OneToMany(mappedBy="employee")
    Collection<Employee> getEmployees() {
        ....
    }
    ...
}

This would allow you to run something like:

SELECT d FROM Department d WHERE d.employees IS EMPTY

Which should give you equivalent results.

Without altering your mapping, you should be able to run something like this query to get the results you want:

SELECT d from Department d WHERE NOT EXIST (SELECT e FROM Employee e where e.department = d)