ruby-on-railspostgresqlself-join

Query with self-joins

I have purchases, which may (or may not) have an associated sub-purchase, which i've modelled with a self-join as:

class Purchase < ApplicationRecord
  has_one :sub_purchase, class_name: 'Purchase', dependent: :destroy
  belongs_to :main_purchase, class_name: 'Purchase', foreign_key: 'purchase_id', optional: true
end

Purchases have an attribute, status, which may be 'active'. How do I query for sub-purchases, with a main purchase that is active?

Something close to this perhaps?:

Purchase.where.not(purchase_id:nil).where(main_purchase: {status: 'active'})

UPDATE: In SQL, I have discovered I can alias the purchases table twice and do something like this:

SELECT
FROM
purchases p1 INNER JOIN purchases p2 ON p2.id = p1.purchase_id
WHERE p2.status='active';

but it would be nice to not use SQL directly if it's possible to avoid.

Solution

  • Without actually trying it:

    Purchase.joins(:main_purchase).where(main_purchase: {status: 'active'})