CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
robotdana
robotdana

Reputation: 532

Rails, Count and Group

I have a table like: 

 +--------+-----------+-------+-----------+
 |house_no|house_alpha|flat_no|street_name|
 +--------+-----------+-------+-----------+
 |       1|           |       |James St   |
 |       1|           |       |James St   |
 |       1|           |       |James St   |
 |       2|          A|       |James St   |
 |       2|          B|       |James St   |
 |       3|          A|       |James St   |
 |       4|           |    416|James St   |
 |       4|           |    416|James St   |
 +--------+-----------+-------+-----------+

And I'm trying to count the number of different addresses in this table. This returns the distinct addresses:

Address.all(:select => 'street_name, flat_no, house_no, house_alpha',
            :group => 'street_name, flat_no, house_no, house_alpha').length

But I want to do it on the SQL end. and trying to combine count and group doesn't like me. I'm clearly doing something wrong.

(Database is postgres, rails is 2.x).

Upvotes: 3

Views: 8163

Answers (3)

Jellicle
Jellicle

Reputation: 30206

In Rails 2, I have found the :group option to not behave reliably (in my latest case, it made an array of only the first record in each group). As an alternative, you might combine your db search with EnumerableArray#group_by as follows:

addresses = Address.find(:all, 
             :select => "count(*) as addr_count", 
             :having => "addr_count > 0"), 
grouped_addrs = addresses.group_by{|a| [a.street_name, a.flat_no, a.house_no, a.house_alpha] }

Upvotes: 1

rwc9u
rwc9u

Reputation: 960

For rails 3 with Arel, you could do the following:

Address.group(:street_name, :flat_no, :house_no, :house_alpha).having("count(*) > 0").count.size

Upvotes: 7

jdl
jdl

Reputation: 17790

I'm not sure that there's a pretty Rails way to do a count across grouped columns. There are plenty of weird ways to do this in SQL, but this way is easy enough to follow.

Address.find(:all, 
             :select => "count(*) as addr_count", 
             :having => "addr_count > 0", 
             :group => 'street_name, flat_no, house_no, house_alpha').size

That will run the following SQL query.

SELECT count(*) as addr_count FROM "addresses" GROUP BY street_name, flat_no, house_no, house_alpha HAVING addr_count > 0

Edit: Read this for Postgres

From the comments below, here is the way to do the above on Postgres.

Address.find(:all, 
             :select => "count(*)", 
             :having => "count(*) > 0", 
             :group => 'street_name, flat_no, house_no, house_alpha').size

This generates the following query.

SELECT count(*) FROM "addresses" GROUP BY street_name, flat_no, house_no, house_alpha HAVING count(*) > 0

Upvotes: 2

Related Questions