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" = TRUEThis 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)
endThis is exactly what the old implementation needed and could not do, so the query syntax
works again without monkey-patching Symbol globally.
- Ruby 4.1 or later (for
Proc#refined; not released yet, so aruby-masterbuild is needed for now) - ActiveRecord 7.0 or later
Add this line to your application's Gemfile:
gem 'activerecord-refined'
And then execute:
$ bundle
Or install it yourself as:
$ gem install activerecord-refined
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.
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 NULLin? 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?('Matz') } # LOWER(name) = LOWER('Matz')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 rowsstart_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 tooOnly 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%')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"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 takes the CTE's name as a symbol, with as to select it under the
model's own table name so the model's columns resolve:
Node.with_recursive(
tree: [
Node.where { :id == root.id },
Node.joins(:tree) { :nodes[:parent_id] == :tree[:id] },
]
).from(:tree, as: :nodes)
# WITH RECURSIVE "tree" AS (
# SELECT "nodes".* FROM "nodes" WHERE "nodes"."id" = 1
# UNION ALL
# SELECT "nodes".* FROM "nodes" INNER JOIN "tree" ON "nodes"."parent_id" = "tree"."id"
# ) SELECT "nodes".* FROM "tree" AS "nodes"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.
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 ceil char_length coalesce concat date_trunc exp floor format
greatest least length ln log lower ltrim mod now nullif power rand
replace round rtrim sqrt substr trim 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. Where an adapter has no equivalent — date_trunc
outside PostgreSQL, now on SQLite — the block raises NotImplementedError
rather than leaving the database to reject the SQL.
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 purposePass :* 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.
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/ 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) |
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.
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 turnPostgreSQL 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 dbCI runs all three, one job per adapter, with PostgreSQL and MySQL as service containers.
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- 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