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
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 -
Now, let’s use Subqueries with ActiveRecord to find out the same thing -
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:
Heightsof 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 -
In Ruby, the code should be-
This creates the SQL we want.
The WHERE.NOT query
if we have to identify the students who have not taken any leave,
we will be using
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-
let’s use the
method to peek into the SQL that Rails is producing without actually executing the query.
The FROM subquery
To find the average performance review score, we have to use the following code-
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.
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-
The Ruby code should be -
The FIND_BY() query
Find_by() returns only one object of a class
allows additional attributes besides the argument passed.
If we have several instances of the object,
we’d like to add an additional attribute to get the actual object we are looking for,
Let’s try to obtain a student whose name is Rohit Sharma-
If we want it in SQL, the query should be -
The Ruby code should be:
We hope that this article was helpful. Check out the official Rails documentation to know about other methods.