If you develop an e-commerce website or CRM application, you most likely use a database (DB) to store and access information. At the beginning of the development, the DB queries look very simple, and the execution of them takes milliseconds. Everything changes when the complexity of the business logic grows. Now DB queries join other tables, have complex nested queries, and aggregate multiple values. Rails provides a wonderful ActiveRecord gem that helps to interact with the database. However, there are still some challenges that require custom solutions. One of them is the creation and maintenance of complex queries using database-specific features.
gap intelligence has vast experience in database design and management. The tools from gap intelligence allow users to easily get a report of pricing, promotions, placements and products information. To efficiently build reports we use all the power and capabilities of SQL that modern databases provide today.
In this article I want to share my experience writing complex SQL queries using Arel, which is available by default in the Rails framework.

What is Arel?

Arel is a part of ActiveRecord and is used to generate SQL queries. Such ActiveRecord methods like "where" or "select" can use Arel methods to build a SQL AST (Abstract Syntax Tree). At the moment when the query must be executed, all Arel parts get converted into the corresponding SQL statements and combined in the order that complies to SQL specification.
ActiveRecord allows one to build queries without the explicit use of Arel. The input argument of "where" method will be converted to Arel object as needed. In spite of ActiveRecord interface providing a limited set of SQL capabilities, using Arel directly will extend that list and provide a more powerful way to build complex SQL queries.

Arel Features

More than one post is needed to cover all Arel capabilities, but I will provide a brief overview of Arel functions I use most and guide through how to apply them.

Arel Table

To switch to “manual mode”, the ActiveRecord model provides the "arel_table" method. The return value is type of "Arel::Table" and has the analogy of a table in SQL. For example, "arel_table[:column_name]" allows you to access a table column and also apply the operator.
# Arel::Table of Product model
product_arel_table = Product.arel_table

# SQL equivalent is 
# "products"."name" IS NULL
product_arel_table[:name].eq(nil) # => Arel::Nodes::Equality

# SQL equivalent is 
# "products"."price" >= 100
product_arel_table[:price].gteq(100) # => Arel::Nodes::GreaterThanOrEqual

Joins and Aliases
This example demonstrates how to implement "LEFT OUTER JOIN" with two join conditions.
# Product and Order models
products_table = Product.arel_table
orders_table = Order.arel_table

# Arel's JOIN definition is similar to SQL JOIN statement
orders_join = products_table.outer_join(orders_table).on(
  products_table[:part_number].eq(orders_table[:part_number]).and(
    products_table[:brand].eq(orders_table[:brand])
  )
)

# Uses created join
Product.joins(orders_join.join_sources).where(
  orders_table[:created_at].gt(2.days.ago)
)

Arel allows you to create custom alias to nested queries in JOIN statement.
# top_five is a scope of Product
top_five_products_subquery = Product.top_five

# Creates alias to nested query 
top_five_products_table = Arel::Nodes::TableAlias.new(
  Arel.sql(
    format('(%s)', top_five_products_subquery.to_sql)
  ), 'top_five_products'
)

# Joins nested query
suppliers_table = Supplier.arel_table
top_five_products_join = suppliers_table.outer_join(top_five_products_table).on(
  suppliers_table[:product_id].eq(top_five_products_table[:id])
)
Supplier.joins(top_five_products_join.join_sources)

CTE
CTEs (Common Table Expression) have similarities to JOIN subqueries, but they have several advantages in use. Arel allows you to create CTE expressions using the "with" method.
# Defines CTE alias
cte_table = Arel::Table.new(:cte_table)
# Adds alias to subquery
composed_cte = Arel::Nodes::As.new(cte_table, Arel.sql(
  format('(%s)', Products.top_five.to_sql)
))

# Adds created CTE to query using `with`
arel_query = cte_table.project(
  cte_table[:brand], cte_table[:part_number].count
).with(composed_cte).group(cte_table[:brand]).distinct

Using Arel in FROM
One of the advantages of using Arel is the ability to use ActiveRecord chains. For example, the kaminari gem extends ActiveRecord to be able to paginate over a dataset by calling "page" method on an "ActiveRecord::Relation" object. In my practice, I have encountered cases where complex queries using grouping may not be properly built with the SQL statements. One solution for such cases could be to use a subquery in FROM. In this case, Arel fits perfectly.
# This can be any type of complexity query
subquery = Product.top_five

# Creates alias
products_table = Arel::Nodes::TableAlias.new(
  Arel.sql(format('(%s)', subquery.to_sql)), Product.arel_table.name
)

# Pass subquery to FROM statement
Product.from(products_table).page(1)

Pros and Cons of using Arel
Using Arel has its pros and cons. Below is a short list, which in my opinion, reflects the most important things. I’ll start with the pros:
  • Ruby instead of String. An alternative way to build a custom complex query is to pass SQL statements as a String to ActiveRecord methods. If a statement contains unescaped string interpolations, or you accidentally miss a closing bracket, such errors will be detected only during the execution of the query by the database. It is especially hard is to concatenate multiple string statements into one. Arel escapes all values automatically, the missing bracket will be detected by the Ruby parser, and query concatenation is done by AST. By using Arel, a developer reduces the risk of creating an invalid SQL query.
  • Reuse of Arel expressions. Arel expressions can be reused while building queries or sharing a behavior. A good example is the use of Arel expressions in Rails Concerns. Arel expressions will be properly applied while query generation with the context of ActiveRecord model that included it.
  • Ease of use ActiveRecord chains. Arel provides the possibility to use ActiveRecord methods and create ActiveRecord chains.
The following are some shortcomings I have identified:
  • Difficulty in reading. Arel, although it has similar names for methods with SQL, the code expressions and writing remain Ruby. This creates a mix of languages and complicates the understanding of the codebase.
  • Redundant code. Writing some SQL expressions in Arel will require writing more code than would be required in SQL.
  • Difficulty in debugging. Although Arel allows us to create queries using Ruby, queries still get executed in the database, and Arel does not provide any benefits in SQL debugging or performance troubleshooting.
Summary
In the Rails world, there are many solutions for creating, maintaining, and executing custom complex SQL queries. In this article, I reviewed the possibilities of building SQL queries using Arel, which is available in Rails framework. With applying other techniques, Rails allows you to effectively build database queries to solve problems of any complexity.
For more than 16 years, gap intelligence has served manufacturers and sellers by providing world-class services monitoring, reporting, and analyzing the 4Ps: prices, promotions, placements, and products. Email us at info@gapintelligence.com or call us at 619-574-1100 to learn more.