Hi Marina,
Marina Vatkina wrote:
> 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.
>
Yes, I do have a reference in the Employee entity. I agree, this sounds like it
would be a much easier way to get the data in such a way that I can easily
construct the output that I'm looking for. Thanks for the suggestion!
> thanks,
> -marina
>
Thanks,
Tom
> 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.
> >>