Rails: Get count of values from database

Question

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"

Ursus · Accepted Answer · 2017-09-13 17:08:24Z

3

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 %>

edited Sep 13, 2017 at 17:08

answered Sep 13, 2017 at 14:41

Ursus

30.1k3 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cyzanfar Over a year ago

I like this a lot! Great answer.

random_user_0891 Over a year ago

So I see Job.group(:experience).count returns (0.3ms) SELECT COUNT(*) AS count_all, "jobs"."experience" AS jobs_experience FROM "jobs" GROUP BY "jobs"."experience" => {"0-2 years"=>1, "10+ years"=>2, "2-5 years"=>1, "5-10 years"=>2} how do I call this in the view though?

Ursus Over a year ago

I udpated my answer

Danil Speransky · Accepted Answer · 2017-09-13 20:24:51Z

0

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 %>

edited Sep 13, 2017 at 20:24

answered Sep 13, 2017 at 16:41

Danil Speransky

30.5k6 gold badges70 silver badges78 bronze badges

3 Comments

random_user_0891 Over a year ago

the only thing is that the links aren't ordered the way I want on the page anymore. Do you know how I could get the links returned so it would be in the order 0-2 years, 2-5 years, 5-10 years, 10+ years?

random_user_0891 Over a year ago

Unfortunately that doesn't work, getting "undefined method `each' for nil:NilClass" coming from <% @exp_names.each do |name| %>

random_user_0891 Over a year ago

Good to go now, I just had to add '@exp_names ' and '@exp_counts' inside the index action. Thanks for all the help, much appreciated!

Collectives™ on Stack Overflow

Rails: Get count of values from database

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related