If you are using Mura ORM, you may have stumbled across the fact that, while "one-to-many "and "many-to-one" relationships are natively available, "many-to-many" relationships are not.

I have used Mura ORM for years, but this never occurred to me, simply because I've always handled those relationships in other ways. In this article, I'll explain this limitation in more detail, and provide a way to work around it using existing Mura ORM functionality.

It is important to remember that Mura ORM was never intended as a fully-functional equivalent to Hibernate-style ORM. It was originally implemented to unify the Mura core by standardizing all the core Mura objects (content, users, components, etc.), and to work around some limitations of Hibernate, which does not always play well in a mixed-application environment (e.g. Mura, Plugins and Custom Modules running under a single cfml application). Since then, Mura ORM has matured into a robust toolset for building tightly integrated custom applications and extensions, as well as speeding development by offering a shared-environment way to use familiar Mura tools like feed() and iterator() via an ORM methodology. 

There is a great deal of documentation (v6, v7) available on the topic, and I highly recommend you take a moment to review it, as Mura ORM provides a lot of powerful functionality.

One-to-Many/Many-to-One

In a one-to-many relationship, bindings between Mura ORM objects are relatively straightforward. In the example below, the child table will have a "parentid" field, and every child will have an id in that column that references its parent. The code that makes this happen is pretty simple.

component
    extends="mura.bean.beanORM"
    entityname="person"
    table="d_person"
    {
        property
          name="personid";
          fieldtype="id";
          
        property
          name="contacts";
          fieldtype="one-to-many";
          relatesto="contact";
          
        property
          name="name";
          datatype="varchar";
          fieldtype="index";
          length="50";

}

component
    extends="mura.bean.beanORM"
    entityname="contact"
    table="d_contact"
    {
        property
          fieldtype="id";
          name="contactid";
          
        property
          name="person";
          fieldtype="many-to-one";
          relatesto="person";
          fkcolumn="personid";
          
        property
          datatype="varchar";
          fieldtype="index";
          length="50";
          name="name";
}

In this case, Mura ORM would create two tables, "d_person" and "d_contact", and the "d_contact" table would have a column "personid", which Mura ORM would bind to "Person". The "fkcolumn" in the "Person" object is there only for clarity. If you omit it, Mura will simply use the primary key (aka personid) when creating the table. You only need to use fkcolumn if the linking id is different than the parent's primary key.

Mura ORM just gets better and better. Here's how to use many-to-many relationships, simply explained!

Many-to-Many

In a many-many relationship, there must be some kind of a binding, in-between object (e.g. Parent, Child, and a "ParentChild" which connects them).

Let's take a look at how we might build a many-many relationship to Authors and Books (as an author can have many books, and a book can have multiple authors):

component
    extends="mura.bean.beanORM"
    entityname="book"
    table="d_book"
    {
        property
          fieldtype="id";
          name="bookid";
          
        property 
          name="bookauthor";
          fieldtype="one-to-many";
          relatesto="bookauthor";
      
        property 
          name="title";
          datatype="varchar"
          fieldtype="index";
          length="50";

        property
          name="isbn";
          datatype="varchar";
          fieldtype="index";
          length="30";

}

component
    extends="mura.bean.beanORM"
    entityname="author"
    table="d_author"
    {
        property
          name="authorid";
          fieldtype="id";
 
        property 
          name="bookauthor";
          fieldtype="one-to-many"
          relatesto="bookauthor"

        property
          name="firstname";
          datatype="varchar"
          fieldtype="index"
          length="50";
      
        property
          name="lastname";
          datatype="varchar"
          fieldtype="index";
          length="50";

}

component
    extends="mura.bean.beanORM"
    entityname="bookauthor"
    table="d_bookauthor"
    {
        property 
          name="bookauthorid";
          fieldtype="id";

        property
          name="author";
          fieldtype="many-to-one"
          relatesto="author";
          
        property
          name="book";
          fieldtype="many-to-one";
          relatesto="book";
}

You can use functions like author.getBookAuthorIterator() or book.getBookAuthor() or get the relationships, and then use an iterator to retrieve the related objects. I usually create a custom function to encapsulate this into a single call, but you can do this outside of the object as well.

// in the book.cfc component

function getAuthorArray() {
  // this returns an iterator of all the related "bookauthor" objects.
  var bookAuthorIterator= this.getBookAuthorIterator();
  var authorArray = [];

  //now we loop through "bookauthor", each of which is related to an author, and get this "author" object
  while(bookAuthorIterator.hasNext()) {
     var item = bookAuthorIterator.next();
     ArrayAppend(authorArray,item.getAuthor());
  }

  return authorArray;
}

If you want to retrieve an iterator of the related objects, you could do something like this:

// in the book.cfc component

function getAuthors() {
    // returns an iterator of all the related "bookauthor" objects.
    var bookAuthorIterator= this.getBookAuthorIterator();

    // creates a list of authorid based upon the iterator
    var bookAuthorQuery = this.bookAuthorIterator.getQuery();
    var bookidlist = valuelist(bookAuthorQuery.authorid);
    var m=getBean('m').init(this.getValue('siteid'));

    //gets all of the authors within the list of authorids
    var authors = m.getFeed('author')
      .where()
      .prop('authorid')
      .isIn(bookidlist);
      .getIterator();

    return authors;
}

This is a little more complicated than using only Mura ORM getters, but it has the advantage of only doing only a single query to get all of the children (where using only the iterator and .getAuthor() would invoke a query for each author). This is the standard trade-off of ORM legibility and code efficiency vs. performance. It's up to you to decide which approach works best for your project and goals. 

Also See

Mura 6 ORM Documentation: docs.getmura.com/v6/back-end/mura-orm/
Mura 7 ORM Documentation: docs.getmura.com/v7/mura-developers/mura-beans-objects/custom-objects/mura-orm/
ORM Bootcamp (blog): getmura.com/blog/mura-orm-boot-camp-instantiation-relationships/
Webinar: Super Fast Application Development with Mura 7
Download or Contribute to Mura CMS on Github: github.com/blueriver/muracms
Mura Information, Blog, Support, and More: getmura.com