A better way to write SQL queries in Rails using Arel Library
Being a Rails developer we are familiar with ActiveRecord queries, but not so much with Arel. Arel is a library which allows us to customize our queries in object-oriented manner without having to use manual SQL and string interpolation.
We often use where a method for querying the database. Where method can handle equality, null, arrays and now in Rails 4 it is also able to handle inequality condition with not method. But when there is a requirement of writing queries with OR statement or greater-than or less-than conditions most developers opt for writing SQL string queries. We have a better way to handle this situation using Arel.
For example:
If you want to select students having the date of birth greater than today’s date or date of birth less-than or equals to today’s date, usually a developer will write the query like this
Student.where("date_of_birth> ?", Date.today)
or
Student.where("date_of_birth<= ?", Date.today)
But this is not a good way to write such queries as we can write these queries in a more better way using Arel like this
Student.where(Student.arel_table[:date_of_birth].gt(Date.today))
or
Student.where(Student.arel_table[:date_of_birth].lteq(Date.today))
In the same way, ActiveRecord does not provide support for OR queries in earlier rails version.
If you want to fetch all the students having first as John or last name as Doe you can write the query like this but that is not the recommended way
Student.where(“first_name = ? OR last_name = ?”, “John”, “Doe”)
The recommended way of writing a query for above scenario is
arel = Student.arel_table
Student.where(arel[:first_name].eq("John").or(arel[:last_name].eq("Doe")))
Same with the LIKE queries when we opt for writing SQL string literals
Arel has a keyword called matches for writing LIKE queries
Advantages of Using Arel are:
- Follows DRY principle
Most of the time we write duplicate queries having one different parameter. We can avoid writing such queries using Arel. Because we can write queries in methods and that methods can be used for another query as well as we can design query builder using Arel.
- Reliability
When we write Join query using SQL string it can break down due to ambiguity in column names but this is not the case with Arel.
- Readability
Methods provided by Arel are readable to anyone who even don’t know much about coding. As we can write queries in parts using Arel it is much easier to understand in parts.
Arel allows rails developer to write queries in an object-oriented manner and it also allows us to write more complex queries with ease and modular code. Next time, when you stuck into complex queries go for Arel. This article just provides an overview of Arel as there is much more to explain in Arel. Detail description is available in the official docs of Arel.
All the businesses are converting into the web business for making an impact on their worldwide customers. There are number of software development companies offering software solutions to the market, Cryptex Technologies is one of them.
