Adding a property to a manytomany join in Transfer ORM (Coldfusion)

I am using transfer and I have a ManyToMany relationship between articles and videos. What I need to be able to do is to stick a timestamp against each video when it is added to an article. i.e. I have two tables:

article (ID, title, body)
video (ID, url)

I then have a linked table:

article_videos(articleID, videoID)

I need to add in an extra column timeStamp to article_videos:

article_videos(articleID, videoID, timeStamp)

The problem I have is that when I try and create an extra property in the link table it does not work.

My Transfer ORM configuration:

<package name="article">
    <object name="Article" table="article">
        <id name="ID" type="numeric"/>
        <property name="Title" type="string" column="title"/>
        <property name="Body" type="string" column="body"/>

        <manytomany name="Videos" table="article_videos">
            <link to="article.Atricle" column="articleID"/>
            <link to="assets.Video" column="videoID"/>
            <collection type="array">
                <order property="OrderIndex" order="asc"/>
            </collection>
            <property name="TimeStamp" type="timestamp" column="timeStamp"/>
        </manytomany>
    </object>
</package>

<package name="assets">
    <object name="Video" table="video">
        <id name="ID" type="numeric"/>
        <property name="url" type="string" column="url"/>
    </object>
</package>

The problem is that the new property inside the ManyToMany is not allowed, it throws an error saying that the Transfer configuration is malformed.

Where and how should I add the timestamp baring in mind that the timestamp needs to be for that video in that article as the video may be used in multiple articles?

Thanks in advance.

Solution

What you will likely have to do is create a new object for your article_videos join.

Because Transfer ORM handles many-to-many joins transparently so you don't directly interact with the join table if you wanted to add and access additional properties on the join you will need to create a new object. There are a couple of ways to achieve this.

If you still wanted to handle the many-to-many relationship transparently and the timestamp will be automatically populated by the database you can keep the relationship as is and add a new object representing article_videos and a new relationship joining that object to both article and video objects.

So you would add a new object representing the article_videos, I might also add a surrogate key to the database or you might want to use a composite ID:

<object name="ArticleVideo" table="article_videos">
  <id name="ID" type="numeric"/>
  <property name="TimeStamp" type="timestamp" column="timeStamp"/>
</object>

Then you would update your Article object to reference this new object:

<object name="Article" table="article">
  <id name="ID" type="numeric"/>
  <property name="Title" type="string" column="title"/>
  <property name="Body" type="string" column="body"/>

  <manytomany name="Videos" table="article_videos">
    <link to="article.Article" column="articleID"/>
    <link to="assets.Video" column="videoID"/>
    <collection type="array">
      <order property="OrderIndex" order="asc"/>
    </collection>
  </manytomany>

  <onetomany name="ArticleVideo">
    <link to="article.ArticleVideo" column="articleID"/>
    <collection type="array">
      <order property="TimeStamp" order="asc"/>
    </collection>
  </onetomany>
</object>

And you would also update your Video object:

<object name="Video" table="video">
  <id name="ID" type="numeric"/>
  <property name="url" type="string" column="url"/>

  <onetomany name="ArticleVideo">
    <link to="article.ArticleVideo" column="videoID"/>
    <collection type="array">
      <order property="TimeStamp" order="asc"/>
    </collection>
  </onetomany>
</object>

This way you can use the Article to Video object relationship as normal but access the additional properties if you need to:

// Creating the join using the many-to-many relationship
article = transfer.get("article.Article", 1);
article.addVideo(video);

You can also then access the join, how you get the information to correlate the above relationship to the join might require a bit of work, so you will likely have to decide up front whether the join you are creating you will want more info:

// Creating the join using the ArticleVideo object
articleVideo = transfer.new("article.ArticleVideo");
articleVideo.addParentArticle(article);
articleVideo.addParentVideo(video);
transfer.save(articleVideo);

// Using a composite ID to access the ArticleVideo
articleVideo = transfer.get("article.ArticleVideo", {
  "ArticleID" = 1,
  "VideoID" = 1
});
WriteOutput("The timestamp is: #articleVideo.getTimeStamp()#");

Otherwise you can adjust all of the relationships, but this will require you using an intermediate object between your videos and articles. If it is just a timestamp, then it may be unnecessary.