Many to one mapping using non primary key

kunwarmahen · **Joined:** Thu Apr 19, 2007 9:52 am **Posts:** 27

Hello Everyone,

I have a situation. In my project the data model is controlled by business guy and he has created a relationship between two tables where the relationship is with a unique column on other table rather than primary key. Let me create a sample table

Table - CATEGORY
Column
ID - PK
Version -
CatergoryID - Combination of Version and this column is is Unique
Category_Desc

Table - PCM
Coulmn
ID - PK
Catergory_ID - FK to Category ID in Category Table
Version - FK to Version on Category Table
.....
.....

Now when I use hibernate to use many-to-one relationship it does not work. It does not load child Category.

I you suggest it is a bad database design. I agree with you but I can't help, its political.

So can someone suggest me how to use this scenario with hibernate.

Regards,
Mahen

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

use the property-ref attribute in order to specify an FK relationship by a column other than the PK in the target table.

kunwarmahen · **Joined:** Thu Apr 19, 2007 9:52 am **Posts:** 27

Got it thanks. But how do you achieve same when there are multiple columns involved like in the situation I mentioned. It uses both Version and CatergoryID to map.

Mahen

kunwarmahen · **Joined:** Thu Apr 19, 2007 9:52 am **Posts:** 27

Got it using <properties> tag

Mahen

DeltaHuey · **Joined:** Tue Nov 25, 2008 5:23 pm **Posts:** 4

*double post*

DeltaHuey · **Joined:** Tue Nov 25, 2008 5:23 pm **Posts:** 4

Can you post your solution? I have the same problem.

Logically I want to do this:

Code:

<many-to-one class="ShippingOrder" name="ShippingOrder">
    <column name="PICK#" property-ref="PickListNumber" />
    <column name="LINE#" property-ref="LineItemNumber" />
    <column name="RELEASE" property-ref="ReleaseNumber" />
</many-to-one>

Unfortunately property-ref is only allowed in the many-to-one tag, not the column tag.

kunwarmahen · **Joined:** Thu Apr 19, 2007 9:52 am **Posts:** 27

You need to do this

Code:

<many-to-one class="ShippingOrder" name="ShippingOrder" property-ref="somenaturalid">
    <column name="PICK#" property-ref="PickListNumber" />
    <column name="LINE#" property-ref="LineItemNumber" />
    <column name="RELEASE" property-ref="ReleaseNumber" />
</many-to-one>

On other hbm you need to define something like this

<properties name="somenaturalid" unique="true" insert="true" update="true">
           <property name="pick" type="big_decimal">
               <column name="PICK" not-null="true" />
           </property>        
           <property name="line" type="big_decimal">
               <column name="LINE" not-null="true" />
           </property> 
           <property name="release" type="big_decimal">
               <column name="RELEASE" not-null="true" />
           </property>          
</properties>

You don't need to define your properties twice in second hbm.
Hopefully this helps let me know if that works for you

DeltaHuey · **Joined:** Tue Nov 25, 2008 5:23 pm **Posts:** 4

My apologies, I am using NHibernate. I was in the NHibernate Users forum, did a search to see if my question had already been asked and didn't notice this result was in Hibernate Users. I will have to remember to filter results next time!

I just read Hibernate documentation to see what the properties tag did, and your mapping solution is perfect for what I want. That said, it isn't provided (that I can see) in NHibernate.

Thanks for quick response!

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

I had a similar problem when mapping from UserCountry table with userId (PK), countryId (FK) to Address table (one-to-many) userId (FK), countryId (FK) to get a unique address for a given UserCountry record.

In User mapping, I would get a unique address as

Code:

<many-to-one name="uniqueAddress" class="database.Address" property-ref="userIdCountryId">
         <column name="user_id"></column>   <!--  name of the foreign key column -->
         <column name="country_id"></column>   <!--  another foreign key column, the number of columns should match the property-ref -->
      </many-to-one>

In the Address mapping, I would have the property-ref defined as userIdCountryId:

Code:

<!-- properties name, logical name of the grouping - not an actual name of the property 
        this name can be used as property-ref for ecField -->
        <properties name="userIdCountryId">
           <!-- the insert="false" and update="false" are needed as the fields are already used as properties -->
           <property name="dummyUserId" type="long" insert="false" update="false">
              <column name="user_id" ></column>
           </property>
           <property name="dummyCountryId" type="long" insert="false" update="false">
              <column name="country_id" ></column>
           </property>
        </properties>

When I say

Code:

from User left join Address
where userId = 10 

the sql query does join both of the columns from the user table to the address table, it nicely joins both the fields from user to address i.e.,

Code:

from User user left outer join Address address
on user.user_id = address.user_id and user.country_id = address.country_id

What is the difference between using one-to-one and many-to-one or are there any other options besides these?

When will the simple left join on any other class be introduced without having to introduce all these?

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

Now here is where the mapping problem starts, Let's say I need to lookup Address table for a given user and if the address does not exist, I expect a null. I am also looking for a specific address with state 'NJ' and that the user table does not have a state column to link with address table, my simple sql would be:

Code:

user left join address where user.userId = address.userId and user.countryId = address.countryId 
and address.stateId = 'NJ'

otherwise I will end up getting many addresses, in my case there is no stateId in the user table

Code:

<many-to-one name="uniqueAddress" class="database.Address" property-ref="userIdCountryId">
         <column name="user_id"></column>   <!--  name of the foreign key column -->
         <column name="country_id"></column>   <!--  another foreign key column, the number of columns should match the property-ref -->
      </many-to-one>

<!-- properties name, logical name of the grouping - not an actual name of the property
        this name can be used as property-ref for ecField -->
        <properties name="userIdCountryId">
           <!-- the insert="false" and update="false" are needed as the fields are already used as properties -->
           <property name="dummyUserId" type="long" insert="false" update="false">
              <column name="user_id" ></column>
           </property>
           <property name="dummyCountryId" type="long" insert="false" update="false">
              <column name="country_id" ></column>
           </property>
        </properties>

This above mapping works fine when I write my HQl as

Code:

from User user left join user.uniqueAddress address
where address.stateName = 'NJ'

and I get what I need, however because it is mapped as a many-to-one and the user table does not include the state, every time I say

Code:

from User

it would throw an exception saying that it found multiple addresses for the same user. So in such a case there is no way I can map a user and address table?

I see that there is no solution to this problem unless I had a specific way to write my left join on Address as in my case my User does not have country field at all. Is there left join available or is there an alternative solution to this problem?

Help please!!!

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

update, insert (optional - defaults to true) : specifies that the mapped
columns should be included in SQL UPDATE and/or INSERT statements.
Setting both to false allows a pure "derived" property whose value is
initialized from some other property that maps to the same colum(s) or
by a trigger or other application.

from the above, when I set the update and insert to false, I was expecting that when I say

Code:

from user

it should not fetch even a proxy of the address, how else can I map the address in user so that it is not fetched unless I explicitly request? as the query breaks saying

Code:

org.hibernate.HibernateException: More than one row with the given identifier was found: database.Address@1eb02cd, for class: Address

which makes sense that I mapped it as many-to-one

mshijie · **Joined:** Fri May 29, 2009 4:44 am **Posts:** 2

Thanks!

mshijie · **Joined:** Fri May 29, 2009 4:44 am **Posts:** 2

Thanks!

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

One-to-one will not work the way we want it as it just cannot be used to fetch lazily for 1<---->0 or 1 relationship, https://www.hibernate.org/315.html and https://www.hibernate.org/162.html

But why is the many-to-one being eagerly loaded, when property-ref is used?

A many-to-one maps with lazy="proxy" by default, meaning it does not eagerly fetch the parent object, however, I noticed that if I use the property-ref to map to an other property field, they it is by default doing an eager fetch, no matter what other property options I set.

When will I find an answer to this problem?

Regards
Krishna

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

Alright, raised a JIRA

http://opensource.atlassian.com/project ... e/HHH-4046

Regards
Krishna