many-to-one join question

traverse · **Joined:** Mon Aug 06, 2007 12:57 pm **Posts:** 4

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 1.2.0.GA

Name and version of the database you are using: MS Sql Server 2000

Consider the Parent, Child relationship using this mapping:

Code:

<class name="Parent" table="parents">
      <id name="id" column="id" type="int">
         <generator class="identity" />
      </id>
      <property name="firstName" column="firstName"  />
      <property name="lastName" column="lastName"  />

      <bag name="children">
         <key column="parentId"/>
         <one-to-many class="Child"/>
      </bag>
   </class>

   <class name="Child" table="children">
      <id name="id" column="id" type="int">
         <generator class="identity" />
      </id>
      <property name="firstName" column="firstName"  />
      <property name="parentId" column="parentId" />

      <many-to-one name="parent" class="Parent" column="parentId" fetch="join" lazy="false"/>

   </class>

If I retrieve a list of Children, why does it create a SELECT for every Parent when I use the fetch="join" attribute? Or do I miss-understand the "fetch" attribute? If so, is there a better way I should be doing this?

Ulitmate goal: I want to display all the children's names (thousands):

Code:

child.FirstName + " " + child.Parent.LastName;

or maybe???:

Code:

child.FirstName + " " + child.ParentLastName;

and have the initial list of children created using a join instead of a select for each child's parent.

pelton · **Posted:** Mon Aug 06, 2007 4:53 pm

I'm not sure I understand what the problem is.

Are you asking for explanation on how the fetch attribute works,
or, are you having a problem making a list of the full name of the child objects?

--Brian

traverse · **Joined:** Mon Aug 06, 2007 12:57 pm **Posts:** 4

pelton wrote:

Are you asking for explanation on how the fetch attribute works,
or, are you having a problem making a list of the full name of the child objects?

Basically when I retrieve the collection of Children, it does not do a JOIN for the Parent data.

I have found out more, if I use

Code:

IQuery query = session.CreateQuery("from Children");

it does not do a JOIN, however if I use

Code:

ICriteria c = session.CreateCriteria(typeof (Child));

it does create the JOIN. So I guess its a matter of me learning why now.

karlchu · **Posted:** Tue Aug 07, 2007 10:27 am

The "fetch" attribute does not have any effect when using HQL. With HQL, you have to do this:

Code:

IQuery query = session.CreateQuery("from Children c left join fetch c.parent");

If you want to learn more about fetching strategies, have a look at the docs here:
http://www.hibernate.org/hib_docs/nhibe ... e-fetching

tseanf · **Joined:** Wed Jun 27, 2007 9:34 am **Posts:** 8

I have been having issues trying to get NHibernate 1.2.0 to ever use the inner select to populate my many-to-one properties.

If I have the following query:

Code:

FROM AppUser app
LEFT JOIN FETCH app.Type

The AppUser query contains the inner join to Type, but it still queries the Type table once for each AppUser row.

Also, why does NHibernate query for every row, shouldn't it check the cache first since most rows in AppUser are of the same type?

Any ideas would be appreciated.

Thanks,

Sean

karlchu · **Posted:** Sat Dec 01, 2007 11:26 pm

Please start a new thread instead of resurrecting old topics so you can give proper credit to whoever end up helping you.

By "cache", I assume you meant the second-level cache. Queries made by IQuery and ICriteria are not automatically cached. Have a read of this thread and see if it answers your question:
http://forum.hibernate.org/viewtopic.php?t=981567