Hi Markus,
Thanks for the reply. That makes sense; I just wasn't sure if JPA was able to
more easily handle these more complex scenarios. I'll give you an example of
where I would want to do this:
I have a JSF page with a datatable showing a list of departments - each
department row shows relevant fields (department name, ID, etc) as well as a
nested list of Employees with 15 years of service. It would be nice if I could
just bind the result of a single JPA query to that datatable. That is, I could
just bind the list of Departments, and each one would have the appropriate list
of Employees I want to display.
So in JPA to do this, I have to do n + 1 separate 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?
That is doable, but it does complicate things a bit in that in my JSF page I
can't just bind a list of Departments to my datatable; I would need to bind a
list of DepartmentBeans that would wrapper my Department and for the employees
property would instead return the results of a separate query to populate the
Employees list for each row. Does that sound correct?
Thanks
Tom
Markus Fuchs wrote:
> Hi Tom,
>
> JPA queries are not intended to return incompletely initialized fields.
> As the Department in question really contains Employees besides those
> having more than 15 years of service, the employeeCollection will always
> contain all Employees from that Department.
>
> A query returning a Collection containing the Employees with more that
> 15 years of service is:
>
> select e from Employee e where e.department.deptno = 100 and
> e.yearsOfService >= 15
>
> Tom Mutdosch wrote:
> > Hi there,
> > I have a question regarding JPA Query
> >
> > I have a DEPARTMENT entity with a one-to-many relationship to an
> > EMPLOYEE entity. Say I want to get "departments with a department
> > number of 100 and containing EMPLOYEES with more than 15 years of
> > service".
> >
> > Is it possible to get back a single result containing the DEPARTMENT
> > object; and its relationship collection list consisting of only the
> > EMPLOYEES matching the criteria?
> >
> No. Please see above.
>
> > I've tried creating various queries using joins, but they always
> > return me back the DEPARTMENT I want, but the its employee list
> > contains ALL of the employees belonging to that department, not just
> > the ones matching the "15 years of service" filter.
> >
> > For example:
> > select d from Department d LEFT JOIN d.employeeCollection e WHERE
> > d.deptno = 100 AND e.yearsOfService >= 15
> >
> > This returns one result - a Department with ALL of the employees that
> > belong to that department (not just the ones matching e.yearsOfService
> > >= 15)
> >
> > I also tried this:
> > select d, e from Department d LEFT JOIN d.employeeCollection e WHERE
> > d.deptno = 100 AND e.yearsOfService >= 15
> >
> > And that returns 3 results, each an array containing a Department and
> > Employee. This is giving me the right data (there are 3 employees
> > that match the criteria), but I really just want to get back a single
> > department result containing the filtered employees. Is this possible?
> >
> No. How should the expected result look like in this case? An array
> containing one Department and three Employees?
>
> HTH,
>
> -- markus.