openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tom Mutdosch <>
Subject Re: possible to write a JPA Query to that filters both an Entity and its relationship entities?
Date Fri, 23 Feb 2007 21:30:46 GMT
Thanks Craig,

I like this idea (as well as what Patrick mentioned).  It gets me close 
- this is probably an easy way to get the data I want in a way that I 
can then construct the output that I am looking for. 
Thanks for the suggestion. 

Craig L Russell wrote:
> How about
> select dept, oldtimers from Department dept LEFT JOIN 
> dept.employeeCollection oldtimers WHERE dept.deptno >= 100 AND  
> oldtimers.yearsOfService >= 15
> That should give you only departments that satisfy the deptno 
> constraint and since you are not using outer join, only departments 
> that also have at least one employee satisfying the yearsOfService 
> constraint.
> The results would have one element for each employee. Each result row 
> would contain the department and the employee. So you would have the 
> results:
> [looks better in monospace font]
> dept            oldtimers
> =============   ==============
> R&D             Larry
> R&D             Curly
> R&D             Moe
> Entertainment   Fred
> Entertainment   Ginger
> Craig
> 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
> Craig Russell
> Architect, Sun Java Enterprise System
> 408 276-5638
> P.S. A good JDO? O, Gasp!

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message