How to do ManyToMany Relationships with a Lookup Table Sort Order Field in Transfer

I recently had a use case in an application I'm writing where I needed to be able to sort some items which were referenced in a lookup table - modelled using ManyToMany in Transfer.

The relationship maps users to pages. It's possible to allocate the same pages to multiple users.

I initially had this relationship defined as follows (part of the 'users' object - not shown):

<manytomany name="Pages" table="lnkUsers_Pages" lazy="true">

     <link to="users.user" column="lnkIDUser"/>
     <link to="content.page" column="lnkIDPage"/>

     <collection type="array">
          <order property="id" order="asc"/>
     </collection>
</manytomany>

This all works fine and I'm able to call getPagesArray() from the users object and retrieve an array of page objects.

However, I also have the requirement to sort the returned pages based on a numeric 'sort order'. If the sort order was based upon the content pages - i.e. all pages would be sorted in the same way regardless of user allocation I could model this by modifying the collection:

<manytomany name="Pages" table="lnkUsers_Pages" lazy="true">

     <link to="users.user" column="lnkIDUser"/>
     <link to="content.page" column="lnkIDPage"/>

     <collection type="array">
          <order property="SortOrder" order="asc"/>
     </collection>
</manytomany>

 

This would sort the collection based upon the 'SortOrder' field in the content table.

However, my requirement is to be able to sort the results based upon a sort order field in the actual lookup table - 'lnkUsers_Pages'. The database table looks like this:

lnkIDUser
lnkIDPage
SortOrder

Unfortunately it is NOT possible to model this in a ManyToMany in Transfer - you can only sort by a property in the actual linked tables. So how do I achieve a sort based upon the 'SortOrder' field in the lookup table?

Well the solution is to forget the ManyToMany relationship and instead use a OneToMany in the user object that links to the lookup table which is defined as it's own object using composite keys. We then use a ManyToOne relationship from the lookup table object to the page object. The complete relationship definitions are as follows (including the user object):

<package name="users">

     <object name="user" table="tblUsers" decorator="model.users.user">
          <id name="id" type="numeric" />
          <property name="userName" type="string" column="userName" />
          <property name="password" type="string" column="password" />
               
          <!-- Link between a user and the pages they have access to -->
          <onetomany name="Pages" lazy="true">
               <link to="users.userPages" column="lnkIDUser"/>
                    <collection type="array">
                         <order property="sortOrder" order="asc"/>
                   </collection>
          </onetomany>
     </object>

     <object name="userPages" table="lnkUsers_Pages">               
          <compositeid>
               <property name="lnkIDUser"/>
               <property name="lnkIDContent"/>
          </compositeid>           
               
         <property name="lnkIDUser" type="numeric" column="lnkIDUser" />
         <property name="lnkIDContent" type="numeric" column="lnkIDContent" />
         <property name="sortOrder" type="numeric" column="SortOrder" />
               
         <!-- Link between a user and a content page -->
         <manytoone name="Page" lazy="false">
              <link to="content.content" column="lnkIDContent"/>
         </manytoone>

     </object>
</package>

<package name="content">
     <object name="content" table="tblContent" decorator="model.content.content">
     <id name="id" type="numeric" />
     <property name="title" type="string" column="title" />
     <property name="content" type="string" column="content" />
</package>

By using this relationship I am able to sort the pages collection by the 'SortOrder' field in the lookup table. This is possible as the lookup table itself is now an object with defined properties that we can access via the OneToMany relationship in the users object.

The only thing to be aware of with this setup is that the array you get back from getPagesArray() is composed of 'userPages' objects. You therefore need to call getPage() on each object in the array to access the page. E.G:

     <cfset userPages = getUser().getPagesArray()>
     <cfloop from="1" to="#ArrayLen(userPages)#" index="x">
          <cfset page = userPages[x].getPage()>
          <cfoutput>#page.getContent()#</cfoutput>
     </cfloop>

This is a flexible way to achieve a ManyToMany type relationship without having to use a ManyToMany composition. It allows you to add any fields you need to the lookup table and have access to them via a business object as well as being able to sort on any field in that table.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
John's Gravatar I was looking for this for hours. I tried your solution but it something wrong.. I think it caches the first row fields ( i.e: SortOrder). Any idea?
# Posted By John | 9/4/10 12:16 PM
James Allen's Gravatar Hey there John.

Brilliant spot! I had missed that in testing as I had reload on in MG so it never showed itself. I am looking into this now. No joy so far but once I have an answer I'll add a comment here and update the post.

Thanks again for letting me know.
# Posted By James Allen | 9/6/10 2:25 PM
© 2014 James Allen | Contact Me
This blog runs on the awesome power of BlogCFC - created by Raymond Camden. This blog is running version 5.9.