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: local policy)
Mime-Version: 1.0 (Apple Message framework v752.3)
In-Reply-To: <5240F16C-5619-4174-8754-6940EE6CCDF8@SUN.com>
References: <45DDFB3F.2090205@gmail.com>
 <5240F16C-5619-4174-8754-6940EE6CCDF8@SUN.com>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <AE9EF045-42E6-438D-A2EF-29D84AA7F8F0@iq80.com>
Content-Transfer-Encoding: 7bit
From: Dain Sundstrom <dain@iq80.com>
Subject: Re: possible to write a JPA Query to that filters both an Entity and
 its relationship entities?
Date: Fri, 23 Feb 2007 07:21:26 -0800
To: open-jpa-dev@incubator.apache.org

Is it now legal to select multiple items using EJBQL?  I haven't  
looked at the 3.0 syntax much.

-dain

On Feb 22, 2007, at 4:54 PM, Craig L Russell wrote:

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