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
takeSELECT * FROM user LIMIT 1
Active record:
User.take
Returns => # <User: id: 0, title: "Developer 1 " name: "John Doe 1">
firstSELECT * 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
orderedSELECT "users".* FROM "users" ORDER BY name
Active Record:
User.order('name').first
Returns => # <User: id: 0, title: "Developer 1", author: "John Doe">
find_bySELECT * 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
selectSELECT "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.