我有模特Account
和Transaction
。
和模型中Account
有两个外键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
此外,交易记录中还有列debit_amount
和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
我要确定范围,即按交易总和(借方金额+贷方金额)对帐户进行排序,使用范围我将为每个帐户输出total_amount。
现在,我有下一个范围:
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
但是它不能正常工作。它不算在内debit_amount
。
Account.accounts_balance.first.total_credit_amount
=> 1500
Account.accounts_balance.first.total_debit_amount
=> 0
但是,如果swap:credit_account_transactions
和:debit_account_transactions
injoins(:credit_account_transactions, :debit_account_transactions)
将计数total_debit_amount
,则total_credit_amount
-不会。
您要两次加入同一张桌子。它们将有所不同,并且必须以不同的方式引用。Rails尝试变得聪明,并重命名第二个联接。
只运行Account.joins(:credit_account_transactions, :debit_account_transactions)
,看看它产生什么SQL。它可能类似于:
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";
在这里,您可以看到credit_account_transactions_accounts
在这种情况下,第二个联接已重命名为其他名称(),因此您必须这样引用它。
.select(
'accounts.*',
'SUM(transactions.debit_amount_cents) AS total_debit_amount',
'SUM(credit_account_transactions_accounts.credit_amount_cents) AS total_credit_amount',
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句