CODEWITHSUNDEEP
ruby-on-railsactiverecord
Snowman
Snowman

Reputation: 32061

Rails query where has_many relationship matching query doesn't exist

I have a Subscription model which has_many Transactions.

I want to find all subscriptions for which a transaction matching a certain query doesn't exist.

This is the manual looping code that yields the correct result. However, I'd like to do the same using a where query instead.

# Find all subs that don't have a transaction where foo == bar
subs = []
Subscription.all.each do |sub|
  if sub.transactions.find { |tran| tran.foo == 'bar' } == nil
    subs.push(sub)
  end
end

I've tried so many different options but none of them produce the correct result:

Subscription.includes(:transactions).where("NOT EXISTS (SELECT null FROM subscriptions.transactions where transactions.foo = 'bar')")

Subscription.includes(:transactions).where("(select count(*) from transactions where foo='bar') = 0")

Subscription.where(Transaction.where("transactions.foo != 'bar'").limit(1).count == 0)

Subscription.joins(:transactions).group('subscriptions.id').having('count(transactions.foo = 'bar') = 0')

Subscription.where(Transaction.group().where("count(transactions.foo != 'bar') = 0"))

Subscription.where('(SELECT COUNT(*) FROM transactions WHERE transactions.foo = 'bar') =  0')

Transaction.left_outer_joins(:transactions).group("transactions.id").having("count(transactions.id)>0")

Subscription.left_outer_joins(:transactions).where.not(transactions: {foo: 'bar'})

Upvotes: 0

Views: 184

Answers (1)

The Wizard
The Wizard

Reputation: 953

You could first select all the transactions where foo is equal to bar. Then you could select all subscription ids which are not in the above result, which would give an Active Record query like this:

Subscription.where.not(id: Transaction.where('foo = ?', 'bar').select(:subscription_id).uniq)

This will generate a SQL query like this:

SELECT * FROM subscriptions WHERE (subscriptions.id NOT IN (SELECT DISTINCT transactions.subscription_id WHERE transactions.foo = 'bar'))

Upvotes: 1

Related Questions