openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: possible to write a JPA Query to that filters both an Entity and its relationship entities?
Date Sat, 24 Feb 2007 04:28:31 GMT
Hi Tom,

The next step would be to use a constructor to avoid getting a  
List<Object[ ]> back. You should use a constructor to get an instance  
with two fields in it. Then it's a lot easier to use.

The JPQL spec doesn't allow returning a collection back as part of a  
result. Only single-valued fields or aggregates are allowed.

Craig

On Feb 23, 2007, at 7:16 PM, Tom Mutdosch wrote:

> 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!
>>
>

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
View raw message