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?
Advertisement
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