Handling self-joins in a RESTful endpoint

In my previous post, I set up a sample scenario where you can programmatically look up a user's customers or vendors using a many-to-many self-join relational schema. The logical next step is to provide a way for external users to filter and access this data from a user-facing application.

If you're using Rails as your platform to expose a RESTful API, those URL resource endpoints generally reflect object models that map to a corresponding database table. And it's common practice to refer to child resources as part of a parent resource's path (e.g. /author/id/books ie access all the books for a given author). In this scenario, our child resource does not reflect an actual object model (e.g. /user/id/customers). We don't have an actual customer or vendor table; both resources would refer back to the User model.

Add new Member Routes

The first thing to do is to add new member routes to your routes.rb file. For us, we would add customers and vendors member routes to the users collection route:

Defining a member routes in a parent collection.

This definition recognizes a GET request to, say, /users/1/customers and it maps this route to the UsersController#customers action in the Users controller. The Users resource ID value is passed in params[:id].

Add a new Action

We'll need to add a new action for each of the member routes. For brevity, we'll only define the customers action; making the vendors action will be left as an exercise for the reader. In brief, the code used in the previous blog post illustrating how to access the model is used for the action definition.

Defining a new action in the User controller.

As part of the action definition, a user can include an optional is_premier_customer parameter in the GET request (e.g. /users/1/customers?is_premier_customer=true).

Add a new template

For the final step, we need to add a new template file to handle the new action. For this example, we're going to make a template file that's parsed by jBuilder template engine to generate JSON data. (jBuilder is used by default if you tell the Rails 5 application generator to set up a RESTful API application.)

We'll make a new file called customers.json.jbuilder defined with the following line:

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.