Rails query comparing one col from table Payment and sum of table Transaction

There are Payment (id, total) and Transaction (payment_id, amount) table in our database (simplified for post). Payment’s id is in Transaction and they can be joined together if needed.

Here is what we come out with the SQL (simplified somewhat):

SELECT * FROM payments a WHERE ABS(a.total) <> (SELECT SUM(d.amount) FROM transactions d WHERE a.id = d.payment_id GROUP BY d.payment_id)

I need to assemble a Rails (4.2) query for SQL above, but is stuck with SELECT sum(d.amount) FROM transactions d WHERE a.id = d.payment_id GROUP BY d.payment_id.

What can I try next?

Answer

Rather than doing subqueries, I’d JOIN and GROUP the two tables:

Payment.joins(:transactions).
        group(:id).
        having("ABS(payments.total) <> SUM(transactions.amount)")

This relies on the following association between payments and transactions:

Payment < ActiveRecord::Base
  has_many :transactions
end