Railsでhas_manyな関連先の最新の一件が条件を満たすのを取得

==================================================

参考

• sql - Rails query through association limited to most recent record_ - Stack Overflow

class User < ActiveRecord::Base
  has_many :stamps
  has_one  :last_stamp, ->{ order(created_at: :desc) }, class_name: 'Stamp'
end

class Stamp < ActiveRecord::Base
  belongs_to :user

  scope :_this_week, -> { where(created_at: Date.today.all_week) }
end

というのがあって、最後のStampが今週に作成されたUser一覧を取得したいというとき

User.joins(:last_stamp).merge(Stamp._this_week)

と書いても

SELECT "users".* FROM "users" INNER JOIN "stamps" ON "stamps"."user_id" = "users"."id" WHERE ("stamps"."created_at" BETWEEN '2015-12-21' AND '2015-12-27')

という具合に、普通にINNER JOINされて最後のStamp以外もひっかかってしまう。

こうするとうまくいく

User.joins(:stamps).where('stamps.created_at = (SELECT MAX(stamps.created_at) FROM stamps WHERE stamps.user_id = users.id)').merge(Stamp._this_week).group('users.id')

SELECT "users".* FROM "users" INNER JOIN "stamps" ON "stamps"."user_id" = "users"."id" WHERE (stamps.created_at = (SELECT MAX(stamps.created_at) FROM stamps WHERE stamps.user_id = users.id)) AND ("stamps"."created_at" BETWEEN '2015-12-21' AND '2015-12-27') GROUP BY users.id

メソッド化

app/models/user.rb

class User < ActiveRecord::Base
  has_many :stamps
  has_one  :last_stamp, ->{ order(created_at: :desc) }, class_name: 'Stamp'

  scope :_last_stamp_this_week, -> {
    last_stamp_merge { Stamp._this_week }
  }

  private
    def self.last_stamp_merge(&:block)
      joins(:stamps).where(<<-SQL.squish!
        stamps.created_at = (
          SELECT MAX(stamps.created_at)
          FROM  stamps
          WHERE stamps.user_id = users.id
        )
        SQL
      ).merge(yield).group('users.id')
    end
end

app/models/stamp.rb

class Stamp < ActiveRecord::Base
  belongs_to :user

  scope :_this_week, -> { where(created_at: Date.today.all_week) }
end