I would love to be able to use SQL modifiers like lower inside an order clause, for example, without having to wrap the whole thing in Arel.sql. That solution just feels like moving the problem to a different junk drawer. I’m not sure what that would look like, since we already have the shorthand
order(foo: :desc)
It’s not clear to me how we reduce this to a similarly understandable DSL. But clearly, this is not it:
order(Arel.sql('lower(foo) DESC'))
Walter
Just my anecdote, in my application the majority of the time I am accessing Arel it is due to the lack of union support in AR (although by now most of that is doable with the active_record_union gem).
order(arel_table[:foo].lower.desc) would be how you’d write it using Arel right now (and isn’t too bad IMO), which wouldn’t need to be wrapped either. The arel_table[:foo] part is the least accessible right now but everything else looks like normal ruby.
That being said, LOWER seems to be a special case for a reason I can’t find (although maybe @tenderlove knows because he wrote it?); most SQL functions aren’t specifically defined.
That as probably needs to convert the param to sql for the table[:attr].as('foo') situation, where as the CTE situation needs to combine a table node with another query. But maybe it’s as easy as …
module Arel # :nodoc: all
module AliasPredication
def as(other)
if other.is_a? Arel::SelectManager
Nodes::As.new self, other
else
Nodes::As.new self, Nodes::SqlLiteral.new(other)
end
end
end
end
@rafaelfranca per https://github.com/rails/rails/pull/39150#issuecomment-624534319 I’m also here to help with Arel if needed. Feel free to ping me.
I think you’re probably right. Send a PR if you don’t mind?
One of the reasons why I frequently reach for Arel that hasn’t yet been mentioned is to patch in support for DB-specific things that the framework doesn’t support.
Since ActiveRecord is nominally DB-agnostic, a lot of the time it wouldn’t even be appropriate for the framework to do these things, but I still want to write it without string-munging.
Here’s a concrete example that I’ve implemented at multiple companies over the years: PostgreSQL supports GIN-indexed binary json fields with the jsonb data type
CREATE TABLE documents (
id bigint NOT NULL,
data jsonb
);
CREATE INDEX index_documents_on_data USING gin (data);
You can perform indexed queries against this data using the @> containment operator
SELECT * FROM documents WHERE data @> '{"foo":"bar"}'
This is pretty trivially easy to patch into Arel without stepping on any existing behavior
module Arel
module Nodes
class Contains < Arel::Nodes::InfixOperation
def initialize(left, right)
super(:'@>', left, right)
end
end
end
module Visitors
class PostgreSQL < Arel::Visitors::ToSql
alias_method :visit_Arel_Nodes_Contains, :visit_Arel_Nodes_InfixOperation
end
end
end
module ActiveRecord
module QueryMethods
def contains(predicates)
predicates = predicates.map do |column, predicate|
column = table[column]
predicate = column.type_cast_for_database(predicate)
predicate = Arel::Nodes.build_quoted(predicate)
where Arel::Nodes::Contains.new(column, predicate)
end
end
end
module Querying
delegate :contains, to: :all
end
end
Document.contains(foo: "bar")
The Arel framework has been remarkably stable over the years, and simple additions like this haven’t caused me any issues.
One thing that would make approaches like this less risky is to have clearer API boundaries between the ActiveRelation DSL and Arel.
It would be nice if there was a specific public API for passing Arel objects to ActiveRelation without having to depend on undocumented behavior of where. That seems like the most likely place where breakage could happen.
I once built a small “predicate builder” on top of ARel, PostgreSQL JSONB support and jQuery QueryBuilder. It wasn’t all powerful because it was servicing a specific need but I was amazed at the power of ARel combined with JSONB operators.
TLDR; I’m interested in helping, even by just writing documentation on ARel
The engine operated like this:
ANDs and ORs
The gist was done by stuff like:
module StringOperators
def string_in(model, rule)
# ...
left_op = model[field[:name]]
if field[:type] == :jsonb
# ["#{name} #> '{#{path}}' <@ json_build_array(?)::jsonb", values]
left_op = json_dash_single_arrow(left_op, field[:json_path])
values = field_value.map { |value| Arel::Nodes.build_quoted(value) }
right_op = Arel::Nodes::NamedFunction.new(
'CAST', [Arel::Nodes::NamedFunction.new(
'json_build_array', values).as('jsonb')])
Arel::Nodes::InfixOperation.new('<@', left_op, right_op)
else
left_op.in(field_value)
end
end
end
it also supported arrays:
left_op = json_dash_single_arrow(left_op, field[:json_path])
right_op = Arel::Nodes::SqlLiteral
.new("'{#{field[:value].join(',')}}'")
Arel::Nodes::InfixOperation.new('?|', left_op, right_op)
and datetimes:
value = Time.zone.parse(field[:value]).utc
if field[:type] == :jsonb
left_op = json_dash_double_arrow(left_op, field[:json_path])
value = value.iso8601
end
left_op.public_send(arel_operator, value)
json_dash_single_arrow and json_dash_double_arrow were very simple custom functions:
def json_dash_single_arrow(node, path)
Arel::Nodes::InfixOperation.new(
'#>', node, Arel::Nodes.build_quoted("{#{path}}"))
end
def json_dash_double_arrow(node, path)
Arel::Nodes::InfixOperation.new(
'#>>', node, Arel::Nodes.build_quoted("{#{path}}"))
end
all of this stuff was then combined with recursion and .reduce() to a final SQL query which combined the various operators.
Albeit complicated, it was working beautifully
Models were “ARelized” by using something like:
def [](name)
arel_table[name]
end
I had to “step debug” a lot to figure out how all of this came together as there was no official documentation.
Can you send the Contains node and the new method upstream please? Those types of things are fine to add. Adding methods to AR::QueryMethods is where we get in to more murky water since contains isn’t going to be supported by every db.
Sure thing, I’ll open a PR
Love Arel! I’m also supportive of it rolled into ActiveRecord or standalone. My usage is always within ActiveRecord but I can totally see why you’d want to use just Arel by itself.
I think that there is a big difference between Arel and the ActiveRecord query interface though; which is why things like ActiveRecord::Base::[] will be difficult. I would assume [] would have given me the column not an Arel::Attribute, but perhaps it’s possible to delegate the necessary methods so that it acts like a Arel::Attribute?
In the PR earlier from @tenderlove, I would also assume people have already overwritten by people, but I think you could still use [], people who override can just test if it’s a symbol being passed and return super, otherwise continue with their code path.
Some of the complaints with ActiveRecord::QueryMethods#order and Arel.sql(...) might be resolved by expanding the whats accepted. For the last couple of years I’ve been using my own extensions activerecord-sort, and also using a StrongParameters like filter to allow clients to select the ordering.
ActiveRecord::QueryMethods#where might be another reason why some people get into Arel, in a bunch of our apps we’ve been using activerecord-filter to support more complex conditions.
I wanted to share my usage of Arel to create a chainable advisory_lock scope that would lock and return the results of a Relation. It involves decomposing the original query (limit and order) and embedding some parts in a CTE.
It was interesting to discover how Arel changed between Rails 5.1 and Rails 5.2 in how it used bind parameters (or didn’t) in the AST, to the extent that making it work on Rails 5.1 was too difficult. These are the sharp edges that I think maintainers worry about, but I also found the tooling and testing (e.g. Appraisals) fairly easy for identifying problematic versions of Rails to dig into despite the lack of official documentation.
This is Postgres only unfortunately.
scope :advisory_lock, (lambda do
original_query = self
cte_table = Arel::Table.new(:rows)
composed_cte = Arel::Nodes::As.new(cte_table, original_query.select(primary_key).except(:limit).arel)
query = cte_table.project(cte_table[:id])
.with(composed_cte)
.where(Arel.sql(sanitize_sql_for_conditions(["pg_try_advisory_lock(('x'||substr(md5(:table_name || \"#{cte_table.name}\".\"#{primary_key}\"::text), 1, 16))::bit(64)::bigint)", { table_name: table_name }])))
limit = original_query.arel.ast.limit
query.limit = limit.value if limit.present?
unscoped.where(arel_table[:id].in(query)).merge(original_query.only(:order))
end)
This was done for creating an ActiveJob adapter that uses advisory locks. You can see the original implementation and a test that shows the resulting sql.
Just saw this PR which honestly makes me ಠ_ಠ
That would be a solved problem already if Arel was a public interface 
Two things I’ve already done in multiple codebases:
class ApplicationRecord < ActiveRecord::Base
def self.[](column)
arel_table[column]
end
end
module Arel
module Predications
alias_method :>, :gt
alias_method :<, :lt
alias_method :>=, :gteq
alias_method :<=, :lteq
alias_method :"!=", :not_eq
alias_method :==, :eq
end
end
where(Post[:created_at] <= 30.days.ago)
This solves the same problem as the above PR without introducing anything new to the framework, and it’s much more flexible than the symbol-parsing because you can use it in additional contexts, like
select([Post[:author].as("author_name")])
There also is the following PR which allows you to do:
# simple query
Post.where { updated_at.gt(1.day.ago) }
# deeply nested
Post .joins(comments: :user).where {
comments.user.first_name.eq("John")
}
@Adam_Lassek Support `where` with comparison operators (`>`, `>=`, `<`, and `<=`) by kamipo · Pull Request #39613 · rails/rails · GitHub was reverted for now and it is waiting for discussion again at Support `where` with comparison operators (`>`, `>=`, `<`, and `<=`) Take 2 by kamipo · Pull Request #39863 · rails/rails · GitHub.
Hey all, just wanted to get a bead on how this effort was going (and make sure it wasn’t abandoned or anything)
I’d love to help as well, but I’m not sure if there’s an ongoing discussion elsewhere as to the direction we’d want to take it in? There doesn’t seem to have been any activity on this thread since July of last year