Before you go, check out these stories!

0
Hackernoon logoSpeed ​​Up Your Development Time With Queries in Ruby on Rails by@jpablomgil

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

Author profile picture

@jpablomgilJuan Pablo Gil

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 ()
, among others, in which we as developers do not see what is happening.

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()
method syntax it would be:

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
: Returns a record without any implicit order.

SELECT * FROM user LIMIT 1 

Active record:

User.take 

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

first
: Returns the first record ordered by its primary key.

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:
Returns the last record ordered by its primary key (default).

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
using the order method, last will return the last record ordered by the specified attribute.

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
: Returns the first record found that meets a certain condition.

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
method allows you to specify conditions to limit the returned records. This method represents the WHERE part of a SQL query.

 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
method.

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.

Tags

The Noonification banner

Subscribe to get your daily round-up of top tech stories!