How to do a qwery witch filter ActiveRecord::Relation by linked relation number-CodePudding

I have to tables in my Rails app which are linked by a ActiveRecord::Relation like so.

# Table name: lease_rents
#  id                  :bigint not null, primary key
class LeaseRent < ApplicationRecord
  has_many :lease_tenant_payments,
  ...
end

class LeaseTenantPayment < ApplicationRecord
  belongs_to :lease_rent
  ...
end

each LeaseRent can have 0 or multiple LeaseTenantPayment.

in my Rails console i am trying to get only the LeaseRent witch have at least one or more LeaseTenantPayment.

to do so i have been able to get the number of LeaseTenantPayment linked to a specific LeaseRent

LeaseRent.all.where('id = 1').first.lease_tenant_payments.count

I want to get an ActiveRecord::Relation array whitch contain each LeaseRent witch have at least 1 or more LeaseTenantPayment. something like this:

#<ActiveRecord::Relation [#<LeaseRent id: ...>,<LeaseRent id: ...>,<LeaseRent id: ...>,...]>

Does anyone have some idea of how to get it?

CodePudding user response：

Use a join, it would only return rows with lease_tenant_payments.

LeaseRent.joins(:lease_tenant_payments)

If you want to use them in a loop, you may need to also use includes

LeaseRent.joins(:lease_tenant_payments).includes(:lease_tenant_payments)

CodePudding user response：

finaly got my solutions !

there are serval way to do it.

solution 1 :

LeaseRent.joins(:lease_tenant_payments).includes(:lease_tenant_payments)

solution 2 :

LeaseRent.joins(:lease_tenant_payments).distinct

solution 3 (my favorite cause it allow to revert it very easily juste by removing the '.not' ) :

LeaseRent.includes(:lease_tenant_payments).where.not(lease_tenant_payments: { id: nil })

i am using that way :

  scope "Aucun Paiement", :all, group: :unpayed do |rents|
    rents.includes(:lease_tenant_payments).where(lease_tenant_payments: { id: nil })
  end

  scope "Reliquat", :all, group: :unpayed do |rents|
    rents.includes(:lease_tenant_payments).where.not(lease_tenant_payments: { id: nil })
  end