I'm no JPA expert, so take my comments with a grain of salt.
How about searching the other direction:
select e from employee e where e.department.depno >= 100 &&
e.yearsOfService >= 15
Then assuming OpenJPA can do it, you could tune the query to grab the
department object along with the employee. If OpenJPA can't do that,
you will get at most 100+1 queries. Alternatively, you could try
executing this query before the one above:
select d from Department d WHERE d.deptno >= 100
And then rely on the OpenJPA cache to hook the departments to the
employees. Depending on the database vendor and the data in the
tables, executing two queries may be more efficient then one.
-dain
On Feb 22, 2007, at 12:21 PM, Tom Mutdosch wrote:
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
