From open-jpa-dev-return-2347-apmail-incubator-open-jpa-dev-archive=incubator.apache.org@incubator.apache.org Thu Feb 22 23:56:02 2007 Return-Path: Delivered-To: apmail-incubator-open-jpa-dev-archive@locus.apache.org Received: (qmail 14931 invoked from network); 22 Feb 2007 23:56:02 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 22 Feb 2007 23:56:02 -0000 Received: (qmail 84170 invoked by uid 500); 22 Feb 2007 23:56:10 -0000 Delivered-To: apmail-incubator-open-jpa-dev-archive@incubator.apache.org Received: (qmail 84145 invoked by uid 500); 22 Feb 2007 23:56:10 -0000 Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: open-jpa-dev@incubator.apache.org Delivered-To: mailing list open-jpa-dev@incubator.apache.org Received: (qmail 84136 invoked by uid 99); 22 Feb 2007 23:56:10 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Feb 2007 15:56:10 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of plinskey@bea.com designates 66.248.192.22 as permitted sender) Received: from [66.248.192.22] (HELO usremg02.bea.com) (66.248.192.22) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Feb 2007 15:55:59 -0800 Received: from usremr01.bea.com (usremr01.bea.com [10.160.29.91]) by usremg02.bea.com (Switch-3.2.2/Switch-3.2.2) with ESMTP id l1MNtbI7018109 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=FAIL) for ; Thu, 22 Feb 2007 15:55:37 -0800 Received: from repbex02.amer.bea.com (repbex02.bea.com [10.160.26.99]) by usremr01.bea.com (Switch-3.2.2/Switch-3.2.2) with ESMTP id l1MNtYjU012857 for ; Thu, 22 Feb 2007 15:55:36 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5 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: Thu, 22 Feb 2007 15:55:32 -0800 Message-ID: <7D856CDFE035FF45A0420ACBD71BDD63034FDD92@repbex02.amer.bea.com> In-Reply-To: <45DE29F8.6030108@sun.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: possible to write a JPA Query to that filters both an Entity and its relationship entities? Thread-Index: AcdW2uvc8QKk2rhnSJuQ0qSNtL4QqwAAPEkA References: <45DDFB3F.2090205@gmail.com> <2944D567-9414-4928-93D5-C30C71298E83@iq80.com> <45DE29F8.6030108@sun.com> From: "Patrick Linskey" To: X-PMX-Version: 4.7.1.128075, Antispam-Engine: 2.5.0.283055, Antispam-Data: 2007.2.21.161433 X-Virus-Checked: Checked by ClamAV on apache.org I think that Tom is looking for a way to get back just those employees that are > 15 years, so that he could display something like so: Department: R&D Old-timers: Larry, Curly, Moe Department: Entertainment Old-timers: Fred, Ginger IOW, he's not looking for departments that have old-timers, but rather looking for the departments with old-timers and *just* the old-time employees. I think that Dain's suggestion of turning it around, plus appropriate ordering, is the best bet: select e from Employee e where e.tenure >=3D 15 order by e.dept This will require some post-processing to turn the single ordered list into a list of lists, but you could probably do that inline with the display logic if performance becomes a concern. OpenJPA can definitely be tuned (using the Query's FetchPlan) to eagerly join the emp=3D>dept relation: OpenJPAQuery q =3D OpenJPAPersistence.cast(em.createQuery( "select e from Employee e where e.tenure >=3D 15 order by e.dept")); q.getFetchPlan().addField(Employee.class, "dept"); List emps =3D (List) q.getResultList(); -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: Lance.Andersen@Sun.COM [mailto:Lance.Andersen@Sun.COM]=20 > Sent: Thursday, February 22, 2007 3:41 PM > 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 > What about something like: >=20 > select distinct e.department from employee e where=20 > e.department.deptno=20 > >=3D100 and e.yearsOfService>=3D15 >=20 >=20 > Not sure if the above will do the trick without trying it myself... >=20 > Dain Sundstrom wrote: > > I'm no JPA expert, so take my comments with a grain of salt. > > > > How about searching the other direction: > > > > select e from employee e where e.department.depno >=3D 100 &&=20 > > e.yearsOfService >=3D 15 > > > > Then assuming OpenJPA can do it, you could tune the query=20 > to grab the=20 > > department object along with the employee. If OpenJPA=20 > can't do that,=20 > > you will get at most 100+1 queries. Alternatively, you could try=20 > > executing this query before the one above: > > > > select d from Department d WHERE d.deptno >=3D 100 > > > > And then rely on the OpenJPA cache to hook the departments to the=20 > > employees. Depending on the database vendor and the data in the=20 > > tables, executing two queries may be more efficient then one. > > > > -dain > > > > On Feb 22, 2007, at 12:21 PM, Tom Mutdosch wrote: > > > >> Hi there, > >> I have a question about JPA Queries containing 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=20 > as a nested=20 > >> 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=20 > that would=20 > >> return me such a result using just a single query. From=20 > what I have=20 > >> heard, I don't believe this is possible but thought I'd=20 > ask here just=20 > >> to be sure. As far as I know, JPA will just return me the=20 > >> Departments that match that query and each Department will contain=20 > >> 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=20 > >> EMPLOYEES with more than 15 years of service". > >> > >> Using SDOs I was able to write a query that would return a List of=20 > >> results which I would then bind to my JSF datatable. This=20 > list would=20 > >> contain Departments, and the Departments would have the Employees=20 > >> that matched that criteria. To do something similar in=20 > JPA, I think=20 > >> I now need to do n + 1 queries. That is, one query to=20 > retrieve the=20 > >> departments that I want to display, and then for each department=20 > >> another query to get the filtered list of its 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 > >> > >> > >> > >> > > >=20