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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
