I have a Postgres table where user can have many payment subscriptions and the payment subscriptions are either active or inactive, which is shown through an activated_at and an inactivated_at date field on the payment subscriptions table. I now want to find all the users without active payment subscriptions.
Finding all the users without any payment subscription is easy enough
User.joins("LEFT JOIN payment_subscriptions ON (payment_subscriptions.user_id = users.id)").where("payment_subscriptions.id IS NULL")
I also want to find all the users who only have inactive subscriptions. If I use the same pattern as above I will find all the users who at some point have inactivated their subscription, but there is no guarantee that they do not also have an active subscription.
User.joins("LEFT JOIN payment_subscriptions ON (payment_subscriptions.user_id = users.id)").where("payment_subscriptions.inactivated_at IS NOT NULL")
How do I get the users who have either no subscriptions or no active subscriptions from my table?
this one gives you users without any active subscriptions.
User.joins("LEFT JOIN payment_subscriptions on (payment_subscriptions.user_id = users.id and payment_subscriptions.inactivated_at is null)").where("payment_subscriptions.id is null")
If you want to get only those who have inactive, use also your previous join
I don't know if it possible in rails, but you also can use not exists syntax:
User.where('
not exists (
select *
from payment_subscriptions as ps
where ps.user_id = users.id and ps.inactivated_at is not null
)
')
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments