How to write SQL queries in Rails?

ActiveRecord helps us to perform simple queries effortlessly and allows us to access the raw SQL to make our lives easier. In this article, we will explore a number of real-life use-cases using ActiveRecord with SQL.

We shall cover five forms of queries and subqueries to identify the desired data.

Working with ActiveRecord in Rails

ActiveRecord helps developers with a lot of things when it comes to SQL. Whether it’s the differences in SQL between databases (MySQL, Postgres, SQLite) or being able to escape strings to prevent SQL injection attacks, or interacting with the database using Ruby, Active Record comes to our rescue every time.

What are Subqueries in Rails

In this blog, we will be talking about how to use queries and subqueries in Active Record. Subqueries are nothing but a query within a query.

We can embed queries into the SELECT, FROM, WHERE, FIND_BY and FIND clauses of SQL, to visualize our data and meet the requirements of businesses.

So, why do subqueries matter? For instance, if we are to identify the students whose height is more than average, without using Subqueries, we’d use the following code -

SELECT AVG(height) FROM students

SELECT * FROM students WHERE height > 181

Now, let’s use Subqueries with ActiveRecord to find out the same thing -

SELECT * FROM students WHERE height > (SELECT AVG(height) FROM height)

We can see in the above example that the code gets way more simplified and shorter, thereby, deriving the result in a single query. This is the advantage of using subqueries in Rails.

We will be playing the role of a developer and help the school authorities find the data they are looking for, using Active Record in SQL.

Overview of the data

Our database has four tables and attributes:

students: Names and Heights of the students
performance_reviews: Scores of students in their overall school activities
leaves: Number of leaves taken by each student

The WHERE subquery

This method allows us to add conditions to limit the record returned. The conditions could be anything ranging from a hash, a string, to an array. Suppose, we are to identify the students who have a height more than average, then we have to use the following code -

-- find the students
SELECT "students".* FROM "students" WHERE (name = 'John')

In Ruby, the code should be-

Student.where(name: "John")

This creates the SQL we want.

The WHERE.NOT query

Suppose, if we have to identify the students who have not taken any leave, we will be using WHERE.NOT()

Student.left_joins(:leaves).where.not(leaves: { id: nil }).group('students.id')

The SELECT subquery

Now, if we are to identify the list of students, including their average height, and how much this student’s height differs from the average height of other students, then this should be the code in SQL-

SELECT
  *,
  (SELECT avg(height)
    FROM students) avg_height,
  salary - (
    SELECT avg(height)
    FROM students) above_avg
FROM students

Now, let’s use the to_sql method to peek into the SQL that Rails is producing without actually executing the query.

avg_sql = Student.select('avg(height)').to_sql

Student.select(
  '*',
  "(#{avg_sql}) avg_height",
  "height - (#{avg_sql}) avg_difference"
)

The FROM subquery

To find the average performance review score, we have to use the following code-

SELECT avg(avg_score) reviewer_avg
FROM (
  SELECT reviewer_id, avg(score) avg_score
  FROM performance_reviews
  GROUP BY reviewer_id) reviewer_avgs

In order to make our Ruby code free of mess, we have to place the subquery into a variable which can then be embedded into the main query.

from_sql =
  PerformanceReview.select(:reviewer_id, 'avg(score) avg_score').group(
    :reviewer_id
  ).to_sql

PerformanceReview.select('avg(avg_score) reviewer_avg').from(
  "(#{from_sql}) as reviewer_avgs"
).take.reviewer_avg

The FIND() query

This method returns a single object from the database that aligns to the primary key which has been passed in as an argument. For example, if we have to identify the students with their primary id, we have to use the following code-

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

The Ruby code should be -

Student.find(1)

The FIND_BY() query

Find_by() returns only one object of a class and allows additional attributes besides the argument passed.

For example:

If we have several instances of the object, “Sharma”, and we’d like to add an additional attribute to get the actual object we are looking for, we use find_by(). Let’s try to obtain a student whose name is Rohit Sharma-

If we want it in SQL, the query should be -

SELECT * FROM "students" WHERE "students"."name" = "Rohit" AND "students"."surname" = "Sharma"  LIMIT 1

The Ruby code should be:

Students.find_by(name: “Rohit”, surname: “Sharma”)

Final Thoughts

We hope that this article was helpful. Check out the official Rails documentation to know about other methods.