CODEWITHSUNDEEP
ruby-on-railsruby
random_user_0891
random_user_0891

Reputation: 2061

Rails: Get count of values from database

I have a column in my database called "experience" the user can save values called 0-2 years, 2-5 years, 5-10 years, 10+ years. My question is how do I get a count of those values? I'm not looking for a sum total of all, I'm trying to get a count of how many "2-5 years" values exist in the column and present that to the user in the view.

So in the view the user will see the values from the database tables column called experience and then how many jobs are posted and contain that value like this.

Experience:

0-2 years (3)

2-5 years (15)

Currently I am doing this in the view

<%= link_to "0-2 years", filtered_jobs_path(experience: '0-2 years', num_days_past: params[:num_days_past], search: params[:search]) %>
   (<%= Job.where(experience: '0-2 years').size %>)<br />

I was reading it's better to use size than count because size won't make another call to the database if it's already been loaded. I'm not sure if I'm doing this in the most efficient way though, possibly I should be using a counter cache column?

Here are the calls that get made how I currently have it setup, seems like the database is constantly being queried even though I'm using .size

   (0.1ms)  SELECT COUNT(*) FROM "jobs" WHERE "jobs"."experience" = ?  [["experience", "0-2 years"]]

   (0.1ms)  SELECT COUNT(*) FROM "jobs" WHERE "jobs"."experience" = ?  [["experience", "2-5 years"]]

   (0.1ms)  SELECT COUNT(*) FROM "jobs" WHERE "jobs"."experience" = ?  [["experience", "5-10 years"]]

   (0.1ms)  SELECT COUNT(*) FROM "jobs" WHERE "jobs"."experience" = ?  [["experience", "10+ years"]]

  Job Load (0.2ms)  SELECT  "jobs".* FROM "jobs" LIMIT ? OFFSET ?  [["LIMIT", 5], ["OFFSET", 0]]

   (0.1ms)  SELECT COUNT(*) FROM "jobs"

  CACHE (0.0ms)  SELECT COUNT(*) FROM "jobs"

Upvotes: 0

Views: 1681

Answers (2)

Danil Speransky
Danil Speransky

Reputation: 30473

You want to group by experience and then count it:

Job.group(:experience).count

That would return a hash:

{
  '0-2 years' => 1,
  '10+ years' => 2,
  '2-5 years' => 1,
  '5-10 years' => 2
}

So in your view you could write something like this:

# in controller

@exp_names = ['0-2 years', '2-5 years', '5-10 years', '10+ years']
@exp_counts = Job.group(:experience).count

# in view

<% @exp_names.each do |name| %>
  <%= link_to name, filtered_jobs_path(experience: name, num_days_past: params[:num_days_past], search: params[:search]) %>
  (<%= @exp_counts[name] %>)
  <br>
<% end %>

Upvotes: 0

Ursus
Ursus

Reputation: 30071

Job.group(:experience).count

should give you an hash with as keys the experience field and as values the number of rows for that field

<% Job.group(:experience).count.each do |name, count| %>
  <%= link_to name, filtered_jobs_path(experience: name, num_days_past: params[:num_days_past], search: params[:search]) %>
  (<%= count %>)
<% end %>

Upvotes: 3

Related Questions