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.
![Title](/images/2022-05-29-arel-filter-support/arel-tree.png)
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