a = A.arel_table b = B.arel_table c = C.arel_table a .join(b) .on(a[:id].eq(b[:a_id])) .join(c, Arel::Nodes::OuterJoin) .on(b[:id].eq(c[:b_id])).to_sql

If Arel does not have the predicate you are looking for, consider monkey patching it:

Arel::Nodes::InfixOperation.new('->>', parent.table[:properties], 'link_type')

As far as I see the current version of arel gem is not support FROM keyword for the sql query. You can generate a query using the SET, and WHERE keywords only,

Beer.scoped.arel.class => Arel::SelectManager

beer = Beer.arel_table union = Beer.where(name: "Oberon") \ .union(Beer.where(name: "Two Hearted")) Beer.from(beer.create_table_alias(union, :beers)).all

join = Arel::Nodes::NamedFunction.new('json_b_array_elements', [Arel::Nodes::SqlLiteral.new("subscriptions")]) .as(Arel::Nodes::NamedFunction.new('sd', [Arel::Nodes::SqlLiteral.new("subscription_data")]).to_sql) p = e.project( Arel::Nodes::SqlLiteral.new( Arel::Nodes::Grouping.new( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'id'"))).to_sql) << '::uuid' ).where( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'type'").eq( Arel::Nodes::SqlLiteral.new("'Company'") ) ).and(e[:slug].eq(event_slug))) p.join_sources << Arel::Nodes::StringJoin.new( Arel::Nodes::SqlLiteral.new('CROSS JOIN LATERAL')) << join

any_role = Arel::Nodes::NamedFunction.new("ANY", [User[:roles]])

c2 = Comment.arel_table.alias s1 = Comment.arel_table. project(c2[:user_id], c2[:created_at].maximum.as('max_created_at')). from(c2).group('user_id').as('s1') puts s1.to_sql # (SELECT "comments_2"."user_id", MAX("comments_2"."created_at") AS max_created_at # FROM "comments" "comments_2" GROUP BY user_id) s1

Generates the following sql in sqlite3: "SELECT \"patients\".* FROM \"patients\" INNER JOIN \"users\" ON \"users\".\"id\" = \"patients\".\"user_id\" WHERE (\"users\".\"name\" LIKE '%query%')" And the following sql in postgres (notice the ILIKE): "SELECT \"patients\".* FROM \"patients\" INNER JOIN \"users\" ON \"users\".\"id\" = \"patients\".\"user_id\" WHERE (\"users\".\"name\" ILIKE '%query%')" This allows you to join with simplicity, but still get the abstraction of the ARel matcher to your RDBMS.

def self.current_table_name current_table = current_scope.arel.source.left case current_table when Arel::Table current_table.name when Arel::Nodes::TableAlias current_table.right else fail end end

Product. joins(Arel::Nodes::InnerJoin.new(subquery, Arel::Nodes::On.new( t[:version_id].eq(subquery[:version_id]).and( t[:date].eq(subquery[:max_date])))))

Comment[:article_id].in(Arel.sql(articles_sql))

Query values including NULLs Because .where.not can be counter-intuitive regarding NULL values, you can use is_distinct_from to include them. User.where(User[:active].is_distinct_from(true))

User.order(User[:email].desc.nulls_last)

User.order( Arel::Nodes::Case .new(User[:role]) .when("admin").then(1) .when("editor").then(2) )

User.distinct.pluck(User[:created_at].extract("year"))

space = Arel.sql("' '") User.pluck(User[:first_name].concat(space).concat(User[:last_name]))

User.where(User[:email].matches("%@example.org")) # => SELECT "users".* FROM "users" WHERE "users"."email" ILIKE '%@example.org'

User.where(User[:email].lower.eq(email.downcase)) # => SELECT "users".* FROM "users" WHERE LOWER("users"."email") = 'user@example.org'

class ApplicationRecord < ActiveRecord::Base def self.[](attribute) arel_table[attribute] end end

scope :with_spec_options, ->(spec_options) { where("specs->'spec_option' @> ?", spec_options.to_json) }

where("specs->'spec_option' ?| array[:options]", options: spec_options)

where_values_hash.reduce(&:or)

scope :deck_access, ->(user) { includes(:deck_permissions).where(where({ deck_permissions: { user_id: user.id, read_access: true } }, global_deck_read: true, user_id: user.id).where_values_hash.reduce(&:or)) }

user_table = User.arel_table a = user_table.project(Arel.star, Arel.sql("1 as car_id")).take(2) b = user_table.project(Arel.star, Arel.sql("2 as car_id")).take(2) union = Arel::Nodes::UnionAll.new(a,b) User.from(Arel::Nodes::As.new(union,user_table))

between(other)

between = Arel::Nodes::Between.new( Arel.sql('current_date'), valid_from.and(valid_to))

Core to arel is per-query / per-clause chainability: “build at your leisure, one scope at a time”

Let’s take a look at one more visitor, Arel::Visitors::Dot. The visitor generates the Graphviz’s Dot format and we can use it to create diagrams out of an AST.

Arel’s ComposabilityInternally Arel functions on AST nodes and every time an Arel method is made, the nodes in the AST will be modified accordingly. This is an important nature of how Arel works — composability, so that it’s able to build queries in a flexible way. With this abstract representation, we are able to safely add different filters and operations to the same query and even combine queries regardless of its order. Arel is able to generate the correct SQL query from it.

Arel’s responsibility is SQL query construction and optimization, and it knows very little about ActiveRecord’s Models and nothing about the database. Arel provides the basic building blocks for ActiveRecord.

You might be thinking––"a tool that allows me to write semantic and reusable queries? Sounds like Active Record". It's absolutley true that Active Record already provides a powerful query tool kit. But what happens when even simple queries stretch the bounds of Active Record's capabilities?

Arel is a SQL AST (Abstract Syntax Tree-like) manager for Ruby. It allows us to write complex SQL queries in a semantic, reusable fashion. Arel is "framework framework"; it's designed to optimize object and collection modeling over database compatibility. For example, Active Record is built on top of Arel.

joins { a.as('addressable').on { id.eq(addressable.id) }.outer } .select { ["users.*", addressable.id.as('addressable')] }