CODEWITHSUNDEEP
rubyruby-on-rails-3activerecord
Sri
Sri

Reputation: 2273

Number of string value occurrences for distinct another column value

I have a model Counter which returns the following records:

name.....flowers.....counter  
vino.....rose.........1
vino.....lily.........1 
gaya.....rose.........1 
rosi.....lily.........1 
vino.....lily.........1 
rosi.....rose.........1 
rosi.....rose.........1

I want to display in the table like:

name | Rose | Lily |
---------------------
Vino |  1   | 2    |
---------------------
Gaya |  1   | 0    |
---------------------
Rosi |  2   | 1    |

I want to display the count of flowers for each distinct name. I have tried the following and wondering how can I do it elegantly?

def counter_results
  @counter_results= {}
  Counter.each do |name|
    rose = Counter.where(flower: 'rose').count
    lily= Counter.where(flower: 'lily').count
    @counter_results['name'] = name
    @counter_results['rose_count'] = rose
    @counter_results['lily_count'] = lily
  end
  return @counter_results
end

which I don't get the hash values.

Upvotes: 0

Views: 48

Answers (1)

dinjas
dinjas

Reputation: 2125

This will give you slightly different output, but I think it is probably closer to what you want than what you showed.

You can use the query:

Counter.group([:name, :flowers]).sum(:counter)

To get a result set that looks like:

{ ["vino", "rose"] => 1, ["vino", "lily"] => 2, ["gaya", "rose"] => 1, ["gaya", "lily"] => 0, ... }

And you can do something like this to generate your hash:

def counter_results
  @counter_results = {}
  Counter.group([:name, :flowers]).sum(:counter).each do |k, v|
    @counter_results[k.join("_")] = v
  end
  @counter_results
end

The resulting hash would look like this:

{
  "vino_rose" => 1,
  "vino_lily" => 2,
  "gaya_rose" => 1,
  "gaya_lily" => 0,
  ...
}

Somebody else may have a better way to do it, but seems like that should get you pretty close.

Upvotes: 2

Related Questions