Speed ​​Up Your Development Time With Queries in Ruby on Rails

Whether you have made simple queries with SQL language to get records in some relational database or you have experience as a complex database administrator, when starting it might seem extremely difficult to learn the syntax to select only what you require, or which table to join with which to make the necessary consultation.

In this article, we are going to know the magic that Ruby performs together with her gem "ActiveRecord" to automate and facilitate the management of records in a relational database.

When ActiveRecord maps classes that inherit from the ActiveRecord :: Base class, it builds common query methods on simple class methods. The same SQL queries are made by ActiveRecord, but the abstract ones to short story methods like

find ()

group ()

select ()

You will realize that you can perform the same operations in a faster way and with a much more natural syntax. I felt it was much easier as it required writing less code plus those methods were named more intuitively. For example, if we want to get the user with id: 43, in SQL it would be something like this:

SELECT * FROM user WHERE (user.id = 43) LIMIT 1 

With the ActiveRecord

find()

User.find(43) 
Returns => # <User id: 43, title: "The Bes Taquero", name: "Juan Pablo Gil"> 

The result is the same, you get the User record with the requested id, with the advantage that the syntax is much shorter and easier to understand.

We have just used an ActiveRecord method to perform a simple query that finds a record in the “User” table and with it, we could see a bit of the magic in action, however, the query that was executed to obtain was not observed in the terminal that record.

In addition to obtaining only one record, you can send the array an ids array:

SQL:

SELECT "user". * FROM "user" WHERE "user". "Id" IN (?,?) [["Id", 43], ["id", 72]] 

Ruby:

User.find ([43, 72]) 

Returns Array => # [< User id: 43, title: "The Bes Taquero", name: "Juan Pablo Gil">, <User id: 72, title: "CEO", name: "John Doe">] 

As we can see, executing the find method shows the SQL query that was executed, in addition to the result, obtained this time an Array with the 2 records of the "User" table that we required.

Essential methods to search for records in your database

take

SELECT * FROM user LIMIT 1 

Active record:

User.take 

Returns => # <User: id: 0, title: "Developer 1 " name: "John Doe 1"> 

first

SELECT * FROM user ORDER BY user.id ASC LIMIT 1 

Active record:

User.first
  
Returns => # <User: id: 0, title:"Developer 1 " name: "John Doe 1">

last:

SELECT * FROM user ORDER BY user.id DESC LIMIT 1 

Active Record:

User.last 

Returns => # <User: id: 100, title: "Developer 101", name: "John Doe 101">

In a collection that is

ordered

SELECT "users".* FROM "users" ORDER BY name 

Active Record:

User.order('name').first 


Returns => # <User: id: 0, title: "Developer 1", author: "John Doe"> 

find_by

SELECT * FROM user WHERE (user.name = 'Juan Pablo Gil') LIMIT 1 

Active Record:

User.find_by (name: 'Juan Pablo Gil') 

Returns => # <User: id: 43, title: "The best taquero", author: "Juan Pablo Gil" 

Conditional searches

The

where

 SELECT "details".* FROM "details" WHERE (quantity > 47 AND price > 10) 

Active Record:

Prices.where("quantity > ? AND price > ?", 99, 1000 ) 

Returns => # 
[#<Prices: id: 61, quantity: 100, user_id: 15, price: 1062>, 
 #<Prices: id: 26, quantity: 100, user_id: 39, price: 1863>, 
 #<Prices: id: 95, quantity: 100, user_id: 6, price: 1029>] 

Ordered searches

As we saw earlier, we can use the order method, it allows you to order the records, based on an indicated parameter.

 SELECT "projects".* FROM "projects" ORDER BY "projects"."date" ASC 

Active Record:

Project.order(:date) 

=> #
[#<project: id: 94, date: 23 May 2020, user_id: 1>, 
 #<project: id: 12, date: 10 Feb 2020, user_id: 2>, 
 #<project: id: 4, date: 28 Sep 2020, user_id: 53> ] 

Selecting an attribute

When performing a search you can specify the specific attributes you need with the

select

SELECT "projects"."date" FROM "projects" 

Active Record:

Project.select("date") 
 
Returns => [
 #<project: id: 0, date: 05 Nov 2019>, 
 #<project: id: 1, date: 19 May 2020>, 
 #<project: id: 2, date: 26 Oct 2020>] 

Scopes

Scopes allow you to specify commonly-used queries, which can be referenced when invoked. They can use all the methods mentioned above. (Usually declared in modes and can receive parameters ex "Age").

class User < ActiveRecord::Base 
 scope :productive, -> { where(age: 20..50) } 
end 

When invoking that scope defined in the Book model, the following happens:

SELECT "users".* FROM "users" WHERE "users"."age" BETWEEN ? AND ? [["age", 20], ["age", 50]] 

Active Record :

User.productive

Returns => [
<User: id: 1, title: "Developer 1", name: "John Doe ", age: 21>, 
. 
.
. 
<User: id: 48, title: "Developer 48", name: "John Doe ", age: 48>]

After learning about some of the most widely used methods to query the database with ActiveRecord, I can recommend that you do many practices to reduce the development time of an application, in addition to consulting the Active Record Query Interface documentation , where you will find the utility, proper syntax, and arguments for each query method.