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.

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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}