Mapping isue

regen · **Joined:** Wed Feb 20, 2008 9:15 pm **Posts:** 11

Hello,
i have a datastructure like this:

I want a class with
the Contact.Name, Contact.Number, ContactLocationNumber.DialableNumber to a given Location.Name or given ContactLocationNumber.DialableNumber.

With pure SQL it's very easy with 2 joins, but how can I map such a scenario with nhibernate?

What is the way to go?
3 Mapping files each for one Database table and then join with hql?

Or is there another way without heavy using hql?

wolli · **Posted:** Sat Aug 09, 2008 5:41 am

You can map each entity and joni them with associations:

Code:

<class name="Contact">
  <bag name="ContactLocationNumbers">
     <one-to-many class="ContactLocationNumbers"/>
  </bag>
</class>

<class name="ContactLocationNumber">
   <many-to-one name "Location"/>
</class>

In HQL you can then use:

Code:

from Contact c where c.ContactLocationNumbers.Location.Name = "Somewhere"

Is that what you need ?

regen · **Joined:** Wed Feb 20, 2008 9:15 pm **Posts:** 11

yes, that looks great.

Is there also another way to do this with mapping files only?

wolli · **Posted:** Sat Aug 09, 2008 7:29 am

Quote:

Is there also another way to do this with mapping files only?

I can't follow ?

regen · **Joined:** Wed Feb 20, 2008 9:15 pm **Posts:** 11

Hello,
you HQL query results in a "expecting 'elements' or 'indices' after Location" Exception.

My SQL Query would look like

Quote:

select c.Name, c.Number, cls.DialableNumber from Contact c
join ContactLocationNumber cls on cls.ContactId=c.Id
join Location loc on cls.LocationId = loc.Id
where loc.Name='someloc'

wolli · **Posted:** Mon Aug 11, 2008 7:06 am

Then try this:

Code:

from Contact c 
   join where c.ContactLocationNumbers cln 
   join cln.Location l
   where l.Name = "Somewhere"

regen · **Joined:** Wed Feb 20, 2008 9:15 pm **Posts:** 11

Hello,
thank you. That's works.
However this results in (Contact.Count + 1 ) Queries against the Database. So for 50 Entries in the DB i got 51 queries.

Anyone any idea what's the probleme here?

wolli · **Posted:** Mon Aug 11, 2008 8:28 am

Have a look at the documentation:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-joins

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/performance.html#performance-fetching

You can use a "fetch join" in HQL for retrieving collections with a join instead of single selects:

Code:

select c.Name, c.Number, cls.DialableNumber from Contact c
join fetch ContactLocationNumber cls on cls.ContactId=c.Id
join Location loc on cls.LocationId = loc.Id
where loc.Name='someloc'