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.

How to debug a Rails runner script in Jetbrains RubyMine

For anyone using an IDE debugging and stepping through a script is generally a trivial affair. But what if your script isn't directly run by the interpreter?

Right now I'm working on a ruby script that needs to get access to the resources I built in my rails app; specifically, I want to use the activerecord model AND the rails database connector. The solution was to write a ruby script that was called by rails runner.

I had an issue where during my testing, I thought there were some puts calls that were being skipped for some unknown reason, and I wanted to debug and step through the area in question. The most popular solution on Google and SO was to use puts statements followed by a ton of bundler.pry calls. Compared to other languages (where I could step in, over, and out of code blocks), this was very cumbersome. I was about to go down this path, when...

Someone asked if I had used the Jetbrains Rubymine debugger. I did try it but it didn't seem obvious as to how I could configure it to handle my situation. Then JB was nice enough to post this video and voila! Now because I'm one of the few schlubs on this planet that write my rails app on Windows, I had to spend another couple of minutes figuring out some additional things but I got it working!

Below is a screenshot of my working debug config.

How to use SSH without PuTTY in Windows 10

PuTTY has been the go-to terminal/SSH software for Windows users pretty much forever, and for the vast majority of users it does the job and does it well enough. But I use CentOS as my desktop OS at work, and one of the things I did was to create shell scripts that would transparently execute remote GUI tools. And I placed them on my Gnome desktop so I could double-click on them and they'd behave like local applications. That's not possible using PuTTY and Windows - you need to log in then call the command at the prompt.

I apologize for the blasphemy I'm about to reveal... I actually like Windows 10. MS actually did a good job with UI/UX this time around, and I have no plans on replacing it with CentOS or Ubuntu as my desktop OS (unless my wife [one of these days] lets me buy a MacBook Pro). But I do like the trick I set up on my work desktop with setting up my remote tools as icons. After some thought, I came up with something very similar for Windows, using SSH tunnelling to access remote Linux tools, and without having to use a terminal tool like PuTTY to explicitly provide a password for each software session.

Software needed

• X server: I imagine any package will do, but I switched over to VcXsrv last year. I had used Xming for ages, but it's ancient and the free version hasn't been updated in a long time. And I don't use cygwin because I don't use all the other cruft that comes with it.  That said, pick what you like best. Download VcXsrv here: https://sourceforge.net/projects/vcxsrv/
• Git for Windows: the bash window that comes with git is the big reason why I don't use cygwin. It gives me what I need in a thin package with tools that work transparently through Windows. Download git here: https://git-scm.com/downloads

Install X server

Installing VcXsrv is pretty straightforward, and Xming was similarly easy to install as well. Visit here for installation details. If you want X to start when you log in, go to C:\Users\<your username>\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup and make a shortcut to the executable:

For my startup shortcut I run the executable with the following options:

"C:\Program Files\VcXsrv\vcxsrv.exe"  :0 -ac -terminate -lesspointer -multiwindow -clipboard -wgl

A list of available options can be found here: https://gist.github.com/stowler/9921780

Install git

If you're not a software developer, you really won't have a need for git. And sure you can use mingw if you really want to start with a minimalist environment. But the git package provides SSH and openssl tools by default, and that will save loads of time and effort configuring those tools. (Personally I think git is the best thing since sliced bread.)

During installation you will be prompted to select your preference for using git in either bash or the Windows command prompt. Being more of a Unix person, I chose bash. I have no idea if anything I've done in bash can be done in the command prompt, and (since everything works the way I like it) I don't care.

Create and deploy SSH keys

The use of SSH keys is the secret sauce to this entire effort; it allows for you to log into your Unix/Linux machine without having to repeatedly log in and providing username and password. Details on how to do this are available here, but to summarize the process (using git bash):

1. Create your keys: ssh-keygen -t rsa. Do not provide a passphrase when prompted.
2. Deploy your key to remote: ssh-copy-id -i ~/.ssh/id_rsa.pub username@remote_host

Create Windows batch file

I use the code below in my batch file to combine a couple of commands into a single call that I can link a shortcut to on my desktop:

@echo off
set DISPLAY=0.0.0.0:0.0
C:\Progra~1\Git\usr\bin\ssh.exe -YC username@remote_host "dbus-launch gnome-terminal --geometry=132x43 &"

SSH uses the keys made in the previous step to log into the remote host, and it then passes the command in quotes to run from the remote host.

Create and modify desktop shortcut

Create a shortcut to the batch file on your desktop. Once done, open the properties window:

And make sure Run is set to "minimized". If this is not set and you run the command, a command window will open in addition to the software you actually want to execute.