Reputation: 15269
What ActiveRecord do I write to make this happen:
SELECT COUNT(*), SUM(`users`.`sign_in_count`), MAX(`users`.`sign_in_count`) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
I suspect the syntax is vaguely like:
User.where(some_field: some_condition).count.and.sum(:sign_in_count).and.maximum(:sign_in_count)
Currently have this:
:001:0> User.where(some_field: some_condition).count
(1ms) SELECT COUNT(*) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> 10
:002:0> User.where(some_field: some_condition).sum(:sign_in_count)
(1ms) SELECT SUM(`users`.`sign_in_count`) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> 100
:003:0> User.where(some_field: some_condition).maximum(:sign_in_count)
(1ms) SELECT MAX(`users`.`sign_in_count`) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> 50
Is ActiveRecord able to make multiple selects in one query? My real world use case is a bit heavier and has multiple conditions so it is worth grabbing all results after filtering. I suppose I could do:
:004:0> all_things = User.select(:sign_in_count).where(some_field: some_condition)
but that would still do a call each time you called upon it:
:005:0> all_things.count
(1ms) SELECT COUNT(*) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> 10
:006:0> all_things.sum(:sign_in_count)
(1ms) SELECT SUM(`users`.`sign_in_count`) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> 100
:007:0> all_things.maximum(:sign_in_count)
(1ms) SELECT MAX(`users`.`sign_in_count`) FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> 50
so I could do .to_a
and be forced to use less elegant methods
:008:0> all_things = User.select(:sign_in_count).where(some_field: some_condition).to_a
User Load (0.9ms) SELECT `users`.`sign_in_count` FROM `users` WHERE (`users`.`some_field` = 'some_condition')
=> [#<User id: nil, sign_in_count: 0>, ... many elements
:009:0> all_things.count
=> 10
:010:0> all_things.inject(0){|r,e| r+e.sign_in_count}
=> 100
:011:0> all_things.inject(0){|r,e| [r,e.sign_in_count].max}
=> 50
There has to be a pretty way... which doesn't force me to cram millions of rows into memory instead of a few results, or do this:
:012:0> count, sum, max = ActiveRecord::Base.connection.execute("SELECT COUNT(*), SUM(`users`.`sign_in_count`), MAX(`users`.`sign_in_count`) FROM `users` WHERE (`users`.`some_field` = 'some_condition')").to_a.first
Upvotes: 0
Views: 997
Reputation: 2741
Use select
:
stats = User.select("count(*) as num_users", "sum(sign_in_count) as total_sign_ins", "max(sign_in_count) as max_sign_ins")
.where(age: 18).first
Then you can access these like so:
stats.num_users
stats.total_sign_ins
stats.max_sign_ins
Upvotes: 2