has_many through association records duplication problem with Subquery solution

by hothero

Rails 有非常完整的 ORM 機制，也是許多網站框架還難以超越的部分，其中的 has_many, has_one, belongs_to 等等的 association 拯救了不知多少工程師的白頭髮，讓大家少花了多少打 SQL 語法的歲月.....但近期著實被 has_many through 狠狠地上了一課，主要是 join 導致加總了重複的紀錄，使得金額與數量計算上出現大幅的落差。

先簡述一下我們的系統，就是一個電商架構的網站，其中 Transaction 與 TransactionItem 用來記錄每一筆交易買了哪些商品，那每個專案則理所當然的透過 transaction_items 來去找到 transaction_record 加總金額，即可知道共收到了多少錢。

（這邊姑且先不探討關聯設計上的問題，先針對 has_many through 上的重複紀錄產生原因來做討論）

class Transaction < ActiveRecord::Base
  has_many :transaction_items
end

class TransactionItem < ActiveRecord::Base
  belongs_to :transaction_record
  belongs_to :project
end

class Project < ActiveRecord::Base
  has_many :transaction_items
  has_many :transaction_records, through: :transaction_items
end

在這樣的架構下，若使用 project.transaction_records.success.sum(:money) 這樣的語法，看起來是沒有問題，但因為 rails 的 ORM 建構出的 sql 語法使用到了 join，會導致 transaction_record 因為多筆的 transaction_item 而重複了，使得 money 欄位計算重複加總了。

SELECT SUM("transactions"."money") FROM "transactions" INNER JOIN "transaction_items" ON "transactions"."id" = "transaction_items"."transaction_id" WHERE "transactions"."deleted_at" IS NULL AND "transaction_items"."deleted_at" IS NULL AND "transaction_items"."project_id" = $1 AND "transactions"."status" = $2  [["project_id", 1], ["status", 1]]

所以以下面的資料來說

正常出來的數值應為 4200，但最後出來卻是 9800，差了超過一倍，這是因為 has_many through 產生的 inner join query，導致 Transaction 會因為 has_many TransactionItem 的數量而複製了這麼多筆導致。

光是要發現這個問題就不容易（過程沒什麼訣竅，就是要百般懷疑）...一般來說是不會懷疑到 rails ORM 的問題，中間我們嘗試過幾種解法：

一、 比如把 Project 的 has_many association 加上 distinct:

class Project < ActiveRecord::Base
  has_many :transaction_items
  has_many :transaction_records, -> { distinct }, through: :transaction_items
end

這是 stackoverflow 上許多人的解法，但以此例來說，完全沒辦法解決，因爲出來的 query 會是

select DISTINCT SUM("transactions"."money") FROM "transactions" ....

針對一個 sum 的欄位做 distinct 想當然爾是沒用的

二、 直接使用 outer join 內的 LEFT/RIGHT join==（這就是問題！）==

TransactionItem.joins("RIGHT JOIN transactions ON transaction_items.transaction_id = transactions.id").where(project_id: 1).sum("transactions.money")

正常來講應會成功，但再仔細思考，不管是 inner 還是 outer join 都只是一種排解 NULL Association 的方式，但我們的 transaction 一定會有 transaction_item；transaction_item 也一定會有對應的 transaction。所以不管哪種 join 結果都一樣，失敗！

三、硬幹

Transaction.where(id: Project.find(1).transaction_records.success.pluck(:id)).sum(:money)

這個方法是可以解決的沒問題，因為從 Transaction ID 下 where-condition 總不可能又重複，但這個方式會有兩個問題：需要最少兩次 Query；如果涉及到的 Transaction 很多筆，會導致資料庫執行 SQL 指令的負擔，像是：

SELECT SUM("transactions"."money") FROM "transactions" WHERE "transactions"."deleted_at" IS NULL AND "transactions"."id" IN (3740, 3615, 3824, 3821, 7217, 64, 7219,
3770, 3484, 44, 3528, 3878, 3619, 3682, 3773, 114, 3625, 3602, 6749, 4265, 3650, 1455, 3565, 3493, 4644, 6966, 7266, 3627, 3648, 7256, 7262, 7275, 3688, 267, 3857, 7240, 3730, 72
64, 7259, 4412, 7336, 7321, 7332, 7329, 4729, 7337, 29, 68, 66, 47, 58, 6036, 76, 77, 70, 79, 73, 92, 112, 49, 74, 88, 95, 113, 101, 652, 107, 119, 89, 163, 168, 156, 160, 157, 1
66, 172, 153, 194, 183, 167, 185, 174, 190, 207, 243, 220, 224, 257, 237, 6044, 617, 274, 7187, 292, 278, 283, 164, 325, 318, 111, 313, 324, 782, 333,...

在許多嘗試後，我們最後找到一個相對好的做法：SubQuery

在 ActiveRecord 中很棒的只要語法正確，就會自動被轉為 SubQuery。語法跟上面第三個硬幹的方法類似，只要將輸出 id 這部分拿掉就可以了：

Transaction.where(id: Project.find(1).transaction_records.success~~.pluck(:id)~~).sum(:money)

輸出的 SQL 則為：

SELECT SUM("transactions"."money") FROM "transactions" WHERE "transactions"."deleted_at" IS NULL AND "transactions"."id" IN (SELECT "transactions"."id" FROM "transactions" INNER JOIN "transaction_items" ON "transactions"."id" = "transaction_items"."transaction_id" WHERE "transactions"."deleted_at" IS NULL AND "transaction_items"."deleted_at" IS NULL AND "transaction_items"."project_id" = $1 AND "transactions"."status" = $2)  [["project_id", 1], ["status", 1]]

可以看到 select in 中的 query 已經從 IDs 變為一個正常的 SQL，很棒對吧！

解決了資料正確性以及效能問題後，剩下的工作就是把相關的地方都改成正確的方式了...

後記

目前此狀況在提報後已被歸類成 ActiveRecord 的問題，可參考 #27478