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.
Tom
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 http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
