実際に使用したActive Record関連処理のまとめ

説明で使用するテーブルのER図

through(多対多関連)

class Hoge < ActiveRecord::Base
  has_many :hoge_categories
  has_many :categories, through: :hoge_categories
end

class HogeCategory < ActiveRecord::Base
  belongs_to :hoge
  belongs_to :category
end

class Category < ActiveRecord::Base
  has_many :hoge_categories
  has_many :hoges, through: :hoge_categories
end

実装例

[1] pry(main)> Category.find_by(id: 1).hoges
  Category Load (0.3ms)  SELECT  `categories`.* FROM `categories` WHERE `categories`.`id` = 1 LIMIT 1

  Hoge Load (0.3ms)  SELECT `hoges`.* FROM `hoges` INNER JOIN `hoge_categories` ON `hoges`.`id` = `hoge_categories`.`hoge_id` WHERE `hoge_categories`.`category_id` = 1

=> [#<Hoge:0x007fad7b5006d8 id: 1, name: "hogehoge", status: 0, created_at: Thu, 26 Nov 2015 10:14:41 JST +09:00, updated_at: Thu, 26 Nov 2015 10:14:41 JST +09:00>]

enum

class Hoge < ActiveRecord::Base
  has_many :hoge_categories
  has_many :categories, through: :hoge_categories

  enum status: {
           display: 0,
           hide: 1,
       }
end

statusが0の情報を取得

[1] pry(main)> Hoge.display
  Hoge Load (0.4ms)  SELECT `hoges`.* FROM `hoges` WHERE `hoges`.`status` = 0

key + ?

[1] pry(main)> Category.find_by(id: 1).hoges
  Category Load (0.3ms)  SELECT  `categories`.* FROM `categories` WHERE `categories`.`id` = 1 LIMIT 1
  Hoge Load (0.4ms)  SELECT `hoges`.* FROM `hoges` INNER JOIN `hoge_categories` ON `hoges`.`id` = `hoge_categories`.`hoge_id` WHERE `hoge_categories`.`category_id` = 1

=> [#<Hoge:0x007fce0797b508 id: 1, name: "hogehoge", status: 1, created_at: Thu, 26 Nov 2015 15:47:52 JST +09:00, updated_at: Thu, 26 Nov 2015 15:47:52 JST +09:00>,
 #<Hoge:0x007fce0797b300 id: 2, name: "hogehoge2", status: 0, created_at: Thu, 26 Nov 2015 15:53:35 JST +09:00, updated_at: Thu, 26 Nov 2015 15:53:35 JST +09:00>]

↑ の結果から select(&:hide?) で、statusが1の情報に絞る

[2] pry(main)> Category.find_by(id: 1).hoges.select(&:hide?)
  Category Load (0.3ms)  SELECT  `categories`.* FROM `categories` WHERE `categories`.`id` = 1 LIMIT 1
  Hoge Load (0.3ms)  SELECT `hoges`.* FROM `hoges` INNER JOIN `hoge_categories` ON `hoges`.`id` = `hoge_categories`.`hoge_id` WHERE `hoge_categories`.`category_id` = 1

=> [#<Hoge:0x007fce07a94e30 id: 1, name: "hogehoge", status: 1, created_at: Thu, 26 Nov 2015 15:47:52 JST +09:00, updated_at: Thu, 26 Nov 2015 15:47:52 JST +09:00>]

joins

[1] pry(main)> Preset.joins(:category)
  Preset Load (0.3ms)  SELECT `presets`.* FROM `presets` INNER JOIN `categories` ON `categories`.`id` = `presets`.`category_id`

複数のjoins

[1] pry(main)>  Preset.joins(category: [hoge_categories: :hoge])
  Preset Load (0.3ms)  SELECT `presets`.* FROM `presets` INNER JOIN `categories` ON `categories`.`id` = `presets`.`category_id` INNER JOIN `hoge_categories` ON `hoge_categories`.`category_id` = `categories`.`id` INNER JOIN `hoges` ON `hoges`.`id` = `hoge_categories`.`hoge_id`

↑ はthroughしているので↓でいけます。
[2] pry(main)> Preset.joins(category: :hoges)
  Preset Load (0.3ms)  SELECT `presets`.* FROM `presets` INNER JOIN `categories` ON `categories`.`id` = `presets`.`category_id` INNER JOIN `hoge_categories` ON `hoge_categories`.`category_id` = `categories`.`id` INNER JOIN `hoges` ON `hoges`.`id` = `hoge_categories`.`hoge_id`

複数joinsのwhere

[15] pry(main)> Preset.joins(category: :hoges).where(hoges: { name: 'hogehoge' })
  Preset Load (0.5ms)  SELECT `presets`.* FROM `presets` INNER JOIN `categories` ON `categories`.`id` = `presets`.`category_id` INNER JOIN `hoge_categories` ON `hoge_categories`.`category_id` = `categories`.`id` INNER JOIN `hoges` ON `hoges`.`id` = `hoge_categories`.`hoge_id` WHERE `hoges`.`name` = 'hogehoge'

scope

class Preset < ActiveRecord::Base
  belongs_to :category, required: true

  scope :hoges, -> { joins(category: :hoges) }
end

class Category < ActiveRecord::Base
  has_many :hoge_categories
  has_many :hoges, through: :hoge_categories

  scope :by_group_type, ->(group_type) { where(group_type: group_type) } 
end

[1] pry(main)> Category.by_group_type(1)
  Category Load (0.3ms)  SELECT `categories`.* FROM `categories` WHERE `categories`.`group_type` = 1

merge

[1] pry(main)> Preset.hoges.merge(Category.by_group_type(1))
  Preset Load (0.5ms)  SELECT `presets`.* FROM `presets` INNER JOIN `categories` ON `categories`.`id` = `presets`.`category_id` INNER JOIN `hoge_categories` ON `hoge_categories`.`category_id` = `categories`.`id` INNER JOIN `hoges` ON `hoges`.`id` = `hoge_categories`.`hoge_id` WHERE `categories`.`group_type` = 1

生SQL

sql = "select * from categories where name = 'hogehoge' and group_type = 1"

ActiveRecord::Base.connection.select_all(sql)

エスケープ処理させる場合

sql = 'select * from categories where name = ? and group_type = ?'

sanitize_sql = ActiveRecord::Base.send(:sanitize_sql_array, [sql, 'hogehoge', 1])

ActiveRecord::Base.connection.select_all(sanitize_sql)

arel_table

class Preset < ActiveRecord::Base
  belongs_to :category, required: true

  scope :hoges, -> { joins(category: :hoges) }

  scope :preset_ids, lambda { |job_id, employ_id|
     where((arel_table[:job_id].eq(job_id).or(arel_table[:job_id].eq(nil)))
            .and(arel_table[:employ_id].eq(employ_id).or(arel_table[:employ_id].eq(nil))))
   }
end

scopeを実行

[1] pry(main)> Preset.preset_ids(1,1)
  Preset Load (0.3ms)  SELECT `presets`.* FROM `presets` WHERE ((`presets`.`job_id` = 1 OR `presets`.`job_id` IS NULL) AND (`presets`.`employ_id` = 1 OR `presets`.`employ_id` IS NULL))