Combining multiple columns into one query with Rails 4 Activerecord
Rails 4.1, Postgres 9.3, deploying to Heroku
I am trying to reduce the number of calls to the DB.
I have a large table, views, with multiple boolean columns like role_composer
, role_performer
etc.
The controller has multiple requests, for example
@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 great, but results in a lot of individual database queries that differ only in the select statement. Is there a way to plot this as a single query with multiple aggregated / calculated columns? I also have middle () and expression queries, but the 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 the Activerecord methods on @sample instead of resorting to find_by_sql ()?
I have tried different 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.
source to share
Your version .select("count(...) as col1, count(...) as col2")
should work fine as long as you remember two things:
-
M.where(...).select(...)
returns multiple objects even if the query only returns one row. - Just because something isn't showing up in the output
inspect
doesn't mean it isn't there.
You are doing aggregates without GROUP BY, so you only get one row. To expand this line, 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
This will give you a copy of Survey
the counts
. If you look at this counts
in the console, you will see something like this:
#<Survey >
The output inspect
only includes the values from the columns (i.e. things that the class knows about Survey
), but there will be composers
and performers
. However, since ActiveRecord doesn't know what types they should be, they come out as strings:
composers = counts.composers.to_i
performers = counts.performers.to_i
Everything in yours select
will be there if you are looking for it.
source to share