self referencing - is inner join assumed? - SOLVED thanks!

mtHead · **Joined:** Fri May 30, 2008 12:15 pm **Posts:** 7

I have a self referencing table relationship, as in the Hibernate Cat example, mapped as follows:

Code:

   <many-to-one name="mother">
      <column name="mother_id" />
   </many-to-one>

I have a Named Query which seeks to obtain all the Cats in the table with the name "Mittens" OR whose mother is named "Mittens".

Code:

   from Cat as cat 
   where cat.name = "Mittens" or cat.mother.name = "Mittens" 

My problem is that when a Cat's row has no mother_id in it, and is named Mittens, it's not returned. So it looks like Hibernate is doing an inner join instead of an outer join.

Can I change something in the mappings or my Named Query in order to get back all the Cats named Mittens even if mother_id is null?

Sorry if this is a simple question - I did look for an answer before posting :-)

--------------------------------------------------------------------------------------

P.S. I have tried adding the outer-join attribute to my <many-to-one> mapping as follows:

Code:

   <many-to-one name="mother" outer-join="true">
      <column name="mother_id" />
   </many-to-one>

However, cases where mother_id was null were still not included.

--------------------------------------------------------------------------------------

P.P.S. I have also tried the following in my Named Query - to no avail

Code:

   from Cat as cat 
   where cat.name = "Mittens" or cat.mother.name = "Mittens" or cat.mother.name is null

jpkrohling · **Posted:** Wed Aug 06, 2008 10:53 am

What is the SQL that Hibernate is generating?

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

I guess this should do the job for you

from Cat as cat left outer join cat.mother as motherCat where cat.name = "Mittens" or motherCat.name = "Mittens" or motherCat.name is null

Refer following link for more information on HQL
http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#queryhql

mtHead · **Joined:** Fri May 30, 2008 12:15 pm **Posts:** 7

jpkrohling wrote:

What is the SQL that Hibernate is generating?

Thanks for you interest!

My actual SQL is quite a bit more complex of course, but from what I can tell there are a couple of SQL statements that set up the joins (based on the HBM.XML mappings?) and those statements are doing left outer joins.

However, when the filters of my Named Query are applied the very first filter causes the inner join and eliminates the potential null mother_id's. The first filter (as you would imagine) looks like:

Code:

where cat0_.MOTHER_ID=cat7_.id

The evidence seems to indicate that I should be looking for the answer in altering my Named Query. I've tried adding "or cat.mother.name is null", but it didn't eliminate the join that you see above.

Any suggestions?

mtHead · **Joined:** Fri May 30, 2008 12:15 pm **Posts:** 7

sethrohit1977 wrote:

I guess this should do the job for you

from Cat as cat left outer join cat.mother as motherCat where cat.name = "Mittens" or motherCat.name = "Mittens" or motherCat.name is null

Refer following link for more information on HQL
http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#queryhql

Thanks for your post! Adding "left outer join" to my Named Query did get my results closer to what I'm trying to get :-)

Now, I'm getting all the cat objects that I expect (great!), but am also getting motherCat objects (which are sometimes null). I don't want the motherCat objects to be returned at all. Any idea how I can eliminate them from my results?

mtHead · **Joined:** Fri May 30, 2008 12:15 pm **Posts:** 7

The final change that I needed to make in order to get the result set that I wanted once the left outer join was added, was to prepend a "select" statement to my Named Query so that I did not get motherCat objects back as well as cat objects.

Code:

select cat from Cat as cat left outer join cat.mother as motherCat where cat.name = "Mittens" or motherCat.name = "Mittens" 

Yay!!