openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tom Mutdosch <>
Subject possible to write a JPA Query to that filters both an Entity and its relationship entities?
Date Thu, 22 Feb 2007 20:21:19 GMT
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?

View raw message