How to Write a SQL Query in Ruby on Rails

Software developers write countless queries. A query is basically a question, like asking, ‘How many countries are in Africa?’. A query in SQL is no different from that, except you are asking about a set of data. To ask those questions, we use a special language called Structured Query Language (SQL) which is the standard.

When querying a database, it can either be a select query or an action query. A select query retrieves data from the database, while an action query does more such as inserting, deleting, or updating. To better understand this, imagine you are looking for a car in a house. You don’t search the shelves or the kitchen, you simply filter out those places and head straight to the garage. This makes searching for a car easy.

Rails, through ActiveRecord, keeps us away from having to think too much about SQL level queries. This makes it easier to work with our data.

In this article, we’ll assume you have the basic knowledge of SQL queries. If you don’t, you can learn more here.‍

How do we write SQL queries in Active Record?‍

Rails Active Record has many finder methods that can allow an argument to be passed, in order to perform certain queries without writing raw SQL.

We’ll highlight some of the methods in this article, but you can find the complete list of available methods in the Rails documentation.

So, first, we’ll set up a User class and we’ll be using it in our examples.

‘class User < ApplicationRecord

end’

A User model only represents a Users table, the model itself is not the table. In the database, a Users table has tables and rows while in Rails, we have models and objects.

Where()

This query method is very adaptable yet confusing. It filters the current relations according to the conditions in the arguments then returns a new relation, which is an array of instances with more information attached. This method lets you add conditions to limit the record returned. The conditions can either be a hash, a string, or an array.

Now, let’s write a query that will return a user or users with the name ‘zee’:

The usual SQL query would be:

SELECT * FROM users WHERE (users.name = “zee”) LIMIT 1

The above query can be written in Active Record as:

User.where(name: “zee”)

But, what if the name passed in the argument is coming from a different place?

It can be written as:

User.where(“name = ?”, params[:name])

In the above example, the (?) will replace any argument coming from the params[:name]. Let’s say the params[:name] is “roy”, it will execute the query as;

User.where(“name = ?”, “roy”)

What if there are multiple instances of the name “roy”? You could rewrite the query as;

User.where(“name = ?” AND “surname = ?”, “roy”, “ntaate”)

Now, let’s retrieve all users that are not “roy”;

User.where.not(name: “roy”)

The SQL query

SELECT * FROM users WHERE name != ‘roy’

find()

find() is very simple to use. It returns a single object from the database in relation to the primary key that has been passed in as an argument (ID). Let's search for a user with an ID of 1.

SQL query:

SELECT * FROM users WHERE (users.id = 1) LIMIT 1

Active Record:

User.find(1)

But that’s not all find() can do. An array of primary keys or IDs can also be passed in. This will return an array that matches the record of the IDs passed in.

For example:

User.find([2, 3, 6])

What if we are looking for Users with IDs from 1 - 5? We can write our query like this;

User.find([1, 2, 3, 4, 5]).

I’m sure by now you’re thinking that the array might be as long as a “Terms and Conditions” note if you want to find more IDs.

This is where take comes to the rescue. We can achieve that by using take;

User.take(5)

This returns an array of records from 1 - 5.

There are other find() variants like .first and .last. .first retrieves the first record, while .last retrieves the last record ordered by the primary key. Numbers can also be passed as arguments.

Here’s an example of this:

User.first

User.last

An example with argument is:

User.first(3)

This will retrieve the first three records in the database.

User.last(3) will return the last three records in the database.

find_by()

Like find(), find_by() returns only one object of a class. While find() only allows a primary key or ID to be passed in as an argument, find_by() allows additional attributes besides the ID.

For example:

We have multiple instances of the object named “roy”, so we’d like to add an additional attribute to retrieve the actual object we are looking for. Let’s add a country so we can retrieve the “roy” we are looking for.

The query will be:

Users.find_by(name: “roy”, country: “uganda”)

Now, let’s retrieve a user created 2 days ago:

User.find_by(“created_at > ?”, 2.days.ago)

Conclusion

Writing queries in Active Record can go from easy to, “What am I even doing?!”. This article lists the common ways to do so and how to use them. Be sure to check out the official Rails documentation to read about other methods. Hopefully, this helps you understand how to write some common queries in Ruby on Rails. Happy coding!