These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: many-to-one with "hard coded" value
PostPosted: Mon Aug 25, 2008 11:47 am 
Newbie

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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 26, 2008 2:30 am 
Expert
Expert

Joined: Thu Dec 14, 2006 5:57 am
Posts: 1185
Location: Zurich, Switzerland
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.

_________________
--Wolfgang


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 26, 2008 1:57 pm 
Newbie

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?


Top
 Profile  
 
 Post subject: Found a solution in the Java version of Hibernate
PostPosted: Tue Sep 02, 2008 1:02 pm 
Newbie

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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 02, 2008 4:12 pm 
Regular
Regular

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 . :)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.