many-to-one with "hard coded" value

wmnelis · **Joined:** Fri Aug 22, 2008 4:07 pm **Posts:** 9

I was wondering if there is a way I can specify the value of a column in a many-to-one relationship in the configuration. I think the best way to explain what I need is by showing an example.

We have a NameValueLists table that is used as a lookup table. This table contains all of our lookup values. It has two unique keys, the primary key (the ID column) and a combination of ListName and NumValue. Any table that has a foreign key to this one stores the NumValue. This means that you need to know which ListName to use in your queries in order to get the correct value from the NameValueLists table.

NameValueLists table:

Code:

ID          ListName      ValueName         NumValue
----------  ------------  ----------------  --------------
1           AddressType   Home              1
2           AddressType   Work              2
3           AddressType   Mailing           3
4           PhoneType     Home              1
5           PhoneType     Work              2
6           PhoneType     Mobile            3

This address table is an example of a table that gets joined to the NamedValueLists table. Note that the AddressType column is a foreign key to the NumValue column in the NameValueLists table. You need to hard code the ListName value in your query to get the correct value.

Address table:

Code:

ID         ...        AddressType
---------  ---------  -------------------
1          ...        2
2          ...        1
3          ...        2
4          ...        3

An example query would be as such:

Code:

select *
from Address a
join NameValueLists vl
on a.AddressType = vl.NumValue AND vl.ListName = 'AddressType'

I am not a big fan of the way this works, but the database is not mine, and I am unable to make changes to it.

So, the question is can I do something like this in my hibernate hbm.xml files (or an equivalent, note the 2nd column has a "value" attribute in it)?:

Code:

<class name="MyAssembly.BusinessObjects.Sale, MyAssembly" table="Address" lazy="false">
    <many-to-one name="AddressType" class="MyAssembly.BusinessObjects.NamedValue, MyAssembly">
      <column name="Type" property-ref="NumValue"/>
      <!-- What would be equivalent to this?: -->
      <column value="AddressType" property-ref="ListName"/>
    </many-to-one>
    ...
</class>

There is no value attribute for columns in hibernate, but what I am trying to do here is specify the "AddressType" value explicitly since it does not exist in the Address table.

I hope I have made the problem clear. If more information is needed please let me know. Any help is greatly appreciated.

Thanks.

wolli · **Posted:** Tue Aug 26, 2008 2:30 am

You can try and define a filter on the NameValueList entity. Then you can enable the filter in the session where you retrieve the data. The list name can be used as a parameter to the filter. Not the nicest way ford oing that, but the only one I can think of at the moment.

Have a look at http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/filters.html.

wmnelis · **Joined:** Fri Aug 22, 2008 4:07 pm **Posts:** 9

Thanks wolli for your idea. I tried it and I don't think it will work. I simplified the original example I gave, but in reality the Sale object needs to lookup several values from the NameValueLists table, not just the AddressType. Since the filter is added to the "class" instead of the "many-to-one" it looks like the filter would only let me set one value for the whole query (not one per named value list).

Any other ideas?

wmnelis · **Joined:** Fri Aug 22, 2008 4:07 pm **Posts:** 9

Ok, I have found a solution that works in the Java version of Hibernate. Here is what I have done in my hbm.xml files:

The lookup table:

Code:

<class name="businessObjects.NamedValue" table="NamedValueLists" lazy="false">

  <composite-id>
    <key-property name="_listName" column="ListName" type="string"/>
    <key-property name="_numValue" column="NumValue" type="short"/>
  </composite-id>

  <property name="_valueName" column="ValueName" type="string"/>
  ...
</class>

The table that is joined to the lookup table:

Code:

<class name="MyAssembly.BusinessObjects.Sale, MyAssembly" table="Address" lazy="false">
    <many-to-one name="AddressType" class="businessObjects.NamedValue">
      <formula>'AddressType'</formula>
      <column name="AddressType"/>
    </many-to-one>
    ...
</class>

The above does exactly what I need it to (but in Java, not .NET). Is there any way to do this in NHibernate? I have not been able to get it to work. I get the following error:

Code:

XML validation error: The element 'many-to-one' in namespace 'urn:nhibernate-mapping-2.2' has invalid child element 'formula' in namespace 'urn:nhibernate-mapping-2.2'. List of possible elements expected: 'meta, column' in namespace 'urn:nhibernate-mapping-2.2'.

The issue seems to be that the formula tag is not supported inside of the many-to-one tag. I am using NHibernate 2.0.

(Consult the original post for more information)

Thanks again for your help.

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

Last week, I've been searching to achieve something similar, but I haven't found how to do it.
I indeed found a solution which works in Hibernate, so I hope that it gets ported to NHibernate anytime soon. :)

Anyway, I'd love to see this functionality in NH as well . :)