Querying Your Database - The Railsy Way

I do not own Rails nor am I on the Rails team!

Do you want to search through databases like an SQL deity but can’t wrap your head around that obnoxious syntax? Well, has Rails got a solution for you! Introducing “The Railsy Way”, a way to query your database that doesn’t make you want to feel like bashing your head against a wall.

In this article, I will dissect an SQL query that I used and go over some of the magic that Rails can do to make your life a bit easier.

I’ve used Rails database querying in almost every project I’ve done while at Flatiron — it’s super easy. I haven’t looked into it too deeply, but Rails will understand what you are trying to do. Don’t believe me? Check out this horrifying piece of SQL:

Disgusting. For this example, all you need to know is that my database consists of 3 tables: A “users” table, a “frameworks” table, and a join table, called “user_frameworks”. A user can have many frameworks, and a framework can have many users. A simple many-to-many (m:n) relationship.

Now that you know the database structure, what am I searching for in the above query? Let’s go through it piece by piece, just in case you aren’t a huge SQL fan.

Breaking It Down

I’m selecting all the columns from the “users” table.

Disclaimer: For all of the example pictures, I am only selecting a user’s username because I have so many user properties that it can’t be displayed cleanly in the terminal. Just saving some space!

SELECT * FROM users

I’m taking that information and doing an INNER JOIN with the join table, “user_frameworks”. An INNER JOIN only selects records that have matches on both tables.

I’m specifying that I want to join the tables based on the ‘user_id’ stored in my “user_frameworks” table and the ‘id’ column in my “users” table. Therefore, we are now getting a table that will look for matches between these two tables and return another table. If a user is linked to multiple frameworks, that user will be returned multiple times. Example: If Corey knows Rails and Express, we will get TWO Corey results.

Remember, at the beginning of this query we only asked for user columns to be returned, so all we are getting back is users. At this point, we are getting back a table returning a user for each result in ‘user_frameworks’.

SELECT * FROM users INNER JOIN user_frameworks ON user_frameworks.user_id = users.id

What’s this? Another INNER JOIN? This time, we are linking the table we’ve created above with our “frameworks” table.

This won’t return anything different from above since we are still only asking for the users returned. But to show you that the tables have indeed been connected, for the picture below I have also requested the ‘name’ property from our now-joined “frameworks” table.

SELECT users.username, frameworks.name FROM users INNER JOIN user_frameworks ON user_frameworks.user_id = users.id INNER JOIN frameworks ON frameworks.id = user_frameworks.framework_id;

This looks a little different. The WHERE keyword tells us that we are looking for a specific value. Here, we are searching for a specific framework and we are searching for it by its ‘name’, hence “frameworks.name”.

The ILIKE keyword in PostgreSQL will tell the database to ignore capitalization. This will avoid a situation where a user doesn’t capitalize something. Imagine looking for “Javascript” in a database but the name is “JavaScript”, and as a result you get no results. Feels bad, man. ILIKE will help us avoid that.

Finally, we have ‘%rai%’ at the end. This is a simple SQL way of using wildcards. The % simply means anything. Putting it on both sides of our query will return any results that contain ‘rai’ within them. You’ll get “Rails”, “Trails”, and “Samurai”.

SELECT users.username, frameworks.name FROM users INNER JOIN user_frameworks ON user_frameworks.user_id = users.id INNER JOIN frameworks ON frameworks.id = user_frameworks.framework_id WHERE frameworks.name ILIKE ‘%rai%’;

Tada! I’ve just done a partial search for all users that have a framework with the phrase ‘rai’ in it!

But I said Rails has an easier way to do it, didn’t I?

The Railsy Way

The method we can use in Rails is a lot cleaner and a lot less complicated. Rails does a good job of simplifying everything for us. Let’s check out the equivalent Ruby-On-Rails code:

Wow! So much cleaner! Let’s break down what each part is doing here.

The User class is being called, meaning we will be returning a user. Simple enough, huh?

This is very easy to read, and a lot of Rails magic is happening here: the multiple joins we did above are happening automatically because, in the User model, a User is described as having many frameworks. Magical and very concise!

The .where keyword takes the place of the WHERE keyword in SQL. The magical thing in Rails is that you can use the where keyword to search for a specific hash key:value pair.

This magic only works on exact matches, and it will only search the initial object (User). So we need to put the last part of our SQL query in as a string, which Rails will just insert.

I’ve added a variable in here to show Ruby’s string interpolation in action, and to make the search dynamic to whatever your front end returns.

Luckily, in this case, nothing else matched that phrase, so we only got unique users. But what if we only searched for users with the phrase ‘r’?

SELECT users.username, frameworks.name FROM users INNER JOIN user_frameworks ON user_frameworks.user_id = users.id INNER JOIN frameworks ON frameworks.id = user_frameworks.framework_id WHERE frameworks.name ILIKE ‘%r%’;

Oh no! Now miwhag2 is appearing twice! Rails will return 2 miwhag2 User objects!

Luckily, Ruby’s built-in enumerables can help us:

There we go! Now we can return all unique users who know a framework with an R in its name!

Hopefully this little walkthrough helped you not only understand SQL a little better, but also the awesome and concise power of The Railsy Way!

Check out W3Schools for some more information on SQL queries and the Ruby On Rails Guides for ActiveRecord Querying for more information on The Railsy Way of doing things!

Re-inventing myself through Flatiron School in Denver’s Software Engineering program!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store