Handling a many-to-many self join relationship

Mapping an ORM over a simple relational schema is very straightforward, but real life is never simple. Many-to-many self joins take a bit more effort to set up in an ORM but when it's done right it really saves a lot of future effort and complexity.

(Click here for a brief overview on using Activerecord models.)

Relational Design

The simplest relational design for such a situation can be boiled down to the chart below:

A sample schema to handle many-to-many self joins.

In this example, any user can be a vendor to many customers, and any user can be a customer to many vendors. You need to make a distinction between the types of users, but how? And how would I select only certain relationships based on other properties?

Defining the Models

The model definition will use a has many through (HMT) association. As a side note, I won't discuss this comparison in detail since it's beyond the scope of this post, but HMT is very similar to has-and-belongs-to-many (HABTM), and both allow for many-to-many relationships. That said, HMT was chosen because it allows for the developer to work with any additional properties in the association data (in this case, the Relationship model).

User model definition

For this case, we will not want to always directly access our users collection; we may want to use users in context. The User model has been set up so it can be accessed through use of aliases. I'll illustrate later how you can access a user's customers and vendors from within the User controller or an external Ruby script executed using Rails runner.

Relationship model definition

The Relationship model provides the ability to define the relationship between the two classes of Users.

Accessing the Model data

All the scaffolding in the Models definitions leads to ease of use for the developer.

Querying for a User's customers.

Querying for a User's customers querying against a relationship table's properties.

If you want to query for a User's vendors simply replace the .customers alias method call with .vendors.

In my next post, I'll show how to expose this data in a RESTful API endpoint.