openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: possible to write a JPA Query to that filters both an Entity and its relationship entities?
Date Thu, 22 Feb 2007 23:40:40 GMT
What about something like:

select distinct e.department from employee e where e.department.deptno 
 >=100 and e.yearsOfService>=15


Not sure if the above will do the trick without trying it myself...

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 >= 100 && 
> e.yearsOfService >= 15
>
> Then assuming OpenJPA can do it, you could tune the query to grab the 
> department object along with the employee.  If OpenJPA can't do that, 
> you will get at most 100+1 queries.  Alternatively, you could try 
> executing this query before the one above:
>
>   select d from Department d WHERE d.deptno >= 100
>
> And then rely on the OpenJPA cache to hook the departments to the 
> employees.  Depending on the database vendor and the data in the 
> 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 
>> 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
>>
>>
>>
>>
>

Mime
View raw message