CODEWITHSUNDEEP
ruby-on-railsactiverecord
Myxtic
Myxtic

Reputation: 5699

How can I count the result of an ActiveRecord .group query? Chaining .count returns a hash

I can use the following :

User.where("zip_code = '48104'").group('users.id').count

To get hashes like :

{195=>1, 106=>1, 120=>1, 227=>1, 247=>1, 264=>1, 410=>1}

Is there a way I can count these hashes, and just return 7, instead of the above result ?

I know this doesn't make sense in the above scenario, and I can just do it by not using the group clause. But I'm working on a more complex query, where I need to implement this. Thanks in advance.

Upvotes: 19

Views: 13197

Answers (6)

raman
raman

Reputation: 663

As said before by orourkedd the accepted answer is not great when dealing with a large amount of records. I too recently encountered this issue and have large tables that I definitely don't want to load into memory. So here is what works for me.

users=User.where("zip_code = '48104'").group('users.id')
user_count=User.where(id: users.select(:id)).count

Rails will handle the users.select part like a subquery. This works with Rails 4

Upvotes: 5

laffuste
laffuste

Reputation: 17095

Another way, useful if the values can be > 1.

User.where("zip_code = '48104'").group('users.id').count.values.sum

Upvotes: 0

orourkedd
orourkedd

Reputation: 6421

The accepted answer is not scalable. Using @robbrit's method on a query with 25,000 matching users would pull 25,000 results (ie an array with 25,000 elements) into memory and then count the elements array. Here is a method that will do it without pulling all that data:

def count_query(query)
  query = "SELECT count(*) AS count_all FROM (#{query.to_sql}) x"
  ActiveRecord::Base.connection.execute(query).first.try(:[], 0).to_i
end

It is used like this:

query = User.where("zip_code = '48104'").group('users.id')
count = count_query(query)

This works for me using mysql, btw.

It was inspired by this guy: https://github.com/mrbrdo/active_record_group_count/blob/master/lib/active_record_group_count/scope.rb

Upvotes: 14

JD.
JD.

Reputation: 3035

Since the first count() is returning a hash, use whatever Ruby enumerable operations you wish on it. Example:

User.where("zip_code = '48104'").group('users.id').count.count

See also:

Upvotes: 3

sachinr
sachinr

Reputation: 189

If you want to add the values of the hashes together then:

{key1: 1, key2: 2}.values.inject(:+) # => 3

If you just want the number of keys then:

{key1: 1, key2: 2}.length # => 2

Upvotes: 4

robbrit
robbrit

Reputation: 17960

Try:

User.where("zip_code = '48104'").group('users.id').count.length

Upvotes: 24

Related Questions