Hi Michael,
I realized that one moment after I sent my first message.
For the archives, the correct query would be:
select p
from Person LEFT OUTER JOIN p.idPlaceOfBirth b
order by b.name
Regards,
Ognjen
Michael Bouschen wrote:
Hi Ognjen,
I agree with Craig, your solution is correct. I would like to propose a
small correction: you might want to use the outer join identification
variable in the order by clause.
SELECT p FROM Person p LEFT OUTER JOIN p.idPlaceOfBirth b ORDER BY b.name
The order by clause of the outer join example you gave still navigates
the relationship. An JPA implementation strictly following the spec
could still generate an inner join for the order by.
Regards Michael
Hi Craig,
Thank you for your answer.
We have a Web interface with lots of tables that the user can sort by
clicking on the column name. Appropriate action sets the string in the
ORDER BY clause of the JPA query. But, from what you wrote, I
understand that this is not enough, and that we must also modify the
FROM clause.
Regards,
Ognjen
Craig L Russell wrote:
Hi Ognjen,
As I understand the JPA specification, your analysis and solution are
correct. By the specification, navigating a relationship uses inner
join semantics.
By the way, JDO uses outer join semantics in query navigation. With
JDO, you would get Persons with null id_place_of_birth unless your
query explicitly had a query clause id_place_of_birth != null. Of
course, the position of the null entries in the result list still
might be before or after non-null entries.
Regards,
Craig
On Mar 26, 2008, at 5:14 AM, Ognjen Blagojevic wrote:
Hi all,
How can I avoid INNER JOINs to be generated for tables stated in
ORDER BY clause?
For instance, EJB QL
select p
from Person p
order by p.idPlaceOfBirth.name
results in SQL which INNER JOINS Person and Place, and the persons
without known place of birth (id_place_of_birth == null), are not
listed.
The only solution I found so far is to explicitly use
select p
from Person LEFT OUTER JOIN p.idPlaceOfBirth b
order by p.idPlaceOfBirth.name
But it tends to unnecessarily add to the EJB QL queries complexity.
Is there any other way to do the same thing?
Regards,
Ognjen
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!
