ActiveRecord::Refined

Adding clean and powerful query syntax on ActiveRecord using refinements.

Author.
  joins(:posts) { :posts[:author_id] == :authors[:id] }.
  where { :authors[:age].in?(20..40) & (:posts[:published] == true) }
# SELECT "authors".* FROM "authors"
#   INNER JOIN "posts" ON "posts"."author_id" = "authors"."id"
#   WHERE "authors"."age" BETWEEN 20 AND 40 AND "posts"."published" = TRUE

Try it in your browser — Ruby 4.1, ActiveRecord and SQLite run in the page, so the examples build real SQL and return real rows without a ruby-master build of your own.

History

This gem was formerly known as activerecord-refinements, created by Akira Matsuda to experiment with the initial implementation of Ruby 2.0 Refinements. Because of the Refinements' spec change, that implementation stopped working on Ruby 2.0.0 stable, and the project was left dormant for a long time.

It has now been renamed to activerecord-refined and reimplemented on top of Proc#refined, which will be introduced in Ruby 4.1. Proc#refined returns a new proc that is evaluated with the given refinements activated, so a block written by the caller can be re-interpreted under the query DSL's refinements:

def evaluate_block(&block)
  refined_block = block.refined(ActiveRecord::Refined::BlockSyntax)
  BlockContext.new.instance_exec(&refined_block)
end

This is exactly what the old implementation needed and could not do, so the query syntax works again without monkey-patching Symbol globally.

Requirements

Ruby 4.1 or later (for Proc#refined; not released yet, so a ruby-master build is needed for now)
ActiveRecord 7.0 or later

The sandbox is there to skip that build: it carries its own Ruby 4.1. sandbox/ in this repository is what it is made of.

Installation

Add this line to your application's Gemfile:

gem 'activerecord-refined'

And then execute:

$ bundle

Or install it yourself as:

$ gem install activerecord-refined

Usage

Just require the gem, and where, select, joins, left_outer_joins, having, order and group will accept a block.

require 'activerecord-refined'

Inside the block, symbols denote columns of the receiver's table, and :table[:column] denotes a qualified column.

Conditions

Author.where { :age >= 18 }
Author.where { :name.like?('A%') }          # LIKE
Author.where { :age.in?(20..40) }           # BETWEEN
Author.where { :age.between?(20, 40) }      # BETWEEN
Author.where { :age.in?(18..) }             # >= 18
Author.where { :country.in?(%w[JP US]) }    # IN
Author.where { :country.null? }             # IS NULL

! negates any of these. Where SQL has a negative of its own, so does the block, which is the same rows written the way they would be written by hand:

Author.where { :country.not_null? }             # IS NOT NULL
Author.where { :country.not_in?(%w[JP US]) }    # NOT IN
Author.where { :age.not_between?(20, 40) }      # not between 20 and 40
Author.where { :name.not_like?('A%') }          # NOT LIKE
Author.where { :name.not_ilike?('a%') }         # NOT ILIKE / NOT LIKE

Author.where { !:name.start_with?('A') }        # NOT (name LIKE 'A%')

Nothing turns on the choice: NOT (country IS NULL) and country IS NOT NULL select the same rows, NULLs included. not_between? is the one whose SQL looks unlike its name — Arel writes it as the two comparisons, age < 20 OR age > 40, which is again the same rows.

in? also takes a relation as a subquery. Without an explicit select list the subquery selects the relation's primary key, the same way ActiveRecord's own where(id: relation) does:

Author.where { :id.in?(Post.published.select(:author_id)) }
# "authors"."id" IN (SELECT "posts"."author_id" FROM "posts" WHERE ...)

A relation on the right of a comparison is a scalar subquery. It has to select one value, so unlike in? there is no default select list and one is required:

Author.where { :age >= Author.select { avg(:age) } }
# "authors"."age" >= (SELECT AVG("authors"."age") FROM "authors")

exists? takes a relation and becomes EXISTS (SELECT ...). Correlate the subquery with the outer table through qualified columns — its where block goes through the DSL like any other:

Author.where { exists?(Post.where { :posts[:author_id] == :authors[:id] }) }
# EXISTS (SELECT "posts".* FROM "posts" WHERE "posts"."author_id" = "authors"."id")

Author.where { !exists?(Post.where { :posts[:author_id] == :authors[:id] }) }
# NOT (EXISTS (...))

like? is case-sensitive LIKE on every adapter, including PostgreSQL, where Arel would otherwise reach for ILIKE. ilike? is the one that asks for ILIKE; off PostgreSQL it is plain LIKE, which those adapters already match case-insensitively under their default collations. casecmp? is case-insensitive equality, folded on both sides rather than left to the collation, so it means the same thing everywhere:

Author.where { :name.ilike?('ma%') }        # ILIKE 'ma%' / LIKE 'ma%'
Author.where { :name.casecmp?('Alice') }     # LOWER(name) = LOWER('Alice')

not_distinct_from? and distinct_from? compare with NULL treated as a value, rather than as the unknown that makes = and <> neither true nor false. PostgreSQL spells this IS [NOT] DISTINCT FROM, SQLite IS / IS NOT and MySQL <=>, and the rows that come back are the same on all three:

Author.where { :country.not_distinct_from?(params[:country]) }  # matches NULL to nil
Author.where { :country.distinct_from?('JP') }                  # keeps the NULL rows

start_with?, end_with? and include? are shortcuts for the usual like? patterns. Unlike like?, they treat their argument as a literal string, so % and _ in it are escaped rather than matched as wildcards:

Author.where { :name.start_with?('A') }     # LIKE 'A%'
Author.where { :name.end_with?('son') }     # LIKE '%son'
Author.where { :name.include?('test') }     # LIKE '%test%'

Like their String namesakes, start_with? and end_with? take any number of literals; matching any one of them is enough:

Author.where { :name.start_with?('A', 'B') }
# (name LIKE 'A%' OR name LIKE 'B%')

member?, superset?, subset? and intersect? compare against a PostgreSQL array column, each carrying the meaning of its Ruby namesake: member? is Enumerable's element test (which String does not have — that is what separates it from include?), superset? and subset? are Set's whole-array containment, and intersect? is Array's "any element in common":

Article.where { :tags.member?('ruby') }            # tags @> '{ruby}'
Article.where { :scores.member?(80) }              # scores @> '{80}'
Article.where { :tags.superset?(%w[ruby rails]) }  # tags @> '{ruby,rails}'
Article.where { :tags.subset?(%w[ruby rails go]) } # tags <@ '{ruby,rails,go}'
Article.where { :tags.intersect?(%w[ruby go]) }    # tags && '{ruby,go}'

Like its namesake, member? takes one element — [1, 2].member?([1]) is false in Ruby, so an Array argument raises rather than quietly meaning something Array#member? does not. Requiring every element is superset?.

=~ and !~ match a regular expression: REGEXP and NOT REGEXP on MySQL, ~ and !~ on PostgreSQL. SQLite has no regexp operator of its own, so it raises there.

Author.where { :name =~ '^A' }              # REGEXP / ~
Author.where { :name !~ '^A' }              # NOT REGEXP / !~
Author.where { :name =~ /son$/ }            # a Regexp literal works too

Only a literal's source crosses over; the database has its own dialect and no equivalent of Ruby's flags. Dropping one would silently change what the query matches, so /son$/i raises instead — pass the pattern as a string if the database can express what you mean.

== always means SQL =, and passes its value through untouched. A Range or an Array therefore compares against a PostgreSQL range or array column, the same way ActiveRecord's own where(period: from...to) does for those column types:

Reservation.where { :period == (from...to) }   # daterange = '[from,to)'
Article.where { :tags == %w[ruby rails] }      # text[] = '{ruby,rails}'

!= is SQL != under the same rules, value passed through untouched.

For the same reason == nil and != nil raise ArgumentError: = NULL is never true in SQL, so a NULL test has to be spelled as one. Use null?:

Author.where { :country.null? }             # country IS NULL
Author.where { !:country.null? }            # NOT (country IS NULL)

Combine predicates with &, | and !. Ruby's operator precedence makes the parentheses around each comparison necessary, though the ? methods above need none:

Author.where { (:age >= 18) & ((:country == 'JP') | (:country == 'US')) }
Author.where { !(:age.in?(0..17) | :country.null?) }
Author.where { !:country.in?(%w[JP US]) }   # NOT (country IN ('JP', 'US'))
Author.where { !:name.like?('%test%') }     # NOT (name LIKE '%test%')

Joins

The block is the ON clause:

Author.
  joins(:posts) { :posts[:author_id] == :authors[:id] }.
  joins(:comments) { :comments[:post_id] == :posts[:id] }

Author.left_outer_joins(:posts) { :posts[:author_id] == :authors[:id] }

as names the table within the query, which is what makes a self join expressible — the qualified columns in the block go by that name:

Employee.joins(:employees, as: :managers) { :managers[:id] == :employees[:manager_id] }
# SELECT "employees".* FROM "employees"
#   INNER JOIN "employees" "managers" ON "managers"."id" = "employees"."manager_id"

Common table expressions

ActiveRecord's with and with_recursive need nothing from this gem: a CTE is joined by name like any other table, so its ON clause is a block, where Rails' own documentation reaches for a string join.

from_cte takes the CTE's name and selects it under the model's own table name, so the model's columns resolve:

Node.with_recursive(
  tree: [
    Node.where { :id == root.id }.
      select { [:id, :name, :parent_id, 0.as(:depth)] },
    Node.joins(:tree) { :nodes[:parent_id] == :tree[:id] }.
      select { [:nodes[:id], :nodes[:name], :nodes[:parent_id],
                (:tree[:depth] + 1).as(:depth)] },
  ]
).from_cte(:tree)
# WITH RECURSIVE "tree" AS (
#   SELECT "nodes"."id", "nodes"."name", "nodes"."parent_id", 0 AS depth
#     FROM "nodes" WHERE "nodes"."id" = 1
#   UNION ALL
#   SELECT "nodes"."id", "nodes"."name", "nodes"."parent_id",
#          ("tree"."depth" + 1) AS depth
#     FROM "nodes" INNER JOIN "tree" ON "nodes"."parent_id" = "tree"."id"
# ) SELECT "nodes".* FROM "tree" AS "nodes"

The anchor starts the count and the recursive member adds one, which is how the shape of a tree comes out of a flat table. The 0 is a value rather than SQL — see value below for why a number can say .as directly.

The alias on the last line is there for ActiveRecord's sake, not SQL's: written by hand that line would be SELECT * FROM tree. ActiveRecord goes on qualifying columns with the model's table name, so without the alias that name is not in the query and anything qualifying a column fails:

Node.with_recursive(tree: [...]).from(:tree).where(name: 'root')
# PG::UndefinedTable: missing FROM-clause entry for table "nodes"

Since the model's name is the only one that works, from_cte takes it from the model rather than asking. from(:tree, as: :nodes) is the same thing spelled out, and is what to reach for when the name wanted is not the model's.

What makes this worth spelling out is how selectively it breaks. count, order and select never qualify, so they work without the alias on every adapter; it is where and find_by that stop. A query can therefore look right until the day a condition is added to it.

A non-recursive CTE joins the same way:

Node.with(roots: Node.where { :parent_id.null? }).
  joins(:roots) { :roots[:id] == :nodes[:parent_id] }

examples/ctes.rb walks a category tree with these.

Aggregates, functions and aliases

count, sum, avg, min and max are available as methods, as are the scalar functions below, with fn for anything else. Use .as for a column alias, and .asc / .desc for the sort direction. Return an array to select or order by multiple expressions.

The scalar functions are real methods rather than anything caught dynamically, so a misspelling is a NoMethodError where you wrote it, and a name Ruby also answers to — rand — means the SQL one inside a block:

abs  acos  asin  atan  atan2  cast  ceil  char_length  coalesce  concat
cos  current_date  current_time  current_timestamp  date_trunc  degrees
exp  extract  floor  format  greatest  least  length  ln  localtime
localtimestamp  log  log10  log2  lower  ltrim  mod  now  nullif  pi
power  radians  rand  replace  round  rtrim  sign  sin  sqrt  substr  tan
trim  trunc  upper

Most are spelled the same everywhere. Where they are not, the method names one meaning and each adapter gets its own spelling: char_length, greatest and least become LENGTH, MAX and MIN on SQLite, and rand is RAND on MySQL and RANDOM elsewhere, and trunc is TRUNCATE on MySQL, which insists on the second argument the others default to zero — SQLite's takes only the one. Where an adapter has no equivalent — date_trunc outside PostgreSQL, now and the local* pair on SQLite, log2 on PostgreSQL, whose spelling is log(2, x) — the block raises NotImplementedError rather than leaving the database to reject the SQL.

current_date, current_time, current_timestamp, localtime and localtimestamp come out without parentheses, as the grammar has them — written as calls, PostgreSQL and SQLite would reject them. What does go into parentheses is an optional precision — current_timestamp(3) — which current_date never takes and SQLite never accepts. current_timestamp is the portable spelling of what now means, and reaches SQLite where now does not:

Post.where { :published_at <= current_timestamp }
# SELECT "posts".* FROM "posts" WHERE "posts"."published_at" <= CURRENT_TIMESTAMP

extract and cast are grammar as well: the field and the type go where no value could. The field has to be a plain name, and the type has to look like a type — a plain name, at most parenthesized with lengths, so the adapters' own spellings like double precision or decimal(10,2) pass; anything else raises ArgumentError. The type is the adapter's own name for the type, and whether it exists is the database's to say. SQLite spells everything extract does as strftime formats, which no renaming carries, so extract raises there:

Post.where { extract(:year, :created_at) == 2026 }
# SELECT "posts".* FROM "posts" WHERE EXTRACT(YEAR FROM "posts"."created_at") = 2026

Post.select { cast(:price, 'decimal(10,2)').as(:price) }
# SELECT CAST("posts"."price" AS decimal(10,2)) AS price

format is printf formatting, and raises on MySQL, where a function of the same name does something else entirely: it puts separators in a number, and reads a printf template as the number zero rather than complaining. fn still reaches it, spelled as the different thing it is:

Post.select { fn(:format, :amount, 2) }   # MySQL's, on purpose

Pass :* to count for COUNT(*), and distinct: true for COUNT(DISTINCT ...):

Author.group { :country }.having { count(:*) > 1 }
# SELECT "authors".* FROM "authors" GROUP BY "authors"."country" HAVING COUNT(*) > 1

Post.select { count(:author_id, distinct: true) }   # COUNT(DISTINCT "author_id")

Values are quoted by the adapter wherever they appear, as they are in ActiveRecord. Column aliases and fn's function name are not — they are written into the SQL as given — so those two have to be plain names, optionally qualified by a schema in fn's case. Anything else raises ArgumentError rather than reaching the query.

One place asks for a value to be said out loud: the top of a select list. Everywhere else a bare literal is already a value — where { :age > 18 }, concat(:name, '-x') — but ActiveRecord reads a string in select as SQL, so value is how you ask for the other meaning. It carries the predications and arithmetic with it, so a literal can be compared and combined like anything else. Numbers have a shorthand, since nothing else could be meant by one:

Node.select { [:id, value(0).as(:depth)] }
# SELECT "nodes"."id", 0 AS depth FROM "nodes"

Node.select { [:id, 0.as(:depth)] }         # the same thing

Post.select { [:title, value('draft').as(:state)] }
# SELECT "posts"."title", 'draft' AS state FROM "posts"

The shorthand is Integer and Float only. String keeps its two meanings — SQL in a select list, a value everywhere else — and refining it would make the same literal mean one thing or the other depending on whether it had been sent a message.

fn reaches functions without a method of their own. Its name is emitted as written, so a case-sensitive one can be spelled exactly:

Post.select { fn(:date_trunc, 'day', :created_at).as(:day) }
# SELECT date_trunc('day', "posts"."created_at") AS day

+, -, * and / build arithmetic. Ruby puts them above the comparison operators, so an expression groups the way it reads:

Item.where { :price * :quantity > 1000 }
Item.select { sum(:price * :quantity).as(:total) }

.asc and .desc take .nulls_first / .nulls_last. MySQL has no such syntax, but Arel emulates it there, so the resulting order is the same everywhere:

Author.order { :country.asc.nulls_last }

Author.
  joins(:posts) { :posts[:author_id] == :authors[:id] }.
  where { :posts[:published] == true }.
  group { :authors[:id] }.
  having { count(:posts[:id]) > 1 }.
  order { count(:posts[:id]).desc }.
  select {
    [
      upper(:authors[:name]).as(:author),
      count(:posts[:id]).as(:post_count),
      avg(:posts[:likes]).as(:avg_likes),
    ]
  }

Examples

examples/ holds runnable scripts, each printing the SQL it builds and, where the result is the point, the rows that come back. All but the last run against an in-memory SQLite database and need no setup.

| | | | --- | --- | | predicates.rb | the where vocabulary: ranges, sets, NULL, text matching | | subqueries.rb | in? with a relation, exists?, scalar subqueries | | expressions.rb | arithmetic, aggregates, functions, NULLS LAST | | complex_joins.rb | compound ON clauses, outer joins, a self join | | aggregations.rb | GROUP BY, HAVING and aggregates across joins | | ctes.rb | with and with_recursive | | postgresql.rb | array columns, regular expressions, ILIKE (needs a server) |

Performance

benchmark/query_building.rb compares building the same queries through the block DSL and through ActiveRecord's other argument styles. Only query construction (through to_sql) is measured — every style produces the same SQL, so execution costs the same regardless.

Queries built per second (ruby 4.1.0dev, ActiveRecord 8.1.3, one machine — treat the ratios, not the absolute numbers, as the result):

query	string	arel	block (this gem)	hash	relation and/or
simple equality	42.5k	42.0k	37.7k	31.5k	—
range (BETWEEN)	—	34.6k	32.7k	24.8k	—
LIKE	41.5k	41.0k	36.8k	—	—
compound AND/OR	34.4k	27.7k	24.4k	—	11.9k

Allocated memory per built query:

query	arel	block (this gem)	hash	string	relation and/or
simple equality	2,600 B	2,832 B	3,328 B	3,448 B	—
compound AND/OR	3,208 B	3,584 B	—	4,680 B	9,120 B

In short: the block DSL is 6–13% slower than hand-written Arel (which it compiles to), a little faster than hash conditions, and both faster and leaner than where(...).and(where(...).or(where(...))) relation chains, which pay for structural-compatibility checks and relation copies. The Proc#refined call itself costs about 150 ns of the ~25 μs build — the re-interpretation of the block is not where the time goes. Against a database round trip of tens to hundreds of microseconds, none of these differences are visible in an application.

One memory cost sits outside the per-query numbers above: to run a block under the refinements, Proc#refined deep-copies its instruction sequence, nested blocks included. The copy is made lazily on the refined proc's first call and memoized per block and refinement list for the life of the process, so it is paid once per where { ... } call site, not per query — the benchmark measures the copy at the size of the original (568 bytes for the simple-equality block, 888 bytes for the compound one), and a thousand further calls from the same call site copy nothing. Steady state, an application holds one extra copy of each distinct query block's bytecode: a few hundred bytes per call site. "Per call site" assumes blocks compiled once, as normal code is — building query blocks with a string eval mints a fresh instruction sequence per pass, each earning a copy of its own, and the memo keeps both alive for the life of the process.

Running the tests

The tests only build SQL, but they need a live connection to do it. SQLite is the default; set ADAPTER to run the same suite against another one.

rake test                    # sqlite3
ADAPTER=postgresql rake test
ADAPTER=mysql2 rake test
rake test:all                # all three in turn

PostgreSQL and MySQL are reached on 127.0.0.1 as the current user with no password, which is how the devcontainer sets them up. Override with DB_HOST, DB_USERNAME and DB_PASSWORD. The activerecord_refined_test database is created on first use.

The pg and mysql2 gems are in the Gemfile's db group, since building them needs the client libraries installed. Skip them if SQLite is all you need, which is what CI's SQLite job does:

bundle config set --local without db

CI runs all three, one job per adapter, with PostgreSQL and MySQL as service containers.

Releasing

Pushing a v* tag runs .github/workflows/push_gem.yml, which builds the gem and publishes it through RubyGems.org's trusted publishing, so no API key is stored anywhere.

bump patch --tag # or bump {major,minor} etc.
git push --follow-tags

Contributing

Fork it
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create new Pull Request