Ruby on Rails - Active Record Query

---

---

Read this chapter to learn the different ways to retrieve data from the database using Active Record.

You generally need to execute the SELECT query in raw SQL to retrieve data from the tables in a database of any type (such as MySQL, PostgreSQL, Oracle SQLite etc.). In most cases, the Rails ORM implemented by Active Record insulates you from the need to use raw SQL.

An Active Record helps you perform query operation in database-agnostic manner. So that, you don't need to change the Rails code even if the database type is changed.

For this chapter, we shall use the employees table populated with a sample data.

First of all, generate the Employee model and migrate the same.

rails generate model Employee emp_id: string email: string first_name: 
string last_name: string ph_no: string  salary: integer
rails db: migrate

Populate the Table

Open the console corresponding to the database in use (for example SQLite) and populate the employees table with the following data:

INSERT INTO employees (emp_id, email, first_name, last_name, ph_no, salary) 
VALUES (1,'johndoe@email.com','John','Doe','+11536198741',14320),
(2,'janedoe@email.com','Jane','Doe','+11536219842',12050),
(3,'michael.smith@email.com','Michael','Smith','+11536298753',11340),
(4,'emily.jones@email.com','Emily','Jones','+11536376482',9800),
(5,'david.lee@email.com','David','Lee','+11536492837',15430)

While in the Rails application directory, open the Rails Console and verify that the Employee model has the above data by calling the all method. Observer that it internally executes the SELECT query to fetch all records.

library(dev)> Employee.all
SELECT "employees".* FROM "employees"

Methods to Retrieve Objects

Rails Active Record query interface provides different methods to retrieve objects. Let us learn about the most frequently used methods.

The find() Method

With the find() method, you can retrieve the object corresponding to the specified primary key that matches any supplied options. For example, the following statement fetched the employee with id = 2

library(dev)> Employee.find(2)
SELECT "employees".* FROM "employees" WHERE "employees"."id" = 2 LIMIT 1

The console also echoes the corresponding SELECT query.

If no matching record is found, then the find method will raise an ActiveRecord::RecordNotFound exception.

To fetch multiple objects, call the find method and pass in an array of primary keys.

library(dev)> Employee.find([1,3])

The SQL equivalent query is as follows:

SELECT "employees".* FROM "employees" WHERE "employees"."id" IN (1, 3)

The take() Method

Rails will not apply any implicit ordering with the take method that retrieves a given record. Without any arguments, the very first record in the table is retrieved, as can be observed from the SQL query.

library(dev)> Employee.take
SELECT "employees".* FROM "employees" LIMIT 1

You can specify the number of records to be retrieved as an integer argument. For example, the following command returns first four records.

library(dev)> Employee.take(4)
SELECT "employees".* FROM "employees" LIMIT 4

The first() Method

The first method finds the first record ordered by primary key. Accordingly, it adds the ORDER BY clause in the SELECT query internally executed by Rails.

library(dev)> Employee.first
SELECT "employees".* FROM "employees" ORDER BY "employees"."id" ASC LIMIT 1

The last() Method

On the other hand, the last method retrieves the last record ordered by primary key.

library(dev)> Employee.last
SELECT "employees".* FROM "employees" ORDER BY "employees"."id" DESC LIMIT 1

The last method returns nil if no matching record is found and no exception will be raised.

The find_by Method

The find_by method finds the first record matching some conditions. For example, use the following command to find the record with first name as Jane.

library(dev)> Employee.find_by first_name: "Jane"
SELECT "employees".* FROM "employees" WHERE "employees"."first_name" = 'Jane' LIMIT 1

As you can see, the SELECT query adds the WHERE clause.

You can also achieve the same effect by the where() method, passing the condition as argument.

library(dev)> Employee.where(first_name: "Jane").take
  Employee Load (0.2ms)  SELECT "employees".* FROM "employees" 
     WHERE "employees"."first_name" = 'Jane' LIMIT 1

The find_each() Method

You can also retrieve multiple objects. The find_each() method is a memory-friendly technique to retrieve a batch of records and then yields each record to the block individually as a model.

You can use the rails runner command to echo the records on the command terminal.

rails runner "Employee.find_each { |e| puts e.first_name }"
John
Jane
Michael
Emily
David

You may also do the same inside the rails console. Have a look at the equivalent SQL query.

library(dev)> Employee.find_each { |e| puts e.last_name }
  SELECT "employees".* FROM "employees" ORDER BY "employees"."id" ASC LIMIT 1000

Conditional Retrieval

You can use the where() method for conditional retrieval. You can execute a parameterized SELECT query by using the ? symbol as a place holder.

library(dev)> Employee.where("last_name = ?", "Smith")
SELECT "employees".* FROM "employees" WHERE (last_name = 'Smith')

The params Keyword

You can use array conditions in the Employee model while incorporating the params keyword, and pass params as argument to where() method.

Here, the last_name is defined as a parameter. Rails retrieves objects matching with the condition. Use of params helps in avoiding SQL injection.

library(dev)> params = { last_name: "Smith" }
=> {:last_name=>"Smith"}
library(dev)> Employee.where(last_name: params[:last_name])
SELECT "employees".* FROM "employees" WHERE "employees"."last_name" = 'Smith'

Here is another example. All the objects with last_name column having the given characters are returned. As in SQL, using LIKE keyword is defined in Rails Active Record class.

library(dev)> params = { last_name: "Do" }
=> {:last_name=>"Do"}
library(dev)> Employee.where("last_name LIKE ?", params[:last_name] + "%")
  Employee Load (0.3ms)  SELECT "employees".* FROM "employees" WHERE (last_name LIKE 'Do%')

The order() Method

You know that the SELECT query has the ORDER BY clause. Active Record interface implements the same with its order() method. You need to pass the expression on which the query result should be ordered.

The following statement retrieves the employee objects on the ascending order of salary:

library(dev)> Employee.order(:salary)
SELECT "employees".* FROM "employees" ORDER BY "employees"."salary" ASC LIMIT 11

Add the desc keyword to generate the SELECT query with ORDER BY DESC clause:

library(dev)> Employee.order(first_name: :desc)
SELECT "employees".* FROM "employees" ORDER BY "employees"."first_name" DESC LIMIT 11

The select() Method

The Model.find method selects all the fields, which equivalent to SELECT * from table. To select only a subset of fields from the result set, you can specify the subset via the select method.

library(dev)> Employee.select(:first_name, :last_name, :salary)
SELECT "employees"."first_name", "employees"."last_name", "employees"."salary" FROM "employees" LIMIT 11 

Alternatively, you can pass a string with desired column names separated by a single space.

library(dev)> Employee.select("first_name last_name salary")

To translate the SELECT query with the DISTINCT clause, append the distinct keyword to the select() call.

library(dev)> Employee.select(:last_name).distinct
SELECT DISTINCT "employees"."last_name" FROM "employees"