I have association as following

A user has many subscriptions (only one subscription can have its status as active) A subscription belongs to a user

I'm looking for an active record query that would help me select users whose all subscriptions are cancelled or in other words who doesn't have any active subscription.

Raw Sql queries are also welcome for this question.

CodePudding user response：

You can do this with this query

User.where.not(id: Subscription.select(:user_id).where(status: 'Active'))

CodePudding user response：

Please try below query

User.joins(:subscriptions).where('subscriptions.status = "active"').group('subscriptions.id').having("count(subscriptions.id) < 1")

CodePudding user response：

We can do it in two ways

1st one:

User.where.not(id: Subscription.where(status: 'active').pluck(:user_id))

2nd one:

active_subscription_user_ids = Subscription.where(status: 'active').pluck(:user_id)

User.left_outer_joins(:subscriptions).where.not(id: active_subscription_user_ids)