Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Unwanted join added when using "order by" on nested property
PostPosted: Wed Jun 29, 2011 3:38 am 
Newbie

Joined: Tue Apr 22, 2008 10:01 am
Posts: 6
Hello,

I have a problem in an HQL query. I'm trying to get an ordered list of "Connection" objects using the following query :
Code:
FROM Connection AS connection ORDER BY connection.message.status ASC


When I look at the generated query, it seems that Hibernate adds a JOIN clause like this :

Code:
select [...] from CONNECTION connection0_, MESSAGE message1_ where connection0_.ID_MESSAGE=message1_.SID_MESSAGE order by message1_.ID_STATUS ASC


It's cool, but unfortunately it adds an INNER JOIN by default. So, my result list contains only the Connections that have a reference to a message. The connections without message aren't listed. How can I tell Hibernate to add a left outer join instead ?

Here is the mapping between Connection and Message.

Code:
public class Connection {

    ...

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="ID_MESSAGE", nullable=true)
    @ForeignKey(name = "FK_MESSAGE_CONNECTION")
    private Message message;

    ...

}


I tried to add "optional=true" to the ManyToOne annotation, but it doesn't change anything.
I tried adding manually a left outer join, but the result list contains raw data and not persistent objects.


Top
 Profile  
 
 Post subject: Re: Unwanted join added when using "order by" on nested property
PostPosted: Wed Jun 29, 2011 10:59 am 
Newbie

Joined: Tue Apr 22, 2008 10:01 am
Posts: 6
I found a workaround for this problem.

I used the Criteria API, so I can add my LEFT OUTER JOIN manually...
Code:
criteria.createAlias(path, alias, CriteriaSpecification.LEFT_JOIN)

.. and still get persistent objects in the result list.


However I am still interested in a solution using HQL AND returning persistent objects.


Top
 Profile  
 
 Post subject: Re: Unwanted join added when using "order by" on nested property
PostPosted: Wed Jun 29, 2011 5:49 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
I really don't understand what you mean with "the result list contains raw data and not persistent objects". Manually specifying a left join in the HQL should work:

Code:
select connection FROM Connection AS connection left join connection.message as msg ORDER BY msg.status ASC


Top
 Profile  
 
 Post subject: Re: Unwanted join added when using "order by" on nested property
PostPosted: Thu Jun 30, 2011 4:35 am 
Newbie

Joined: Tue Apr 22, 2008 10:01 am
Posts: 6
I'm sorry I probably didn't use the correct terms.

What I meant was that this request :
Code:
FROM Connection AS connection ORDER BY connection.message.status ASC

returns a List of Connection objects.

And this request :
Code:
FROM Connection AS connection left join connection.message as msg ORDER BY msg.status ASC

returns a List of List<Object> containing the fields of both Connection and Message tables.

But I realized my mistake when I read your answer. If I add a projection SELECT connection to the second query, the result is then a List<Connection>.

Thank you for your help !


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.