Sunfox


Arel through examples

When a database query cannot be described using Ruby on Rails’ short ActiveRecord syntax, consider building your queries using Arel instead of going for raw SQL strings.

This has the following advantages:

  • Escaping by default: this helps not end up with SQL injections. We don’t like those.
  • Full names: using "users"."email" instead of email in your queries helps you be future-proof by making queries that won’t break when you merge queries together or add columns with conflicting names.
  • Composable: Arel makes allows you to merge advanced conditions by manipulating Ruby objects.
  • Uniform queries: it ensures that the generated SQL always looks the same. For example "users"."email" ASC instead of email asc. This makes it easier to search through logs and can help some caching mechanisms.

Shorthand for arel_table

When writing Arel in Rails you access columns through the arel_table method on your models like so: User.arel_table[:email]. However, there is a lovely little shortcut that is often added to shorten this:

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

This way you can write User[:email] instead of User.arel_table[:email] 🎉.


Here are some examples of using Arel, using the shorthand syntax.

Greater or lower than

Using lt, lteq, gt or gteq.

User.where(User[:created_at].lt(1.day.ago))
# => SELECT "users".* FROM "users" WHERE "users"."created_at" < '2021-01-01 00:00:00.000000'

Case insentive search

Using .lower.

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

Search with partial matches

Using matches or matches_regexp.

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

Composing conditions

Using and and or between conditions and using eq, not_eq, in, and not_in on columns.

is_admin = User[:admin].eq(true)
is_moderator = User[:moderator].eq(true).and(User[:activated_at].not_eq(nil))
User.where(is_admin.or(is_moderator))
# => SELECT "users".* FROM "users" WHERE ("users"."admin" = TRUE OR "users"."moderator" = TRUE AND "users"."activated_at" IS NOT NULL)

Math

Using +, -, /, *.

price = Order[:price_cents]
vat_rate = Order[:vat_rate]
Order.sum(price + price * vat_rate)
# => SELECT SUM(("orders"."price_cents" + "orders"."price_cents" * "orders"."vat_rate")) FROM "orders"

Aggregate functions

Such as sum, count, average, minimum or maximum.

User.select(:role, User[:id].minimum, User[:id].maximum).group(:role)
# =>  SELECT "users"."admin", MAX("users"."id"), MIN("users"."id") FROM "users" GROUP BY "users"."role"

Grouping and filtering by columns from other tables

Since the table_name: :column_name syntax isn’t accepted in ActiveRecord’s group and having.

User.joins(:orders).group(Order[:id]).having(Order[:created_at].lt(1.year.ago)).count
# => SELECT COUNT(*) AS count_all, "orders"."id" AS orders_id FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id" GROUP BY "orders"."id" HAVING "orders"."created_at" < '2020-02-20 17:51:14.117027'

Plucking columns from other tables

Since the table_name: :column syntax isn’t accepted in ActiveRecord’s pluck and pick.

User.joins(:comments).pluck(Comment[:id])
# => SELECT "comments"."id" FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id"

Transforming timestamps

Extracting parts of a date or time with extract.

User.distinct.pluck(User[:created_at].extract("year").as("year"))
# => SELECT DISTINCT EXTRACT(YEAR FROM "users"."created_at") AS year FROM "users"

Ordering by other tables

Since ActiveRecord’s order does not accept the table: :column syntax, you can reference an Arel column directly, optionally followed by asc or desc.

User.joins(:comments).order(Comment[:created_at].desc)
# => SELECT "users".* FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" ORDER BY "comments"."created_at" DESC

Ordering with null values

Using nulls_last or nulls_first.

User.order(User[:email].desc.nulls_last)
# => SELECT "users".* FROM "users" ORDER BY "users"."email" ASC NULLS LAST

Ordering by a custom order

Using Arel::Nodes::Case.

User.order(
  Arel::Nodes::Case
    .new(User[:role])
    .when("admin").then(1)
    .when("editor").then(2)
)
# => SELECT "users".* FROM "users" ORDER BY CASE "users"."role" WHEN 'admin' THEN 1 WHEN 'editor' END

Querying values without NULLs

Because .where.not can be counter-intuitive, it can help to use is_distinct_from.

User.where(User[:active].is_distinct_from(true))
# => SELECT "users".* FROM "users" WHERE "users"."active" IS DISTINCT FROM TRUE

Naming fields

Use as to name calculated fields.

User
  .select(User[Arel.star])
  .select(Order[:id].count.as("orders_count"))
  .joins(:orders)
  .group(:id)
# => SELECT "users".*, COUNT("orders"."id") AS orders_count FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id" GROUP BY "users"."id"

Calling other functions

Use Arel::Nodes::NamedFunction to call SQL functions such as ROUND. Here we are also using Arel.star to fetch all fields from a table.

score = User[:followers_count] / User[:followees_count]
rounded_score = Arel::Nodes::NamedFunction.new("ROUND", [commission])

User
  .select(User[Arel.star])
  .select(rounded_score.as("score"))
# => SELECT "users".*, ROUND("users"."followers_count" / "users"."followees_count") AS score FROM "users"

Custom SQL

Use Arel.sql and to_sql if you need to compose raw SQL strings with Arel nodes and ActiveRecord scopes.

articles_sql = Arel.sql(Article.hidden.select(:id).to_sql)
hidden_article_id = Comment[:article_id].in(articles_sql)
is_spam = Comment[:is_spam].eq(true)

Comment.where(hidden_article_id.or(is_spam))
# => SELECT "comments".* FROM "comments" WHERE ("comments"."article_id" IN (SELECT "articles"."id" FROM "articles" WHERE "articles"."hidden" = TRUE)) OR "comments"."is_spam" = TRUE

A final word of warning

A caveat to keep in mind is that Arel is considered a private API and these calls could break in future Rails versions (7+). However, major breaking changes aree unlikely since Arel was a public gem before it was integrated to Rails 5 and a lot of developers are using them to simplify their queries.

As always, whether you are building custom SQL or using Arel, make sure your code is covered by tests.

Happy Areling! ✨

Ajouter un commentaire

Vous pouvez y saupoudrer de l’HTML avec les balises et suivantes : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>. Vous pouvez avoir un avatar en créant un gravatar.

👨🏻‍🦰 Sunny Ripert

est un développeur web vivant à ParisContactArchives

Textes et contenus sous licence Creative Commons.