CODEWITHSUNDEEP
mysqlruby-on-rails
Alon Shmiel
Alon Shmiel

Reputation: 7121

Group by an attribute and count the records

I need to group the results of a query by their name and display the amount of results for each name.

controller:

def show
@task = Task.where(:name => params[:name])
   respond_to do |format|
      format.html # show.html.erb
      format.json { render json: @task }
   end
end

I need to define something that outputs something like this:

show.html.erb:

Name | # of Tasks

Alan |   2
Bob  |   1

How can I accomplish that? Thanks.

Upvotes: 1

Views: 105

Answers (2)

MrYoshiji
MrYoshiji

Reputation: 54882

You can use the group_by method:

@tasks.group_by(&:name)

# this will output:
{
  'Ben' => [<Task id:(...)>],
  'Alon' => [<Task id:(...)>, <Task id:(...)>, ...],
  #...
}

In your view, you could use it like this:

<% @tasks.group_by(&:name).each do |name, tasks| %>
  <tr>
    <td><%= name %></td>
    <td><%= tasks.size %></td> # the tasks variable is an Array of Tasks
  </tr>
<% end %>

Better way

You can also do it in only one SQL request:

Task.count(:group => 'name')
# should return a Hash like:
{
  'Ben': 2,
  'Alon': 5,
  # ...
}

How to use it in your view?

<% @tasks_count.each do |task_name, task_count| %>
  <tr>
    <td><%= task_name %></td>
    <td><%= task_count %></td>
  </tr>
<% end %>

Upvotes: 5

VadimAlekseev
VadimAlekseev

Reputation: 2248

MrYoushiji gave the correct answer. But you can fetch information you need by single sql request:

@task_counts = Task.group(:name).count

# this will output:
{
  'Ben' => 1,
  'Alon' => 2,
  #...
}

Usage:

<% @task_counts.each do |name, count| %>
  <tr>
    <td><%= name %></td>
    <td><%= count %></td>
  </tr>
<% end %>

Upvotes: 0

Related Questions