I have a question about JPA Queries containing relationships and a WHERE filter. I basically want to have a JSF page containing a datatable that shows a list of Departments - each Department row shows relevant columns (department name, ID, etc) as well as a nested list of that Department's Employees with 15 or more years of service.
I was wondering if there was any way to create a JPA query that would return me such a result using just a single query. From what I have heard, I don't believe this is possible but thought I'd ask here just to be sure. As far as I know, JPA will just return me the Departments that match that query and each Department will contain every Employee that belongs to it.
Background: I have a DEPARTMENT entity with a one-to-many relationship to an EMPLOYEE entity. On my web page, I want to display "departments with a department number >= 100 and containing EMPLOYEES with more than 15 years of service".
Using SDOs I was able to write a query that would return a List of results which I would then bind to my JSF datatable. This list would contain Departments, and the Departments would have the Employees that matched that criteria. To do something similar in JPA, I think I now need to do n + 1 queries. That is, one query to retrieve the departments that I want to display, and then for each department another query to get the filtered list of its Employees.
In summary, I basically want to do something like:
select d from Department d LEFT JOIN d.employeeCollection e WHERE d.deptno >= 100 AND e.yearsOfService >= 15
But I think I need to do two separate queries. One to get the departments:
select d from Department d WHERE d.deptno >= 100
Then for each Department returned in that result set, a separate query to get the employees I'm after:
select e from Employee e where e.department.deptno = d.deptno and
e.yearsOfService >= 15
Does this sound like the right/only way to do this?