Building Self-Joins and Triple-Joins in Ruby on Rails
Step-by-step instructional for creating a Ruby on Rails app that implements a self-join, then triple-join relationship
This article is a continuation of The Coddfather: Relational Database Fundamentals, which covers one-to-many, many-to-many, and many-to-many self-join relationships.
Whereas the first article is more conceptual and descriptive, this article is meant as a step-by-step instructional for creating a Ruby on Rails app that implements a self-join, then triple-join relationship.
In this article, we’ll create the back end of a movie comparison app. We should, by the end, be able to create a comparison between two movies (i.e. one is better than the other), then relate that comparison to a single user.
In doing so, we’ll be able to track which users prefer which movies over others.
Part 3 of this article series is a front-end demonstration of everything we create in the back end throughout this article.
Table of Contents
- Create a Rails app.
- Create models and migrations.**
- Define model relationships and test.**
Part 3 (Movie Comparison Website in Ruby on Rails):
- Routes, controller actions, views.
- Form to create instance of triple-join comparison.
* * — Models, relationships, and database migrations are the three most important parts of this article series as a whole. Together they are the force behind our self-join and triple-join relationships. Focus there.
Here is the Github associated with this article series.
If you’re interested in seeing how these concepts can eventually be applied, give this movie recommendation website a visit:
Website (will take few seconds for Heroku to load): MyMDb
Let’s do it.
Create a Rails App
I’m assuming you’ve installed both Ruby and Rails.
First, let’s create a new Rails app from the terminal. Navigate to the directory in which you want to store your new project. For me:
$ cd Desktop/Dev/
Generate a new Rails app from the command line with a relevant name:
$ rails new movieComparisonApp
Navigate inside the newly created directory:
$ cd movieComparisonApp/
When you open your new app in your preferred text editor, expect to see these files:
We will be working in: app (model) and db (database, seeds).
Create Models and Migrations**
Create a model for a movie with the appropriate database table columns:
$ rails g model Movie title year:integer
Note: I will be using Rails generators throughout.
This table migration should have been created:
(For clarity when we’re working in the console, I’m deleting t.timestamps.
A movie model should have also been created in app/models/movie.rb:
Create the comparison model:
$ rails g model Comparison
Auto-generated table migration:
Note: There are no columns defined yet. We’ll do that in a minute.
Auto-generated comparison model in app/models/comparison.rb:
Define Model Relationships and Test**
Before we define our relationships, let’s write out what we want our table in the database to eventually look like:
We want movie 3 to be “inferior to” movie 1, and “superior to” movie 2.
This table follows the typical structure of a has-many join table (see part 1 for two has-many join table examples, based on the structure of Facebook).
The first column defines each unique instance of the join (i.e. the comparison). The second and third columns contain numbers that correspond to IDs from other tables. Those “other tables” are connected through this table.
What’s unique about our case is that there is neither a table called “Inferior Movies” nor one called “Superior Movies.” There is only one table: “Movies”.
Our job, then, is to convince our database that there are, in fact, two tables. If we can do that, we’ve successfully created a self-join.
Let’s do that right now in the comparison model.
Reminder: Models control the flow of data in and out of our database. Every table in the database needs a corresponding model.
Plain-English translation of the belongs_to statements:
- Each new comparison you create contains an ID from the “Superior Movie” table. Heads up: “Superior Movies” are all actually just “Movies”.
- Each new comparison you create contains an ID from the “Inferior Movie” table. Heads up: “Inferior Movies” are all actually just “Movies”.
The two belongs to relationships together tell Rails that every single comparison created will necessarily have a single inferior movie and a single superior movie attached.
That one-to-one-to-one (movie-to-comparison-to-movie) relationship makes it possible for a single inferior movie to see its attached superior movie over the “bridge” of a comparison, and vice versa.
Now, we need to create the columns in which to put those “inferior movie” and “superior movie” IDs.
Navigate to your table migration for comparisons db/migrate/#######_create_comparisons.rb and insert these two lines (each of which equates to a single “column” in the database):
foreign key: true means that Rails will always be looking to associate the integer in the superior_movie_id and inferior_movie_id columns with an ID from an existing table. We’ve already told Rails which table in the model, via our belongs to statements (class_name: “Movie”).
With these two foreign keys declared, both of our belongs_to relationships (one from comparison to “Superior Movies” and the other from comparison to “Inferior Movies”) are solidified. Our join table has been created and we can test its powers in the terminal.
First, migrate the tables and create a seeds file:
$ rails db:migrate
Your schema.rb file should now look like this:
Now that the database exists, we are able to create new movies and comparisons, as well as, within a comparison, retrieve Inferior and Superior Movie objects.
Test these facts by creating a seed file in db/seeds.rb containing example data:
Note: Movie.destroy_all and Comparison.destroy_all are good practice. You don’t want to accidentally reseed your database with identical information. By destroying all previous instances, we ensure that we’re starting from scratch.
- Seed the database:
$ rails db:seed
- Open Rails console:
$ rails c
- View all created movies and comparisons:
Comparison.all and Movie.all
For both, you should get an array of objects in return.
- Retrieve the first comparison:
firstComparison = Comparison.first
- Expect to see an object returned:
=> #<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3>- Test to see that the comparison is correctly finding its associated inferior and superior movies.
firstComparison.inferior_movie => #<Movie id: 3, title: "Citizen Kane", year: 1941>firstComparison.superior_movie => #<Movie id: 1, title: "The Dark Knight", year: 2008>
If you get similar responses, well done. You’ve created a functioning join table.
However, our goal is to see “over the bridge of a comparison”. That is, from a particular movie, we want to be able to see all movies which have been declared superior and/or inferior.
This does not yet work:
firstMovie = Movie.first => #<Movie id: 1, title: "The Dark Knight", year: 2008>firstMovie.inferior_moviesNoMethodError (undefined method `inferior_movies' for #<Movie id: 1, title: "The Dark Knight", year: 2008>)firstMovie.superior_moviesNoMethodError (undefined method `superior_movies' for #<Movie id: 1, title: "The Dark Knight", year: 2008>)
For both, we receive an undefined method error. Let’s go define those methods and their helper methods now in the movie model.
First, the helper method:
Translation: Each movie created appears many times in the superior_movie_id column of the comparison table.
Let’s call that collection of comparisons Superior Comparisons, because we’re looking at all comparisons in which a movie is considered “Superior.”
Test this in the console:
firstMovie = Movie.first (first.id => 1)firstMovie.superior_comparisons=> #<ActiveRecord::Associations::CollectionProxy [#<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3>]>
firstMovie.superior_comparisons returned a collection of comparisons in which the first movie’s ID appears in the superior_movie_id column.
That’s nice, but not what we want. We want to see all movies that are worse than firstMovie. Fortunately, all movies worse than firstMovie are already contained in the superior_comparisons method we just tested:
firstMovie.superior_comparisons=> #<ActiveRecord::Associations::CollectionProxy [#<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3>]>
Therefore, we can access all inferior_movies through the superior_comparisons method:
To reiterate: Rails will go through that list of superior comparisons, finding and collecting together all movie objects whose ID appears in the inferior_movie_id column.
Let’s see this in action in the console:
$ rails c
firstMovie = Movie.first => #<Movie id: 1, title: "The Dark Knight", year: 2008>firstMovie.superior_comparisons => #<ActiveRecord::Associations::CollectionProxy [#<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3>]>firstMovie.inferior_movies=> #<ActiveRecord::Associations::CollectionProxy [#<Movie id: 3, title: "Citizen Kane", year: 1941>]>
Nice! firstMovie.inferior_movies returns movies from the inferior_movie_id column.
To grab all superior movies, we just add the inversion of the previous two methods:
Note: You can name :superior_comparisons (and inferior_comparisons) whatever you want. Call it ‘X.’ It stills works. “X” is still defined as “those instances of a comparison that contain my ID in the superior_movie_id column.”
At this point, we should be able to call .superior_movies and .inferior_movies on a movie object and receive a collection of movies (better and worse) in return.
Test this out in the console:
Comparison.first => #<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3>darkKnight = Movie.first => #<Movie id: 1, title: "The Dark Knight">darkKnight.inferior_movies=> #<ActiveRecord::Associations::CollectionProxy [#<Movie id: 3, title: "Citizen Kane", year: 1941>]>citizenKane = Movie.third => #<Movie id: 3, title: "Citizen Kane", year: 1941>citizenKane.superior_movies => #<ActiveRecord::Associations::CollectionProxy [#<Movie id: 1, title: "The Dark Knight", year: 2008>]>
The self-join relationship is now fully set up. Try using the shovel method to create new comparisons:
Comparison.count => 2pulpFiction = Movie.second=> #<Movie id: 2, title: "Pulp Fiction", year: 1994>pulpFiction.superior_movies.count => 1pulpFiction.superior_movies << darkKnight=> #<ActiveRecord::Associations::CollectionProxy [#<Movie id: 3, title: "Citizen Kane", year: 1941>, #<Movie id: 1, title: "The Dark Knight", year: 2008>]>pulpFiction.superior_movies.count=> 2Comparison.count => 3
If you saw similar results, you’ve successfully created a comparative self-join table!
Let’s turn it up a notch and get users in the picture with a triple-join.
Triple-Join
Each comparison should contain a unique ID from two movies (one “superior”, on “inferior”) and a user. The end goal of our comparison table should, therefore, look something like this:
This table displays two users with opposite opinions.
User 1 creates the same hierarchy with which we’ve been working (movie 1 is superior to 3, which is superior to 2). User 2 has the reverse opinion: they think that movie 1 is inferior to movie 3, which is inferior to movie 2.
Alert: To move forward with a new table setup, we need to rollback our database to add another column in the comparison table. Currently, we have id, superior_movie_id, and inferior_movie id. We must add user_id.
$ rails db:rollback
Alter the comparison migration:
Now create a user model with a name attribute (i.e. a column in the database) in the db migration:
$ rails g model User name
After deleting timestamps, we’re left with:
A User model should have also been created:
Our goal is to be able to create many comparisons from each User. Each comparison will necessarily contain an inferior and superior movie.
We should then be able to see all movies that a particular user deems superior and all of those which the user deems inferior.
We need to include all of this information in the User model. Let’s start with the first and simplest:
The above has_many simply states that one user (represented by their User_id) can appear in many separate comparisons.
With this relationship declared, we have access to the method .comparisons, which will retrieve a list of all comparisons in which the user appears.
The next two relationships are now possible:
Rails will then take that list of comparisons created by the .comparisons method defined in the first line and is able to retrieve all movies that appear in the inferior_movie_id column (method: .inferior_movies), as well as all those movies that appear in the superior_movie_id column (method: .superior_movies).
We are now in a position to test the new relationships. Let’s update our seed file with the information from the initial table diagram we created:
I like my database to be as clean as possible. I’ll reset the whole thing to avoid ID confusion:
$ rails db:reset
Re-migrate:
$ rails db:migrate
Re-seed:
$ rails db:seed
Check our relationships in the console:
chris = User.first=> #<User id: 1, name: "Christopher Nolan">chris.comparisons => #<ActiveRecord::Associations::CollectionProxy [#<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3, user_id: 1>, #<Comparison id: 2, superior_movie_id: 3, inferior_movie_id: 2, user_id: 1>]>chris.comparisons.count => 2 chris.superior_movies => #<ActiveRecord::Associations::CollectionProxy [#<Movie id: 1, title: "The Dark Knight", year: 2008>, #<Movie id: 3, title: "Citizen Kane", year: 1941>]>chris.inferior_movies => #<ActiveRecord::Associations::CollectionProxy [#<Movie id: 3, title: "Citizen Kane", year: 1941>, #<Movie id: 2, title: "Pulp Fiction", year: 1994>]>
Excellent. You’ve successfully implemented the most important prong of the triple-join. Our goal was to be able to access inferior and superior movies from a particular user. We can now do that.
For the sake of good and circumspect learning, lets’ create the other two sides of the triple-join. We should be able to apply the principles we’ve just learned to a movie.
From a movie, we should have access to the users that are “fans” of the movie (i.e. users who put the movie in the superior_movie_id column), as well as those users that are “haters”.
To do this, update the movie model with these two relationships:
Translation: When you call the “fans” method on a movie, Rails will go through that previously defined list of superior comparisons, finding and collecting all User objects (source: user) whose ID appears in the user_id column.
The full movie model view:
At this point, if you try to enact either of these methods (.fans or .haters), you’ll get this error:
Movie.first.fans ActiveRecord::HasManyThroughSourceAssociationNotFoundError (Could not find the source association(s) :user in model Comparison. Try 'has_many :fans, :through => :superior_comparisons, :source => <name>'. Is it one of superior_movie or inferior_movie?)
ActiveRecord could not find the association between Comparison and User. Luckily, that is our last relational method to create. And it’s a simple one. Let’s do that now:
We know what this means. Every time we create a comparison, it must necessarily have an ID from not only the inferior_movie and superior_movie tables, but also the User table via the user_id column.
Test this in the console:
firstMovie = Movie.first => #<Movie id: 1, title: "The Dark Knight", year: 2008>------------firstMovie.superior_comparisons => #<ActiveRecord::Associations::CollectionProxy [#<Comparison id: 1, superior_movie_id: 1, inferior_movie_id: 3, user_id: 1>]>firstMovie.fans => #<ActiveRecord::Associations::CollectionProxy [#<User id: 1, name: "Christopher Nolan">]>-------------firstMovie.inferior_comparisons=> #<ActiveRecord::Associations::CollectionProxy [#<Comparison id: 3, superior_movie_id: 3, inferior_movie_id: 1, user_id: 2>]>firstMovie.haters => #<ActiveRecord::Associations::CollectionProxy [#<User id: 2, name: "Quentin Tarantino">]>
Victory!
Note: With this triple-join, ActiveRecord requires all three IDs when you create a new Comparison (user_id, inferior_movie_id, and superior_movie_id).
Therefore the << shovel method to create new comparison instances no longer works. If we want to create new instances, we need to be explicit. We’ll cover this in part 3.
Recap
We created a Rails app, defined the correct models, migrations, and relationships, then seeded the database to test our work.
In the next article, we will take this data and display it in the browser. We’ll then define actions that allow us to easily create new comparisons for a specific user.
For all that excitement and more: Movie Comparison Website in Ruby on Rails.
If you’d like to download the work we’ve done thus far, visit the GitHub repo.
Conclusion
If you’re dropping off here, thanks for reading! Be sure to visit the site below for a working example of a movie recommendation website utilizing this triple-join relationship.
(It may take ten seconds for Heroku to load the page but once the site has loaded, you’ll be able to navigate easily).
Cheers to you, Ted Codd.
Happy coding,
Jackson
