Many-to-many relationship on the same model via an intermediary table

Question

I'm working with transactions that can be either purchases or refunds and I need to connect them to each other. One purchase can have several refunds (partial refunds), and one refund can be connected to several purchases.

I tried to establish relationships as described here

Here is my Transaction model:

class Transaction < ApplicationRecord
  has_many :refunds_purchases, foreign_key: :transaction_id, class_name: 'RefundsPurchase'
  has_many :refunds, through: :purchases_refunds, source: :refund

  has_many :purchases_refunds, foreign_key: :transaction_id, class_name: 'RefundsPurchase'
  has_many :purchases, through: :refunds_purchases, source: :purchase
end

And here is the association model:

class RefundsPurchase < ApplicationRecord
  belongs_to :purchase, foreign_key: :purchase_id, class_name: 'Transaction'
  belongs_to :refund, foreign_key: :refund_id, class_name: 'Transaction'
end

When I call transaction.refunds it fails with NameError: uninitialized constant Transaction::RefundsPurchase" so it's trying to prefix the namespace with the current class prefix. If I move the associative model to the Transaction::RefundsPurchase namespace, it gives NoMethodError: undefined method refunds_purchases' for #Transaction:0x000055633d746440`

What am I doing wrong?

Looks weird that you define two relations with same options, but different names `has_many :refunds_purchases` and `has_many :purchases_refunds`, Maybe one will be enough? :) — mcfoton, Oct 07 '21 at 07:16
@mcfoton One is not enough, because they reference the same foreign key - transaction_id. With one it finds only purchases, but not refunds or the other way around, depending on what the source is. — Lidia Mokevnina, Oct 07 '21 at 09:10

max · Accepted Answer · 2021-10-07T08:01:13.517

The real problem here is a actually a deficiency in how you have modeled the rest of the domain. You're missing a model that wraps a group of purchases:

class Purchase < ApplicationRecord
  belongs_to :purchase_order
  has_many :transactions, through: :purchase_orders
end

class PurchaseOrder < ApplicationRecord
  has_many :purchases
  has_many :transactions
end 

class Transaction < ApplicationRecord
  belongs_to :purchase_order
  has_many :purchases, through: :purchase_order
end

A more typical naming scheme for this would be line items and orders. If you want to go down the route of using Single Table Inheriance instead of two different tables you can setup separate assocations for refunds and payments through:

class Purchase < ApplicationRecord
  belongs_to :purchase_order
  has_many :transactions, through: :purchase_orders
end

class PurchaseOrder < ApplicationRecord
  has_many :purchases
  has_many :transactions
  has_many :payments
  has_many :refunds
end 

# make sure to add varchar column named 'type' to the table 
class Transaction < ApplicationRecord
  belongs_to :purchase_order
  has_many :purchases, through: :purchase_order
end

class Payment < Transaction
end

class Refund < Transaction
end

I would really consider using two different tables for refunds and payments and use a union (or a view) if you need to treat it as a homogenious collection. It will make the the database less tied to the application and you'll hopefully have far more payments then refunds and need to query that table more.

If you want to make so that a refund can be tied to a single purchase (a specific line item on an order) you can either add a separate nullable foreign key column:

class Transaction < ApplicationRecord
  belongs_to :purchase_order, optional: true
  belongs_to :purchase, optional: true
  has_many :purchases, through: :purchase_order

  def item
    purchase_order || purchase
  end
end

Or use a polymorphic assocation:

class Transaction < ApplicationRecord
  belongs_to :item, polymorphic: true
  has_many :purchases, through: :purchase_order
end

This has the hauge caveat that it precludes the use of foreign key constrainsts to guarantee referential integrity which seems like a really bad idea when money is involved.

The errors I was getting have disappeared after the re-build of the Docker container, but thank you for a great idea. STI wouldn't work well here because of the specific usage of transactional data in the app, but wrapping transactions in a group of purchases or group of refunds would play out nicely. — Lidia Mokevnina, Oct 07 '21 at 09:17

Many-to-many relationship on the same model via an intermediary table

1 Answers1