Hi Tom,
Do you have a Department reference in the Employee entity? If yes, you can query
all the employees with fetched departments, then iterate the results and
construct the requires output.
thanks,
-marina
Markus Fuchs wrote:
> 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
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>