CODEWITHSUNDEEP
ruby-on-railspostgresqlrails-activerecord
Ira
Ira

Reputation: 43

consolidating multiple column counts into a single query with Rails 4 Activerecord

Rails 4.1, Postgres 9.3, deploying to Heroku

I'm trying to reduce the number of calls to the DB.

I have a big table, surveys, with multiple boolean columns like role_composer, role_performer, and so forth.

The controller has multiple queries like 

@sample = Survey.where(...whatever...)
@Composers = @sample.count("case when role_composer then true end")
...
@Performers = @sample.count("case when role_performer then true end")

This works fine, but results in many individual queries to the database that differ only by the expression in the select. Is there a way to construct this as one query with multiple aggregated/computed columns? I also have queries with average() and with expressions, but most common is count().

In postgres this works:

SELECT count(case when role_composer then true end) as "COMPOSERS", count(case when role_performer then true end) as "PERFORMERS" from surveys;

Any way to do this with Activerecord methods on @sample instead of resorting to find_by_sql()?

I've tried a variety of approaches without success: .count().count(), .count([array]), .select("count(...) as col1, count(...) as col2"), .select(["count(...) as col1", "count(...) as col2"])

Thanks in advance for any answers.

Upvotes: 4

Views: 3085

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Your .select("count(...) as col1, count(...) as col2") version should work fine if you remember two things:

  1. M.where(...).select(...) returns multiple things even if the query only returns one row.
  2. Just because something doesn't appear in the inspect output doesn't mean it isn't there.

You're doing aggregates without a GROUP BY so you'll only be getting one row back. To unwrap that row, you can say first:

counts = Survey.where(...)
               .select('count(case when role_composer then true end) as composers,  count(case when role_performer then true end) as performers')
               .first

That will give you a Survey instance in counts. If you look at that counts in the console, you'll see something like this:

#<Survey >

The inspect output only includes values from columns (i.e. things that the Survey class knows about) but the composers and performers will be there. However, since ActiveRecord doesn't know what types they're supposed to be, they'll out as strings:

composers  = counts.composers.to_i
performers = counts.performers.to_i

Everything in your select will be there if go looking for it.

Upvotes: 4

Related Questions