85

I know I've seen this before but I can't find anything now. I want to group a query by a certain column and be able to display how many are in each group. I got the first part down: 

@line_items = @project.line_items.all(:group => "device_id")

This is for my line item index view, which is just a table displaying the line items. How do I make a column in that table for "count" now that the line items are grouped by device?

ekad
  • 14,436
  • 26
  • 44
  • 46
tladuke
  • 1,337
  • 2
  • 11
  • 22

7 Answers7

160

You can do count on line_items which will return you an ordered hash of device_id and count.

@project.line_items.group(:device_id).count
Dorian
  • 22,759
  • 8
  • 120
  • 116
Chandra Patni
  • 17,347
  • 10
  • 55
  • 65
  • 1
    This gives a warning: `DEPRECATION WARNING: Relation#calculate with finder options is deprecated. Please build a scope and then call calculate on it instead. (called from C:in 'count':)` – Chloe Dec 23 '13 at 22:21
  • 7
    @Chloe - Try this in Rails 4 to remove the warning: `@project.line_items.group(:device_id).count` – Matt Huggins Jan 07 '14 at 21:01
22

hash of devise_id as key and associated records count

@project.line_items.group(:device_id).count
JP Silvashy
  • 46,977
  • 48
  • 149
  • 227
Sandip Ransing
  • 7,583
  • 4
  • 37
  • 48
12

I think you can try this as well.

@project.line_items.group(:device_id).pluck("device_id, count(device_id)")

^^ This gives array of arrays with elements 'device_id and count'

bluefoggy
  • 961
  • 1
  • 9
  • 23
10

Just add a :select option:

@line_items = @project.line_items.all(
  :group  => "device_id",
  :select => "device_id, COUNT(*) as count"
)

Then each @line_item will have a count attribute.

Alex Reisner
  • 29,124
  • 6
  • 56
  • 53
  • hrm, I put debug(@line_items) in my view, but I don't see any counts, so I don't think I'm understanding you. I think the output is exactly the same as without the select option. – tladuke Jan 07 '10 at 18:04
  • The debug method won't show the count because it's not an ActiveRecord attribute, but there *is* a count attribute on each object. In any case, Chandra's solution is much better. – Alex Reisner Jan 27 '10 at 00:52
4

something like

 User.all(:joins => :comments, :select => "users.*, count(comments.id) as comments_count", :group => "users.id")

might also work...

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
1

After this commit:

https://github.com/rails/rails/commit/a1c05dd8b9bd3623289d3aa73dda2943d620cc34

there's a new way to do the same thing:

@project.line_items.count(:group => LineItem.arel_table[:device_id])
Psylone
  • 2,788
  • 1
  • 18
  • 15
1

For only count pluck would be faster here rather than group

@project.line_items.pluck(:device_id).count


@project.line_items.pluck(:device_id).uniq.count
John Smith
  • 61
  • 3
  • 1
    They will return completely different results for enumerated attributes. For example: `customer_subscriptions.group(:status).count` will return: `{1 => 6, 2 => 11, 3 => 136, 0 => 30}` and `customer_subscriptions.pluck(:status).count` will just return `183` – msdundar Oct 16 '17 at 12:15
  • true, how about uniq then? @project.line_items.pluck(:device_id).uniq.count – John Smith Nov 10 '17 at 09:44