ruby-on-rails - How to make scope for model, that has two foreign keys?

Question

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.

标签： ruby-on-railsactiverecord

Answer 1

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',