Home → Help and guidance →

HasManyToMany and AsList creates a default index; how to order on it?

samy

26 Apr, 2010 01:52 PM via web

Admin Functions

• Edit
• Remove

Hello everybody,

i'm having a bit of problem mapping two classes. I have a Cart object that contains Declarations and a Declaration can be found in many Carts
I'm interested in keeping the order in which each declaration is put in a cart, so i decided on the following mapping

Public Class PanierDeclarationsMapping
Inherits EntityUpdateTraceMapping(Of PanierDeclarations)

Public Sub New()
    ' snip
    HasManyToMany(Of Declaration)(Function(p As DALMusicien.PanierDeclarations) p.Contenu).Not.LazyLoad.Cascade.None().Table("LiensPanierDeclaration").AsList() ' Contenu is a ICollection(of Declaration) that wraps a List(of Declaration)
End Sub
End Class

and

Public Class DeclarationMapping
Inherits SCPPEntityUpdateTraceMapping(Of DALMusicien.Declaration, String)

Public Sub New()
 ' snip
    HasManyToMany(Of PanierDeclarations)(Function(d As Declaration) d.Paniers).AsSet.Cascade.None().Inverse.Table("LiensPanierDeclaration")

End Sub
End Class

These two classes give the following hbm

 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="none" default-lazy="true">
 <class xmlns="urn:nhibernate-mapping-2.2" name="DALMusicien.PanierDeclarations, DALMusicien, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`PanierDeclarations`">
 <!-- snip -->
 <list cascade="none" lazy="false" name="Contenu" table="LiensPanierDeclaration">
  <key>
    <column name="PanierDeclarations_id" />
  </key>
  <index />
  <many-to-many class="DALMusicien.Declaration, DALMusicien, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
    <column name="Declaration_id" />
  </many-to-many>
 </list>
 </class>
 </hibernate-mapping>

and

 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="none" default-lazy="true">
 <class xmlns="urn:nhibernate-mapping-2.2" name="DALMusicien.Declaration, DALMusicien, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Declaration`">
 <!-- snip -->
 <set cascade="none" inverse="true" name="Paniers" table="LiensPanierDeclaration">
  <key>
    <column name="Declaration_id" />
  </key>
  <many-to-many class="DALMusicien.PanierDeclarations, DALMusicien, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
    <column name="PanierDeclarations_id" />
  </many-to-many>
 </set> 
 </class>
 </hibernate-mapping>

As you can see, there is an order column automatically added to the PanierDeclarations class, which is correctly filled with the order in which the declarations are inserted in it.
However i can't seem to be able to reference this "anonymous" column in the mapping, and any join on the two tables returns different results. I tried to add a OrderBy attribute to the PanierDeclarations.Contenu mapping but since a column must be specified, i don't know how to do it.

Has anyone got an example of this kind of sorting for fluent nhibernate?

Thank you for reading, and for this wonderful tool (even if i'm still struggling :p)

1. 2 Posted by Devon Lazarus on 05 May, 2010 03:06 PM

   

   In c#, sorry. Have you tried this?

   HasManyToMany<Declaration>(x => x.Declarations)
       .AsList(p =>
           {
               p.Column("ThisIsMyIndexColumn");
           });
   

   Not a VB programmer but it might look like this?

   HasManyToMany(Of Declaration)(Function(p As DALMusicien.PanierDeclarations)
           p.Contenu)
       .Not.LazyLoad
       .Cascade.None()
       .Table("LiensPanierDeclaration")
       .AsList(Function(p As System.Action)
           p.Column("ThisIsMyIndexColumn"))
   

   Sorry if that's way off base. I haven't touched VB in many years...

   -devon

   Admin Functions

   • Edit
   • Split
   • Remove

2. 3 Posted by samy on 06 Jul, 2010 09:12 AM

   

   Sorry Devon, i went on a pretty urgent assignment and wasn't able to get back to the project i've been talking about in my question for a time.

   In fact, i realize i've been very imprecise in my question. As a matter of fact, what you offer as a solution works, and it worked all along (albeit by creating a column named 'idx' which looks like it's been changed to 'index' in the latest builds)

   How it works is that the joining table is created with three columns, elementA_id, elementB_id, and the index. Then a primary key is created on elementA_id and index, ensuring that the sort order is kept automagically when the query runs. One thing's sure though, the ordering is not transferred into the resulting hbm, at least when using a list; but as i said, some testing shows that this looks like it's working

   However, i didn't state my problem properly. Even though the index column works properly when i query the list after loading the first object, i cannot seem to be able to query it when i create more complex queries like joining between tables. Let's go for another example; sorry, it's still vb...

   We have the following three classes that are chained together Owner has many Owned, which in turn have many OwnedThings

   Public Class Owner
       Public Overridable Property id() As Integer
       Private m_ListOwned As IList(Of Owned)
   End Class
   
   Public Class Owned
       Public Overridable Property id() As Integer
       Public Overridable Property ListOwner() As IList(Of Owner)
       Public Overridable Property Thingies() As IList(Of OwnedThings)
   End Class
   
   Public Class OwnedThings
       Public Overridable Property Id() As Integer
   End Class
   

   Their fluent mapping is the following
   

   Public Class OwnerMapping
       Inherits ClassMap(Of Owner)
       Public Sub New()
           Id(Function(o As Owner) o.id).GeneratedBy.Assigned()
           HasManyToMany(Of Owned)(Function(o As Owner) o.ListOwned).AsList(Function(ip As FluentNHibernate.Mapping.IndexPart) ip.Column("OwnedOrder")).OrderBy("OwnedOrder")
       End Sub
   End Class
   
   Public Class OwnedMapping
       Inherits ClassMap(Of Owned)
       Public Sub New()
           Id(Function(o As Owned) o.id).GeneratedBy.Assigned()
           HasManyToMany(Of Owner)(Function(o As Owned) o.ListOwner).AsList(Function(ip As FluentNHibernate.Mapping.IndexPart) ip.Column("OwnedOrder")).OrderBy("OwnedOrder").Inverse()
           HasMany(Of OwnedThings)(Function(o As Owned) o.Thingies).AsList(Function(ip As FluentNHibernate.Mapping.IndexPart) ip.Column("ThingiesOrder")).OrderBy("ThingiesOrder")
       End Sub
   End Class
   
   Public Class OwnedThingsMapping
       Inherits ClassMap(Of OwnedThings)
       Public Sub New()
           Id(Function(ot As OwnedThings) ot.Id).GeneratedBy.Assigned()
       End Sub
   End Class
   

   I'm not including their full hbm, i'll concentrate on the hasmanytomany part
   For the owner:
   

   <list name="ListOwned" table="ListOwnedToListOwner" mutable="true">
     <key>
       <column name="Owner_id" />
     </key>
     <index>
       <column name="OwnedOrder" />
     </index>
     <many-to-many class="DALMusicien.Owned, DALMusicien, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
       <column name="Owned_id" />
     </many-to-many>
   </list>
   

   Notice how there is no ordering on the many-to-many tag
   For the owned:
   

   <list inverse="true" name="ListOwner" table="ListOwnedToListOwner" mutable="true">
     <key>
       <column name="Owned_id" />
     </key>
     <index>
       <column name="OwnedOrder" />
     </index>
     <many-to-many class="DALMusicien.Owner, DALMusicien, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
       <column name="Owner_id" />
     </many-to-many>
   </list>
   

   When i load the owner, it works as i said above; the primary key containing the ordering column loads everything in order. But it breaks when i create more complex queries. For example, i'd like to query every Owner which contains Owned objects that have a special status (let's say id above 7 to keep it simple), and the count of their Things.

     Dim pList As NHibernate.Criterion.ProjectionList = NHibernate.Criterion.Projections.ProjectionList
   pList.Add(NHibernate.Criterion.Projections.GroupProperty("ownR.id"))
   pList.Add(NHibernate.Criterion.Projections.GroupProperty("ownDBack.id"))
   pList.Add(NHibernate.Criterion.Projections.Count("ownT.id"))
   
   Dim l As IList = Session.CreateCriteria(Of Owned)("ownD") _
           .Add(Criterion.Restrictions.Gt("ownD.id", 7)) _
           .CreateCriteria("ownD.ListOwner", "ownR") _
           .CreateAlias("ownR.ListOwned", "ownDBack") _
           .CreateAlias("ownDBack.Thingies", "ownT") _
           .SetProjection(pList) _
           .SetResultTransformer(New Transform.AliasToBeanResultTransformer(GetType(OwnerDTO))) _
           .List()
   ' OwnerDTO is a class with the three properties we're interested in
   

   This gives me the following sql

   SELECT ownr1_.id as y0_, owndback2_.id as y1_, count(ownt3_.Id) as y2_ FROM "Owned" this_ inner join ListOwnedToListOwner listowner5_ on this_.id=listowner5_.Owned_id inner join "Owner" ownr1_ on listowner5_.Owner_id=ownr1_.id inner join ListOwnedToListOwner listowned7_ on ownr1_.id=listowned7_.Owner_id inner join "Owned" owndback2_ on listowned7_.Owned_id=owndback2_.id inner join "OwnedThings" ownt3_ on owndback2_.id=ownt3_.Owned_id WHERE this_.id > @p0 GROUP BY ownr1_.id, owndback2_.id;@p0 = 7
   

   There i lose the benefit of the primary key containing OwnedOrder, since the joins are not keeping it. But i can't specify an ordering on this column since it doesn't belong in either object. How would i explicitly sort on OwnedOrder?

   Admin Functions

   • Edit
   • Split
   • Remove

3. 4 Posted by samy on 05 Aug, 2010 12:31 PM

   

   I know we're in the middle of holidays for many people but if any code sample or more detailed explanations can help point me in the right directions, i'm all for it :) I've been picking at the problem left and right, but can't find a way to make it behave... and the "real world" problems loom around me :)

   Admin Functions

   • Edit
   • Split
   • Remove