Stan Blog: [Rails] 使用 merge 合併查詢子句

在寫 rails query 時, 常常會寫出這樣的句子

User.joins([posts: :likes]).where('posts.created_at': 30.days.ago..Time.current).order("posts.id DESC")

如果條件越來越複雜, query 會寫得越來越長

這時候可以使用 ActiveRecord::SpawnMethods的 merge 來整理 query

假設我們有三個 tables

class User < ApplicationRecord
  has_many :posts
  has_many :likes
  has_many :liked_posts, through: :likes, source: :post
end

class Post < ApplicationRecord
  belongs_to :user
  has_many :likes, dependent: :destroy
  has_many :liked_users, through: :likes, source: :user
end

class Like < ApplicationRecord
  belongs_to :user
  belongs_to :post
end

我們想要找出 post 被按了最多 like 的 user 列表

這是一般的寫法

User.joins([posts: :likes]).group(:id).order("count(posts.id) DESC")

會跑出這樣的 SQL

SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" INNER JOIN "likes" ON "likes"."post_id" = "posts"."id" GROUP BY "users"."id" ORDER BY count(posts.id) DESC

可以用 merge 這樣整理

# 先找出按讚數最多的文章，並排序
by_likes_count = Post.joins(:likes).group(:id).order("count(posts.id) DESC")

# 然後用 User 直接 merge
User.joins(:posts).merge(by_likes_count)

這時候跑出的 SQL 會是

SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" INNER JOIN "likes" ON "likes"."post_id" = "posts"."id" GROUP BY "users"."id" ORDER BY count(posts.id) DESC

與上面直接寫 query 的版本，轉換後的 SQL query 是一樣的

如果還想整理得更漂亮, 可以拆進 model scope, 再去 merge scope

# Post model
scope :sort_by_likes_count, -> { joins(:likes).group(:id).order("count(posts.id) DESC") }

User.joins(:posts).merge(Post.sort_by_likes_count)

Ref:

ActiveRecord::SpawnMethods