openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tom Mutdosch <tom...@gmail.com>
Subject Re: possible to write a JPA Query to that filters both an Entity and its relationship entities?
Date Sat, 24 Feb 2007 03:16:32 GMT
Hi Craig,

You are right; with that query "select dept, oldtimers...."  you will 
get back a List of Object[].    Each Object[] will contain a Department 
and and Employee just like you said initially; you'd get 5 results (or 
rows):
{R&D,             Larry}
{R&D,            Curly}
{R&D,             Moe}
{Entertainment,   Fred}
{Entertainment,   Ginger}

What Patrick and I were referring to was the Employee collection found 
in each Department's collection.  For your R&D dept, if you called 
d.getEmployees() it would return all Employees (Larry, Curly, Moe, 
Shemp), not just the ones that were filtered in the WHERE clause.  That 
was what I was trying to wrap my head around.  I think we were talking 
about two different things, so I think your expectation is correct.

Regards,
Tom


Craig L Russell wrote:
> Hi Patrick,
>
> From the JPQL
> select dept, oldtimers from Department dept LEFT JOIN 
> dept.employeeCollection oldtimers WHERE dept.deptno >= 100 AND  
> oldtimers.yearsOfService >= 15
>
> I expect to get SQL that looks something like
> select dept.id, dept.name, oldtimers.id, oldtimers.firstname, 
> oldtimers.lastname, oldtimers.salary, oldtimers.ssn from Department 
> dept, Employee oldtimers WHERE dept.id = oldtimers.deptid AND 
> dept.deptno >= 100 AND  oldtimers.yearsOfService >= 15
>
> I then construct instances from the returned dept and oldtimers 
> columns and give back to the user the results, with one result row for 
> each SQL ResultSet row. So the filtering from the JPQL translated into 
> the WHERE clause in the SQL should exclude oldtimers that don't qualify.
>
> If you don't use the oldtimers.yearsOfService >= 15 in the SQL, then 
> you will get all of the employees in the department returned in the 
> result. But why do you not use the oldtimers.yearsOfService >= 15 in 
> the SQL?
>
> Craig
>
> On Feb 23, 2007, at 2:12 PM, Patrick Linskey wrote:
>
>>> In fact, you have to do a bit of work to get SQL to return you non-
>>> filtered instances. So I don't get it. Does OpenJPA not
>>> construct the
>>> obvious SQL that filters oldtimers?
>>
>> I don't think that you and I are really in sync here. Maybe some more
>> concrete examples are in order?
>>
>> -Patrick
>>
>> --Patrick Linskey
>> BEA Systems, Inc.
>>
>> _______________________________________________________________________
>> 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.
>>
>>> -----Original Message-----
>>> From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
>>> Sent: Friday, February 23, 2007 2:09 PM
>>> To: open-jpa-dev@incubator.apache.org
>>> Subject: Re: possible to write a JPA Query to that filters
>>> both an Entity and its relationship entities?
>>>
>>> So,
>>>
>>> I can tell you that having a result column that is filtered in the
>>> query is not what a user would expect. In JDO, the oldtimers column
>>> would be filtered.
>>>
>>> In fact, you have to do a bit of work to get SQL to return you non-
>>> filtered instances. So I don't get it. Does OpenJPA not
>>> construct the
>>> obvious SQL that filters oldtimers?
>>>
>>> I've re-read the JPA specification, and it appears to be silent on
>>> the issue of filtering. Is this a portability issue?
>>>
>>> Craig
>>>
>>> On Feb 23, 2007, at 1:57 PM, Tom Mutdosch wrote:
>>>
>>>> Hi Patrick,
>>>> Thanks for the query suggestion.  I guess what I was initially
>>>> thinking of doing was incorrect in that JPA doesn't really give me
>>>> a "view" of what I want.  That is, I can never get a Department
>>>> object containing a list of filtered Employees.  A JPA object
>>>> returned from a query is always going to be an exact
>>> representation
>>>> of the database.  So your Department object is always going to
>>>> contain all of the Employees in its relationship.
>>>>
>>>> So like you mentioned, I can still get all the information using
>>>> one query, and then just process those results as I want them.  I
>>>> imagine that this would entail some sort of wrapper bean
>>> that would
>>>> house the Department and the filtered list of Employees.  Or what
>>>> if I added a regular method to my Department entity called
>>>> getFilterEmployees() which would return a List that I populated
>>>> with the filtered results from my query?  Does that seem like a
>>>> reasonable thing to do -- if I didn't want to deal with a wrapper
>>>> object but still have all of my desired data captured by a single
>>>> Entity?
>>>> Thanks
>>>> Tom
>>>>
>>>>
>>>> Patrick Linskey wrote:
>>>>> 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
>>>>>
>>>>>
>>>
>>> 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!
>>>
>>>
>
> 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!
>


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message