1

I have an ActiveRecord model named Transaction. Each transaction has four fields:

  • TransactionID
  • Type of Transaction
  • UserID
  • Parent TransactionID

There are further two types of Transaction:

  • Debit Transaction
  • Payback Transaction

For every Payback Transaction, the TransactionID of the Debit Transaction is mentioned in Parent TransactionID field.

Using ActiveRecord Query I have to find the unpaid Debit Transactions of a particular user ID.

Example data:

TransactionID Type of Transaction UserID Parent TransactionID
123           Debit               1      null
124           Debit               1      null
125           Credit              1      123
127           Debit               1      null

Querying the above data should return Transactions: 124, 127.

Drenmi
  • 8,492
  • 4
  • 42
  • 51
shril
  • 143
  • 2
  • 13
  • Is it correct that `DebitTransaction` has many `PaybackTransaction`s? – Drenmi Aug 27 '18 at 06:47
  • There is only one `PaybackTransaction` for one `DebitTransaction`. – shril Aug 27 '18 at 07:28
  • Try this `Transaction.where(parent_transaction_id: nil, type: 'Debit').where.not(id: Transaction.select(:parent_transaction_id).uniq)` – Hardik Upadhyay Aug 27 '18 at 08:18
  • @HardikUpadhyay your solution works but is it feasible to do without subquery and using self-join table statement? – shril Aug 27 '18 at 11:07
  • @shril Yes, you can. you have to write custom association. please take a look https://stackoverflow.com/questions/17476521/rails-has-many-custom-activerecord-association – Hardik Upadhyay Aug 27 '18 at 11:15
  • https://mrbrdo.wordpress.com/2013/09/25/manually-preloading-associations-in-rails-using-custom-scopessql/ – Hardik Upadhyay Aug 27 '18 at 11:17

1 Answers1

0
class Transaction < ApplicationRecord
  has_one :payback_transaction, class_name: 'Transaction', foreign_key: :parent_transaction_id
  belongs_to :debit_transaction, class_name: 'Transaction', foreign_key: :parent_transaction_id, optional: true

https://guides.rubyonrails.org/association_basics.html#self-joins

You may want has_many instead of has_one if you want multiple paybacks per debit.

kalzen
  • 343
  • 4
  • 12
  • Can anyone help me with the **ActiveRecord Query** for the above question? The records have single `PaybackTransaction` for every `DebitTransaction`. – shril Aug 27 '18 at 07:34
  • @shril what is wrong with the above? That is an `ActiveRecord` association as requested. `Transaction.find(123).payback_transaction` will result in `Transaction` 125 which appears to be your desired result. Although the belongs to needs a foreign key as well – engineersmnky Aug 27 '18 at 16:46