Hi Tom,
Tom Mutdosch wrote:
> 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 sounds correct.
> 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?
>
I'm not an JSF expert, but you'd need to execute n + 1 queries.
Regards,
-- markus.
> 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.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>