openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ognjen Blagojevic <ogn...@etf.bg.ac.yu>
Subject Re: Is INNER JOIN necessary with ORDER BY?
Date Thu, 27 Mar 2008 14:45:34 GMT
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:Craig.Russell@sun.com
>>> P.S. A good JDO? O, Gasp!
>>>
> 
> 


Mime
View raw message