Arel Cheatsheet

cs.md

content copied from Advanced Arel: When ActiveRecord Just Isn't Enough

further information:

• arel_cheatsheet_on_steroids
• Rails test examples
• Arel helpers
• Complex Arel queries

Arel Cheatsheet

SELECT:

column accessors:

Post.arel_table[:id]
Post.arel_table[:text]

column methods:

Post.select(Post.arel_table[:visitors]).sum.as('vistor_total') =>
SELECT SUM(`posts`.`views`) AS visitor_total FROM `posts`

Post.arel_table[:visitors].sum => SUM(`posts`.`views`)
Post.arel_table[:visitors].maximum => MAX(`posts`.`views`)
Post.arel_table[:visitors].minimum => MIN(`posts`.`views`)

custom column methods:

Post.select(Arel::Nodes::NamedFunction.new('LENGTH', [Post.arel_table[:text]]).as('length')) =>
SELECT LENGTH(`posts`.`text`) AS length FROM `posts`

all columns (star):

  Post.select(Arel.star) => SELECT * FROM `posts`

from table:

  Post.select(:id).from(Post.select([:id, :text]).ast) =>
  SELECT id FROM SELECT id, text FROM `posts`

WHERE:

simple equality where clause: (superfluous, covered by active record)

  Post.where(Post.arel_table[:title].eq('Arel is cool')) =>
  SELECT `posts`.* FROM `posts` WHERE `posts`.`title` = 'Arel is cool'

non-standard where clause:

  Post.arel_table[:title].not_eq('arel') => `posts`.`title` != 'arel'
  Post.arel_table[:title].not_eq(nil) => `posts`.`title` IS NOT NULL
  Post.arel_table[:value].gt(25) => `posts`.`value` > 25
  Post.arel_table[:value].gteq(25) => `posts`.`value` >= 25
  Post.arel_table[:value].lt(25) => `posts`.`value` < 25
  Post.arel_table[:value].lteq(25) => `posts`.`value` <= 25
  Post.arel_table[:value].in(25, 26, 27, 28) => `posts`.`value` in (25, 26, 27, 28)

combining where clauses with AND:

  Post.where(Post.arel_table[:title].eq('Arel is cool').and(Post.arel_table[:id].in(25, 26))) =>
  SELECT `posts`.* FROM `posts` WHERE (`posts`.`title` = 'Arel is cool' AND `posts`.`id` IN (25, 26))

combining where clauses with OR:

  Post.where(Post.arel_table[:title].eq('Arel is cool').or(Post.arel_table[:id].in(25, 26))) =>
  SELECT `posts`.* FROM `posts` WHERE (`posts`.`title` = 'Arel is cool' OR `posts`.`id` IN (25, 26))

combining where clauses with AND and OR:

  Post.where(
    Post.arel_table[:title].eq('Arel is cool').and(
      Post.arel_table[:id].in(25, 26).or(
        Post.arel_table[:archived].eq(false)
      )
    )
  ) =>
  SELECT `posts`.* FROM `posts` WHERE (
    `posts`.`title` = 'Arel is cool' AND (
      `posts`.`id` IN (25, 26) OR
      `posts`.`archived` = FALSE
    )
  )

JOIN:

joins defined in active record models: (superfluous, covered by active record)

  Author.joins(:comment)
        .joins(Comment.joins(:post).join_sources)
        .where(Post.arel_table[:id].eq(42)) =>
  SELECT `authors`.* FROM `authors`
  INNER JOIN `comments`
    ON `comments`.`id` = `authors`.`comment_id`
  INNER JOIN `posts`
    ON `posts`.`id` = `comments`.`post_id`
  WHERE `posts`.`id` = 42

custom inner joins:

  Author.joins(
      Author.arel_table
            .join(Comment.arel_table)
            .on(Comment.arel_table[:id].eq(Author.arel_table[:comment_id]))
            .join_sources
  ).joins(
    Comment.arel_table
           .join(Post.arel_table)
           .on(Post.arel_table[:id].eq(Comment.arel_table[:post_id]))
           .join_sources
  ).where(Post.arel_table[:id].eq(42)) =>
  SELECT `authors`.* FROM `authors`
  INNER JOIN `comments`
    ON `comments`.`id` = `authors`.`comment_id`
  INNER JOIN `posts`
    ON `posts`.`id` = `comments`.`post_id`
  WHERE `posts`.`id` = 42

custom outer joins

  Author.joins(
    Author.arel_table
          .join(Comment.arel_table, Arel::OuterJoin)
          .on(Comment.arel_table[:id].eq(Author.arel_table[:comment_id]))
          .join_sources
  ).joins(
    Comment.arel_table
           .join(Post.arel_table, Arel::OuterJoin)
           .on(Post.arel_table[:id].eq(Comment.arel_table[:post_id]))
           .join_sources
  ).where(Post.arel_table[:id].eq(42)) =>
  SELECT `authors`.* FROM `authors`
  LEfT OUTER JOIN `comments`
    ON `comments`.`id` = `authors`.`comment_id`
  LEfT OUTER JOIN `posts`
    ON `posts`.`id` = `comments`.`post_id`
  WHERE `posts`.`id` = 42

ORDER:

active record way:

  Post.order(:value) => SELECT `posts`.* FROM `posts` ORDER BY value
  Post.order(:value).reverse_order => SELECT `posts`.* FROM `posts` ORDER BY value DESC

arel way:

  Post.order(Post.arel_table[:value].desc) => SELECT `posts`.* FROM `posts` ORDER BY value desc

IN:

  Post.where(
    Post.arel_table[:title].in(
      Post.select(:title).where(id: 5).ast
    )
  ) =>
  SELECT `post`.* FROM `posts`
  WHERE `posts`.`title` IN (
    SELECT title FROM `posts`
    WHERE `post`.`id` = 5
  )

MATCHES:

  Post.where(Post.arel_table[:title].matches('%arel%')) =>
  SELECT `posts`.* FROM `posts` WHERE (`posts`.`key` LIKE X'256182656c25')