CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Phil Bottomley
Phil Bottomley

Reputation: 2485

Active Record: query based on another query in an association

I'm trying to do a complex search based on an query in an association.

Event belongs_to :user / User has_many :events

Along the literal lines of:

query = User.where(:name => 'Bob')
query = query.joins(:events).where('COUNT(events.start_at > #{Time.now}) = 0')

I have tried several approaches but none seem to work. Help greatly appreciated.

Thanks in advance.

Upvotes: 2

Views: 124

Answers (4)

Wizard of Ogz
Wizard of Ogz

Reputation: 12643

You cannot use COUNT in a WHERE clause afiak. You'll want to group your results and use conditions to return only the results you want.

query = User.where(:name => 'Bob')
query = query.joins("LEFT JOIN (SELECT events.id FROM events WHERE events.start_at > '#{Time.now.to_s(:db)}') AS events ON events.user_id=users.id").group("users.id").where("events.id IS NULL")

Upvotes: 0

zarazan
zarazan

Reputation: 1174

I think this is what you are looking for all in one line:

Event.joins(:user).where(:users => {:name => 'Bob'}, :events => {:start_at => Time.at(0)..Time.now })

Upvotes: 0

jakeonrails
jakeonrails

Reputation: 1895

I noticed a few issues with the way your query is written but I don't think fixing them will solve your problem. All the same, hopefully knowing this stuff will get you closer!

First, you need double quotes in order to get Ruby to respect your #{} interpolation:

Open IRB and try:

1.9.3p194 :001 > 'COUNT(events.start_at > #{Time.now}) = 0'

Which will output:

"COUNT(events.start_at > \#{Time.now}) = 0"

Notice that nothing changed, because we have single quotes.

Now lets try with double quotes, and we're a bit closer:

1.9.3p194 :002 > "COUNT(events.start_at > #{Time.now}) = 0"

This time we see:

"COUNT(events.start_at > 2012-09-24 14:47:52 -0700) = 0"

But that still won't work, because SQL wants quotes around your timestamp for comparison.

We could handle this manually, like this:

1.9.3p194 :003 > "COUNT(events.start_at > '#{Time.now}') = 0"

Which generates:

"COUNT(events.start_at > '2012-09-24 14:49:31 -0700') = 0"

But that may not work on all SQLs - I know that Postgres wants single quotes around literals, but MySQL may prefer double quotes, or may not matter.

The best way to handle this is to let Rails figure it out. The way we do that is to use the ? operator inside of our SQL query strings:

where('COUNT(events.start_at > ?) = 0', Time.now)

And the output Rails will generate, and send to your SQL database, will look like this:

"WHERE COUNT(events.start_at > '2012-09-24 14:49:31 -0700') = 0"

Hope that helps!

Upvotes: 0

cdesrosiers
cdesrosiers

Reputation: 8892

You need to use scopes to allow this kind of chaining:

In User model:

scope :with_name, lambda { |name| where(:name => name) }

In code:

query = User.with_name('Bob')
query = query.joins(:events).where('COUNT(events.start_at > #{Time.now}) = 0')

Upvotes: 0

Related Questions