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
      .where.not("": nil)
      .where(member_pictures: { status: MemberPicture.statuses[:approved] })
      .distinct # since there could be multiple pictures

# we can write
  def self.with_approved_picture
    where id:

Online SQL to Arel EXCELLENT Playground

bundle console


arel_table = User.arel_table
arel_table =
arel_col = arel_table[:email]

# select fields
# NOTE that AS should use string instead of symbols
# aggregates
arel_table.project arel_table[:age].sum
arel_table.project arel_table[:age]'user_count')

# limit offset

# 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.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_table[:first_name], SEPARATOR, arel_table[:last_name]]

True relation example is (found using online arel editor )'1').eq(1)

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

To execute you can use # 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[]).to_sql # SELECT users.* FROM users[: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)


  • 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"
    class Comment
      belongs_to :post
      scope :active, -> { where(soft_deleted: false) }
    class PostsController
      def index
        @posts = Post.includes(:active_comments)
  • to fetch all indexes for given table name use
    @connection ||= ActiveRecord::Base.connection
    @tables ||=
      if ActiveRecord::VERSION::MAJOR == 5
    @models = ActiveRecord::Base.descendants
    # reject table_name == 'schema_migrations'
  • to find belongs_to associations
  • 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://gi or you can manually merge migrations
  • if you override setters, for example
    class User
      def name=(name) = "#{} child"
    end 'Hi')
    # this will raise error since company is is not defined so better is to use '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) }
  • 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 (this exception is raised even when there is a single page, for example one result) Solution to this is using users = users.distinct