Hi there,
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?
Thanks,
Tom
