Блог

У меня есть модели Category и Transaction .

Category has_many transactions , Transaction belongs_to category .

Transaction имеет столбцы debit_amount и credit_amount .

Я использую область, которая отображает категории и сортирует их по сумме transaction.debit_amount и transaction_credit_amount :

 Category.joins(:transactions)
      .where('transactions.created_at >= ?', 1.month.ago)
      .select(
        'categories.*',
        'SUM(transactions.credit_amount_cents   transactions.debit_amount_cents) AS total_amount'
      )
      .order('total_amount DESC')
      .group('categories.id')
      .limit(10)
 

И это работает.

Кроме того, у меня есть модель Account . То же самое, Account has many transactions , Transaction belongs_to Account.

Но 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
 

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
 

Я хочу использовать область также для учетных записей. Я немного переделал ее:

 Account.joins(:credit_account_transactions, :debit_account_transactions)
      .where('transactions.created_at >= ?', 1.month.ago)
      .select(
        'accounts.*',
        'SUM(transactions.credit_amount_cents   debit_account_transactions_accounts.debit_amount_cents) AS total_amount'      )
      .order('total_amount DESC')
      .group('accounts.id')
 

Но область видимости работает некорректно: (

По какой-то причине учетные записи с transaction.debit_amount == nil не включены в образец.

UPD. SQL:

 SELECT accounts.*, 
SUM(transactions.credit_amount_cents   debit_account_transactions_accounts.debit_amount_cents) AS total_amount 
FROM "accounts" INNER JOIN "transactions" ON 
"transactions"."credit_account_id" = "accounts"."id" INNER JOIN 
"transactions" "debit_account_transactions_accounts" ON 
"debit_account_transactions_accounts"."debit_account_id" = "accounts"."id" GROUP BY accounts.id