Conditional join and mapping with NHibernate

I'm wondering how I might be able to map the following using NHibernate: I've got a User class, a Preference class, and a PreferenceOption class.

A User has many preferences (which in turn have preference options). The way the system works is that there is a default set of preference options for all users, and an individual user can override these default开发者_如何转开发s by specifying her own defaults. This way we only need to store the system defaults (represented by the Preference and PreferenceOption tables/classes) and any specific defaults for a user (represented by the UserPreferenceOption table/class).

Ideally what I'd like to do is map the PreferenceOption class so that it does a left outer join on UserPreferenceOption to include a user's specific defaults if present. Remember, a user may not have overridden the system default.

Preference

Id | Name

PreferenceOption

Id | Value | IsDefault | Preference_id

UserPreferenceOption

Id | UseThis | Preference_id | Option_id | User_id

The SQL query would look something like this:

select  PO.Id, PO.Value, PO.IsDefault, PO.Preference_Id, UPO.UseThis
from    PreferenceOption PO Left Outer Join UserPreferenceOverride UPO on PO.Id = UPO.Option_id
and UPO.Profile_id = THE_USER_ID

Is it possible to map this with NHibernate?

I don't really understand your SQL statement for this - but I have done something similar in NHibernate and it worked like this: you map the preference options and the user preference options to two seperate bags. Then make a logic that for every preference you first look into the user preference options and after that choose the default preference option.

Left outer joins are possible for queries in NHibernate: e.g. if you are using the criteria API it is createAlias("products", "product", Criteria.LEFT_JOIN) and in HQL it would be something like from Cat as cat join cat.mate as mate left join cat.kittens as kitten.

I hope this helps a little bit.