Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: open-jpa-dev@incubator.apache.org
Received-SPF: pass (herse.apache.org: domain of plinskey@bea.com designates
 66.248.192.21 as permitted sender)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: possible to write a JPA Query to that filters both an Entity and
 its relationship entities?
Date: Fri, 23 Feb 2007 07:56:53 -0800
Message-ID: <7D856CDFE035FF45A0420ACBD71BDD63034FDFF6@repbex02.amer.bea.com>
In-Reply-To: <AE9EF045-42E6-438D-A2EF-29D84AA7F8F0@iq80.com>
Thread-Topic: possible to write a JPA Query to that filters both an Entity and
 its relationship entities?
Thread-Index: AcdXXmEG0d3TVg69StqMbYRejC5ezAABLkIg
References: <45DDFB3F.2090205@gmail.com>
 <5240F16C-5619-4174-8754-6940EE6CCDF8@SUN.com>
 <AE9EF045-42E6-438D-A2EF-29D84AA7F8F0@iq80.com>
From: "Patrick Linskey" <plinskey@bea.com>
To: <open-jpa-dev@incubator.apache.org>

It is, but it doesn't buy you much in this situation -- the oldTimers
collection in your example won't be filtered to just the ones that are
old. It'll be all the employees in the dept.

-Patrick

--=20
Patrick Linskey
BEA Systems, Inc.=20

_______________________________________________________________________
Notice:  This email message, together with any attachments, may contain
information  of  BEA Systems,  Inc.,  its subsidiaries  and  affiliated
entities,  that may be confidential,  proprietary,  copyrighted  and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.=20

> -----Original Message-----
> From: Dain Sundstrom [mailto:dain@iq80.com]=20
> Sent: Friday, February 23, 2007 7:21 AM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: possible to write a JPA Query to that filters=20
> both an Entity and its relationship entities?
>=20
> Is it now legal to select multiple items using EJBQL?  I haven't =20
> looked at the 3.0 syntax much.
>=20
> -dain
>=20
> On Feb 22, 2007, at 4:54 PM, Craig L Russell wrote:
>=20
> > How about
> >
> > select dept, oldtimers from Department dept LEFT JOIN =20
> > dept.employeeCollection oldtimers WHERE dept.deptno >=3D 100 AND  =20
> > oldtimers.yearsOfService >=3D 15
> >
> > That should give you only departments that satisfy the deptno =20
> > constraint and since you are not using outer join, only=20
> departments =20
> > that also have at least one employee satisfying the yearsOfService =20
> > constraint.
> >
> > The results would have one element for each employee. Each result =20
> > row would contain the department and the employee. So you would =20
> > have the results:
> > [looks better in monospace font]
> > dept            oldtimers
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D   =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > 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=20
> relationships and a =20
> >> WHERE filter.  I basically want to have a JSF page containing a =20
> >> datatable that shows a list of Departments - each Department row =20
> >> shows relevant columns (department name, ID, etc) as well as a =20
> >> nested list of that Department's Employees with 15 or more years =20
> >> of service.
> >> I was wondering if there was any way to create a JPA query that =20
> >> would return me such a result using just a single query.  From =20
> >> what I have heard, I don't believe this is possible but thought =20
> >> I'd ask here just to be sure.  As far as I know, JPA will just =20
> >> return me the Departments that match that query and each =20
> >> Department will contain every Employee that belongs to it.
> >>
> >> Background: I have a DEPARTMENT entity with a one-to-many =20
> >> relationship to an EMPLOYEE entity.  On my web page, I want to =20
> >> display "departments with a department number >=3D 100 and =20
> >> containing EMPLOYEES with more than 15 years of service".
> >>
> >> Using SDOs I was able to write a query that would return a=20
> List of =20
> >> results which I would then bind to my JSF datatable.  This list =20
> >> would contain Departments, and the Departments would have the =20
> >> Employees that matched that criteria.  To do something similar in =20
> >> JPA, I think I now need to do n + 1 queries.  That is, one query =20
> >> to retrieve the departments that I want to display, and then for =20
> >> each department another query to get the filtered list of its =20
> >> Employees.
> >> In summary, I basically want to do something like:
> >> select d from Department d LEFT JOIN d.employeeCollection e WHERE =20
> >> d.deptno >=3D 100 AND  e.yearsOfService >=3D 15
> >>
> >> But I think I need to do two separate queries.  One to get the =20
> >> departments:
> >> select d from Department d WHERE d.deptno >=3D 100
> >>
> >> Then for each Department returned in that result set, a separate =20
> >> query to get the employees I'm after:
> >> select e from Employee e where e.department.deptno =3D d.deptno and
> >> e.yearsOfService >=3D 15
> >>
> >>
> >> Does this sound like the right/only way to do this?
> >> Thanks,
> >> Tom
> >>
> >>
> >>
> >>
> >
> > Craig Russell
> > Architect, Sun Java Enterprise System=20
> http://java.sun.com/products/jdo
> > 408 276-5638 mailto:Craig.Russell@sun.com
> > P.S. A good JDO? O, Gasp!
> >
>=20
>=20