Arel

https://github.com/rails/arel/tree/7-1-stable https://github.com/rails/rails/blob/master/activerecord/lib/arel/predications.rb https://www.youtube.com/watch?v=ShPAxNcLm3o&feature=youtu.be

left_outer_joins is available in recent rails but usually you do not need joins. For example

# instead of joins
  def self.with_approved_picture
    left_outer_joins(:member_pictures)
      .where.not("member_pictures.id": nil)
      .where(member_pictures: { status: MemberPicture.statuses[:approved] })
      .distinct # since there could be multiple pictures
  end

# we can write
  def self.with_approved_picture
    where id: MemberPicture.approved.select(:member_profile_id)
  end

Online SQL to Arel www.scuttle.io EXCELLENT Playground https://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html

bundle console

cheatsheet https://devhints.io/arel

arel_table = User.arel_table
arel_table = Arel::Table.new(:users)
arel_col = arel_table[:email]

# select fields
# NOTE that AS should use string instead of symbols
arel_table.project(arel_col, arel_col.as('custom_name')).to_sql
# aggregates
arel_table.project arel_table[:age].sum
arel_table.project arel_table[:age].count.as('user_count')

# limit offset
arel_table.take(3).skip(2)

# order
arel_table.order(arel_col, arel_col.desc)

# where restrictions
arel_table.where arel_col.eq '[email protected]'
arel_col.eq 'string'
arel_col.gteq Date.today.beginning_of_month
arel_col.matches "%#{query}%"  # generate ILIKE

# AR use joins(:photos) but Arel use join
photos = Photo.arel_table
arel_table.join(photos, Arel::Nodes::OuterJoin).on(arel_col.eq photos[:user_id]).project(photos[:name].as('photo_name'))

# functions
SEPARATOR = Arel::Nodes.build_quoted(' ')

Arel::Nodes::NamedFunction.new(
  'concat',
  [arel_table[:first_name], SEPARATOR, arel_table[:last_name]]
)

True relation example is (found using online arel editor http://www.scuttle.io )

Arel::Nodes::SqlLiteral.new('1').eq(1)

# Arel::Nodes::SqlLiteral.new('true') is also true relation, but can not be
# first, ie Arel::Nodes::SqlLiteral.new('true').and raises error
# NoMethodError (undefined method `and' for "true":Arel::Nodes::SqlLiteral):

To execute you can use

User.select(Arel.star).to_sql # SELECT * FROM users
# note that if you use joins it will select all columns (id of joined table will
# override id of User) so better is to use with table
User.select(User.arel_table[Arel.star]).to_sql # SELECT users.* FROM users
User.select(User.arel_table[:email].as('m')).first.m

User.where(User.arel_table[:email].eq('Hi')) # it the same as User.where(email: 'Hi')
User.where(User.areal_table[:log_count].gt(200)) # SELECT users.* FORM users WHERE (users.log_count > 200)
User.where(
  User.arel_table[:email].eq('hi').and(
    User.arel_table[:id].eq(22).or(
      User.arel_table[:id].in(23,34)
    )
  )
)

Tips

in one request there should be only one query per table
instead of count use .size (which will use ruby .length if loaded). To force loading (for example to show sieze before .each is performed) you can use users.load.size. In this case there is no edditional query for users.each. Only way when .count is used is when you actually do not load all records, for example showing some link for all items. Similarly do not use empty?, any? without load. Do not use present? or blank? if you do not need to load all. exists? will always executes sql query.

do not write QueryMethods, FinderMethods, and Calculations inside methods on ActiveRecord objects since someone will eventually use then in index action and N+1 will be triggered. Use scopes instead if you need all nested recources (you can have several associations based on one table)

class Post
  has_many :comments
  has_many :active_comments, -> { active }, class_name: "Comment"
end

class Comment
  belongs_to :post
  scope :active, -> { where(soft_deleted: false) }
end

class PostsController
  def index
    @posts = Post.includes(:active_comments)
  end
end

to fetch all indexes for given table name use

@connection ||= ActiveRecord::Base.connection
@tables ||=
  if ActiveRecord::VERSION::MAJOR == 5
    connection.data_sources
  else
    connection.tables
  end
@models = ActiveRecord::Base.descendants
# reject table_name == 'schema_migrations'
@connection.indexes(@model.table_name)

to find belongs_to associations

model.reflects_on_all_associations(:belongs_to).

if you have composite index on column [a, b, c] than you can use where a == 1 or where a == 1 and b == 2 or where a == 1 and b == 2 and c == 3. you can not use where b == 2 alone since it will examine all rows, in aws rds console under database -> Logs & events -> Logs -> search for slowquery.log (with bigger size, created around the time the server was slow) you can see Rows_examinated: 123456 … Also you can find on left menu Performance Insights -> Database load -> Top SQL -> Rows examined
squash migrations, just generate new one and copy content from db/schema.rb You can use gem squasher https://gihttps://docs.aws.amazon.com/AWSEC2/latest/UserGuide/finding-an-ami.html#finding-quick-start-amihttps://docs.aws.amazon.com/AWSEC2/latest/UserGuide/finding-an-ami.html#finding-quick-start-amithub.com/jalkoby/squasher or you can manually merge migrations

if you override setters, for example

class User
  def name=(name)
    self.name = "#{company.name} child"
  end
end

company.users.new(name: 'Hi')
# this will raise error since company is is not defined so better is to use
company.users.new(name: 'Hi', company: company)

use OR or statement like

  Book.where(category: "Programming").or(Book.where(category: "Ruby"))

  member_profile.member_pictures.where(status: 'pending').or(member_profile.member_pictures.where(status: 'approved'))

  scope :web, -> { where.not("last_seen_user_agent LIKE '%iOS_app%' OR last_seen_user_agent LIKE '%Android_app%'").or(where last_seen_user_agent: nil) }

users.page(page).size when applied to ActiveRecord and pagination, it might raise
```
PG::UndefinedColumn: ERROR:  column "distance" does not exist
LINE 1: ...g" = $3 AND "member_profiles"."id" != $4 ORDER BY distance A...
```
but it works if you convert to array users.page(page).to_a.size (this exception is raised even when there is a single page, for example one result) Solution to this is using users = users.distinct

Activerecord Arel

Contents

Arel

Tips