CODEWITHSUNDEEP
ruby-on-railsruby
San
San

Reputation: 165

How to query with has many associations in rails

Could someone help me with my query? I have the following associations:

class Customer < ApplicationRecord
  has_many :customer_subscriptions, dependent: :destroy
end

class CustomerSubscription < ApplicationRecord
  belongs_to :customer
end

I am trying to get the customer whose all customer subscription status is active. Here, customer subscription has status active,canceled and deleted. I am trying to use the where query insted of select query.

Upvotes: 0

Views: 66

Answers (3)

David Aldridge
David Aldridge

Reputation: 52396

Here's a technique which uses a single query that returns customers for which at least one active subscription exists, and no non-active subscriptions exist.

Customer.
  where(
    CustomerSubscription.
      where(status: 'active').
      where(
        CustomerSubscription.
          arel_table[:customer_id].
          eq(Customer.arel_table[:id])
      ).arel.exists).
  where.not(
    CustomerSubscription.
      where.not(status: 'active').
      where(
        CustomerSubscription.
          arel_table[:customer_id].
          eq(Customer.arel_table[:id])
      ).arel.exists)

I might have lost track of the parentheses there, mind. It can definitely be tidied up with scopes, so it looks more:

Customer.has_an_active_subscription.has_no_nonactive_subscriptions

Upvotes: 2

Shaher Shamroukh
Shaher Shamroukh

Reputation: 367

Get all the customer subscriptions that have active status then map on each one to get the customer

CustomerSubscription.where(status: 'active').map { |cs| cs.customer}

Upvotes: 0

spickermann
spickermann

Reputation: 107142

I would try:

customer_ids_with_non_active_subscriptions = 
  CustomerSubscription.select(:customer_id).where.not(status: 'active')
Customer
  .joins(:customer_subscriptions)
  .where.not(customers: { id: customer_ids_with_non_active_subscriptions })

Explanation:

  • First, determine the list of customers' ids that have at least one non-active subscription.
  • Then load all customers that have at least one subscription (the joins part) but exclude those customers who are on the first list.

Upvotes: 0

Related Questions