The relationship between Activerecord and the generated SQL [Ruby on Rails]

What is RDBMS?

It is the abbreviation for Relational Data Base Management System. As the name suggests, it is a general term for software that manages relational databases, and it organizes data into columns and records (rows) and expresses them side by side in a two-dimensional table. The SQL language is used for procedures such as data acquisition and creation. Typical examples include Mysql and Postgresql.

What is ORM?

it is the abbreviation for Object Relational Mapping, a programming technique that allows you to handle tables and data on RDBMS that cannot be directly handled by object-oriented languages such as Ruby by mapping them into classes.

What is ActiveRecord?

ActiveRecord used in Rails is the layer that plays the core of MVC's M, or Model, and mainly interacts with the database. It is a library that implements the "Active Record pattern" described in ORM systems and bears the same name as this pattern. All classes created as Rails models inherit the ApplicationRecord class, which inherits from ActiveRecord::Base, so you can use this ActiveRecord method in all classes generated by Rails.

Preparations

In the example used below, we see a User model. Let's say we have the following columns.

User Model

Column	Type
ID	integer
First Name	string
Last Name	string
Age	integer

ActiveRecord methods and generated SQL statements

The ActiveRecord methods and the corresponding SQL generated to retrieve data are shown below. (The SQL statement is slightly different from what appears on the rails console, but it is shown in a form that can be executed on an actual RDBMS.)

	
# 1
User.all
# SELECT * FROM users;

# 2
User.find(1)
# SELECT * FROM users WHERE id = 1 LIMIT 1;

# 3
User.find_by(first_name: "John")
# SELECT * FROM users WHERE first_name = 'John' LIMIT 1;

# 4
User.create(first_name: 'John', last_name: "Smith", age: 30)
# INSERT INTO users (first_name, last_name, age) values ('John', 'Smith', 30);

# 5
User.first.update(first_name: "Michael")
# SELECT * FROM users ORDER BY id ASC LIMIT 1;
# UPDATE users SET first_name = 'Michael' updated_at = NOW() WHERE id = 1;

1. User.all

SELECT * FROM users;

It is a method that is often executed at the index method of the controller. As you know, all is an ActiveRecord method to get all users. * is a wildcard and means all (= all columns defined in the users table here). In other words, this SQL means "get all the records defined in the users table". The acquired data is mapped to the (array of) User class.

2. User.find(1)

SELECT * FROM users WHERE id = 1 LIMIT 1;

This SQL means "get all the columns of the first data of the user whose id is 1 from the users table". The fetched record is mapped to User model object returned by the method find.

3. User.find_by(first_name: "John")

SELECT * FROM users WHERE first_name = 'John' LIMIT 1;

When specifying the condition by id and getting the user, simply specifying 1 was enough, but if you want to specify the condition on other columns, use the find_by method to specify the column and condition at the same time.

However, looking at the issued SQL, the WHERE id = 1 part only changed to WHERE first_name = 'John'. Not limited to ActiveRecord, there are many ORM systems that provide simplified methods for id searches like this.

4. User.create(first_name: 'John', last_name: "Smith", age: 30)

INSERT INTO users (first_name, last_name, age) values ('John', 'Smith', 30);

This method generates insertion query and execute it.

5. User.first.update(first_name: "Michael")

SELECT * FROM users ORDER BY id ASC LIMIT 1;

UPDATE users SET first_name = 'Michael' updated_at = NOW() WHERE id = 1;

With update, two SQL statements are generated to first get the data column to be updated then rewrite it. The first method used here is a method that retrieves the data with the smallest id on the database, but in SQL, the condition is to "arrange the data in ascending order of id and retrieve the first data''. As you can see, the update method is slightly different from insert in how to specify columns and values.