Suppose I have the following scenario:

I have offerblocks which contain one or more offeritems.
Each offeritem can be in any offerblock.

So I'm thinking many-to-many.
I created tables like this:

OfferBlock (id, name)
OfferBlock_OfferItem (offerblockId, OfferItemId)
OfferItem (OfferItemId, offerprice, etc)

This all works fine with the following mapping:



Now the change request:

People want to be able to SORT the offerItems in the block. So that particular offeritems go first when a block with items gets loaded.

So I'm thinking add a SortOrder column (int) in the OfferBlock_OfferItem table.

I can change my mapping for OfferBlock like this:

...


Works like a charm when I load the OfferBlock with Nhibernate! Because the offerItems are indeed sorted on the SortOrder column!

Now the problem:
In my database I have the three tables. But in my code I only have two classes (OfferBlock with an IList<OfferItem> and the OfferItem class)

Where or how should I add the SortOrder column as a field to my code-classes ??

Thanks,

I know only about hibernate so my apologies if this does not apply to NHiberante, but why don't you use something like array instead of set? This way you assign your natural array order to a column in database and then you will be able to modify the order if you like. I do believe there is a list structure also if you don't like arrays. In any events the mapping will look like this (taken from Hibernate documentation):




Bests,
Farzad-

Well, its a <bag ... /> not a <set .. />

Maybe I have to create a new class for the table that joins the offerblock and offeritem together, but that does not feel right.

I would end up doing something like:

<class "offerblock" ...table="offerblock" />
..
<bag table="offerblock_offeritem">
..
<one-to-many class="offerCollection"...>
</bag>
</class>

and:

<class="offercollection" table="offerblock_offeritem">

<property name="sortorder" column="sortorder".
<many-to-one name="Offer" class="Offeritem">
</class>

So than I would have replaced my two NHibernate mappings with many-to-many into three NHibernate mappings with a one-to-my and another many-to-one. I'm not sure if that would do any good.

I was thinking about something like that too but you may still want to try the list or array approach. Bag and set have the same story for this. My understanding is that semantically they are not supposed to be ordered and even in Java you will have to use a specific order Set class so that orders are respected.

Can you explain the <array... > element?

Do you mean that NHibernate will take care of filling the SortOrder column based on the order of my IList in code? If that is the case, than I would indeed not need to have the SortOrder as field in any class!

That's pretty much my understanding. All you need to do is to define your association as a list element or an array element and hibernate will load your child objects in the order defined in list-index element. This index will get updated each time the association is flushed to database.

if you ever tried this please reply to this post with the results. It is good for future references.

I just tested it.

Its working beautifully in NHibernate.

This is what was needed in NHibernate 1.20G

Replaced the BAG with the Array:

<array name="OfferItems" table="OfferBlock_OfferItem" cascade="all" >
<key column="OfferBlockId" />
<index column="SortOrder" />
<many-to-many fetch="join" column="OfferItemId" class="OfferItem, Namespace" />
</array>


In the SortOrder column, when I save the list, I see the 0...count of the list. Than indeed I can change the Ilist and re-save it after re-arranging. Thanks!

Aii, there is a problem after all.

READING the list from the database is ok.

But SAVING the list gives this error:

UnitTest.Offers.OfferData.OfferData.CreateDummyOffers : NHibernate.ADOException : Could not save object
----> System.InvalidCastException : Unable to cast object of type 'System.Collections.Generic.List`1[NameSpace.Offers.OfferItem]' to type 'System.Array'.

This is probably because the .NET IList<OfferItem> does not match the NHibernate <Array> item....

I also tried <IDBAG> but could not get the SortOrder column in.

Eventually I solved this by using a <MAP> instead of a <BAG>

The <MAP> inside the OfferBlock mapping file now looks like this:




I had to change the IList<OfferItem>
to a
IDictionary<int, OfferItem>

This way, the int of the dictionary gets filled with the value in column SortOrder.
This could have some influency on calling methods because they needed to handle the IDictionary instead of an IList, so I added methods to my code that exposes a List that sorts on the keys in the Dictionary like this:



...
}

This way the users of the OfferBlock object, can now use the .ReturnAllOffers method as view on the offers in the database.

If I want to create a new OfferBlock however, it must be done by creating a new Dictionary, whereby the keys of the dictionary is the sortOrder of all offers in the offerblock..