Hi Randall - why not use a "calculated property" on Person ? You do the SQL for the "CurrentBed" property in the XML mapping layer. I thnik you need to to mark the property as "insert=false" and "update=false".
The property will of course be read-only. You will need to modify the Room_history table and reload the Person entity to update the "CurrentBed" property. So for Person you would add: <property name="CurrentBed" formula="select top 1 Room from Room_history where date_end is null and person_id = ID" /> The standalone "Id" in the formula will be resolved against the Id of the Person entity. Hope this helps. Thomas On 19 Nov., 17:18, rhsanborn <[email protected]> wrote: > I was hoping to tap the collective wisdom for an alternative to a join > with a condition. > > Here is my scenario: > > We have a long-term care application. I have a person entity. That > person should have a property indicating where they are currently > located (which bed they are in). Beds can (and do) change people > occasionally. The history of all the people who have been in any bed > in is in a table, including the current occupant. The current bed is > indicated by a null value in the "END_DATE" field of the bed table > (The bed/person relationship hasn't ended because the person is still > in the bed). So, the only way to get the current bed property is to > join the person table and the bed table on the person id where > end_date is null. > > Unfortunately, it looks like NHibernate doesn't support join > conditions. And I understand that this isn't the best design, but > we're writing against a vendor supplied application. > > Does anyone here have any examples of similar implementations and what > you've done to get around it? We're writing an application that > presents reports, and current live information in a nicer UI. This app > will be written against 2, possibly 3 different vendor databases > depending on the facility (they all use different tracking apps). So, > we've been trying to keep our entity design database agnostic. It > looks like that isn't possible, as I'm likely going to need to setup > some middle-men entities to represent the current structure. Any > ideas? > > Entity: > > public class person > { > public virtual int id { get; set; } > public virtual string name { get; set; } > public virtual room {get; set; } > > } > > Table Person: > ID Name Room > 1 Bob 132 > 2 Hal 203 > > Table Room_history > Room person_id date_start date_end > 132 5 1999/12/23 2003/5/13 > 132 8 2003/5/14 2008/1/21 > 132 1 2008/1/22 NULL > 203 23 1999/12/23 2002/3/30 > 203 2 2002/4/1 NULL > > - Randall -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
