Reputation: 191
I have models Account
and Transaction
.
And model Account
has two foreign keys in Transaction
.
transaction.rb:
belongs_to :credit_account, class_name: 'Account',
foreign_key: 'credit_account_id', optional: true
belongs_to :debit_account, class_name: 'Account',
foreign_key: 'debit_account_id', optional: true
Also, transaction have columns debit_amount
and credit_amount
.
account.rb:
has_many :debit_account_transactions,
class_name: 'Transaction',
foreign_key: 'debit_account_id',
dependent: :nullify
has_many :credit_account_transactions,
class_name: 'Transaction',
foreign_key: 'credit_account_id',
dependent: :nullify
I want to make scope, that will be sort accounts by sum of transactions (debit_amount + credit_amount) and with using scope i will output total_amount for each account.
Now, i have next scope:
joins(:credit_account_transactions, :debit_account_transactions)
.where('transactions.created_at >= ?', 1.month.ago)
.select(
'accounts.*',
'SUM(transactions.debit_amount_cents) AS total_debit_amount',
'SUM(transactions.credit_amount_cents) AS total_credit_amount',
'SUM(transactions.credit_amount_cents + transactions.debit_amount_cents) AS total_amount'
)
.order('total_amount DESC')
.group('accounts.id')
end
But it doesn't work correctly.
It doesn't count debit_amount
.
Account.accounts_balance.first.total_credit_amount
=> 1500
Account.accounts_balance.first.total_debit_amount
=> 0
But if swap :credit_account_transactions
and :debit_account_transactions
in joins(:credit_account_transactions, :debit_account_transactions)
it will count total_debit_amount
, and total_credit_amount
- will not.
Upvotes: 1
Views: 131
Reputation: 4368
You're joining the same table twice. They will be different and have to referenced differently. Rails tries to be smart and renames the 2nd join.
Run just Account.joins(:credit_account_transactions, :debit_account_transactions)
and see what sql it produces. It will probably be something like:
SELECT "accounts".* FROM "accounts"
INNER JOIN "transactions"
ON "transactions"."credit_account_id" = "accounts"."id"
INNER JOIN "transactions" "credit_account_transactions_accounts"
ON "credit_account_transactions_accounts"."debit_account_id" = "accounts"."id";
Here you can see that the 2nd join is renamed to something different (credit_account_transactions_accounts
) in this case and you have to reference it as such.
.select(
'accounts.*',
'SUM(transactions.debit_amount_cents) AS total_debit_amount',
'SUM(credit_account_transactions_accounts.credit_amount_cents) AS total_credit_amount',
Upvotes: 1