Introduction

What is Arel?

Arel is a SQL generator library for Ruby. It allows us to write complex SQL queries in a semantic, reusable fashion by using an SQL AST (Abstract Syntax Tree-like) approach.

It represents the SQL syntax as nodes similar to how we use an expression tree to represent mathematical expressions.

Arel uses different predications (analogous to operators for the expression tree) such as count, eq, not_eq, gt, etc. to construct the SQL

t = User.arel_table
User.where(t[:first_name].eq('Rohit')).to_sql
#=> "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"first_name\" = 'Rohit'"
t.

What is the SQL FILTER clause?

The filter clause in SQL extends aggregate functions (sum, avg, count etc.) by an additional where clause.

Syntax:

COUNT(<expression>) FILTER(WHERE <condition>)

Rails 7 added support for the FILTER binary prediction to support and generate this filter clause in Arel

Before

t = User.arel_table
Arel.star.count.filter(t[:first_name].eq("Rohit")).to_sql
#=> NoMethodError: undefined method `filter' for #<Arel::Nodes::Count:0x00007f7f71fd1dd8>

After

t = User.arel_table
Arel.star.count.filter(t[:first_name].eq("Rohit")).to_sql
#=> "COUNT(*) FILTER (WHERE \"users\".\"first_name\" = 'Rohit')"

t = Order.arel_table
t[:amount].sum.filter(t(:item_count).lt(2)).to_sql
#=> "SUM(\"orders\".\"amount\") FILTER (WHERE \"orders\".\"item_count\" < 2)"

# With alias
t[:amount].sum.filter(t[:item_count].lteq(3).as('small_orders_total_amount')).to_sql
#=> "SUM(\"orders\".\"amount\") FILTER (WHERE \"orders\".\"item_count\" <= 3 AS small_orders_total_amount)"

Note: The filter clause is currently supported only for PostgreSQL(9.4+) and SQLite(3.30+) databases