Retrieving count of individual items among the table

11175 · June 5, 2009, 1:01am

I have a table that is displaying a list of 5 items. Next to each item I want to display the number of times that the item pops up in an individual list. Example:

Item1 (4) Item2 (2) Item3 (1) Item4 (7) Item5 (3)

Here is my code as is:

Index.rhtml.erb

<%= table.item1 + '' + @statcount %> <%= table.item2 + '' + @statcount %> <%= table.item3 + '' + @statcount %> <%= table.item4 + '' + @statcount %> <%= table.item5 + '' + @statcount %>

11175 · June 5, 2009, 3:40am

Jay Covington wrote: [...]

item_ids = 1..5 statcount_query = item_ids.map{|id| "item#{id} = BLANK "}.join(" OR ") @statcount = table.count(:all, :conditions => statcount_query).to_s

Well, first of all, you can forget about the map statement and put the array item_ids straight into :conditions -- Rails will understand it. But that won't get you what you need, because it will just return a total count of all the items that match *any* ID. I think you need to use :group here.

Best,

11175 · June 6, 2009, 10:58am

Marnen Laibow-Koser wrote:

Jay Covington wrote: [...]

item_ids = 1..5 statcount_query = item_ids.map{|id| "item#{id} = BLANK "}.join(" OR ") @statcount = table.count(:all, :conditions => statcount_query).to_s

Well, first of all, you can forget about the map statement and put the array item_ids straight into :conditions -- Rails will understand it. But that won't get you what you need, because it will just return a total count of all the items that match *any* ID. I think you need to use :group here.

Best, -- Marnen Laibow-Koser http://www.marnen.org marnen@marnen.org

I've tried the group method listed below for an individual column, but it returns just the column entry for "item1" and not the number of occurances or count. All I'm getting for the count is "#". Perhaps this code is outdated? Is the there a problem in my controller?

table.find(:all, :select => "item1, count(item1) as occurance",

11175 · June 6, 2009, 2:59pm

Jay Covington wrote:

Marnen Laibow-Koser wrote:

Jay Covington wrote: [...]

item_ids = 1..5 statcount_query = item_ids.map{|id| "item#{id} = BLANK "}.join(" OR ") @statcount = table.count(:all, :conditions => statcount_query).to_s

Well, first of all, you can forget about the map statement and put the array item_ids straight into :conditions -- Rails will understand it. But that won't get you what you need, because it will just return a total count of all the items that match *any* ID. I think you need to use :group here.

Best, -- Marnen Laibow-Koser http://www.marnen.org marnen@marnen.org

I've tried the group method listed below for an individual column, but it returns just the column entry for "item1" and not the number of occurances or count. All I'm getting for the count is "#". Perhaps this code is outdated? Is the there a problem in my controller?

table.find(:all, :select => "item1, count(item1) as occurance",

:group => "item1")

Still no luck. Is there a way to reference <%= table.item1 %> where BLANK is in the following code?:

item_ids = 1..5 statcount_query = item_ids.map{|id| "item#{id} = BLANK "}.join(" OR ") @statcount = table.count(:all, :conditions => statcount_query).to_s

Frederick_Cheung · June 6, 2009, 3:10pm

What does your data look like ? count will always return either a single number or a hash of values to counts (if you've used a :group option) (to be quite precise, an instance of ActiveSupport::OrderedHash which depending on the rails version you have is backed by a different class).

Fred

11175 · June 6, 2009, 4:05pm

Frederick Cheung wrote:

Still no luck. Is there a way to reference <%= table.item1 %> where BLANK is in the following code?:

item_ids = 1..5 � � statcount_query = item_ids.map{|id| "item#{id} = BLANK "}.join(" OR ") � � @statcount = table.count(:all, :conditions => statcount_query).to_s

What does your data look like ? count will always return either a single number or a hash of values to counts (if you've used a :group option) (to be quite precise, an instance of ActiveSupport::OrderedHash which depending on the rails version you have is backed by a different class).

Fred

The data is simply a database of lists. For example, the list ("grocery") that contains: "title", "item1", "item2", "item3", "item4, "item5". It was built behind a scaffold. So the index.rhtml shows all of my lists. Ok, no problem there. Here is example output:

"Grocery List" Apples Pears Oranges Blah.. Blah..

and the list output is coded in the index of course as : <%= title.grocery %> <%= item1.grocery %> <%= item2.grocery %> etc. etc.

Frederick_Cheung · June 6, 2009, 4:17pm

What I meant by that is what is the structure of your data (columns, etables etc.)

Fred

11175 · June 6, 2009, 4:38pm

Frederick Cheung wrote:

>> @statcount = table.count(:all, :conditions => statcount_query).to_s ("grocery") that �contains: "title", "item1", "item2", "item3", "item4, "item5". It was built behind a scaffold. So the index.rhtml shows all of my lists. Ok, no problem there. Here is example output:

What I meant by that is what is the structure of your data (columns, etables etc.)

Fred

Here is the format of index.rhtml and database layout:

</td>

<% end %>

</tr>

<% end %>

</table>

Frederick_Cheung · June 6, 2009, 9:40pm

Frederick Cheung wrote: >> >> @statcount = table.count(:all, :conditions => statcount_query).to_s >> ("grocery") that contains: "title", "item1", "item2", "item3", "item4, >> "item5". It was built behind a scaffold. So the index.rhtml shows all of >> my lists. Ok, no problem there. Here is example output:

> What I meant by that is what is the structure of your data (columns, > etables etc.)

> Fred

Here is the format of index.rhtml and database layout:

<table> <% @listhubs.in_groups_of(3, false) do |row_listhubs| %> <tr> <% for listhub in row_listhubs %> <td><%= listhub.title + (link_to 'edit', edit_listhub_path(listhub) + ' ' + listhub.item1 + ' ' + listhub.item2 + ' ' + listhub.item3+ ' ' + listhub.item4+ ' ' + listhub.item5+ ' ' + listhub.item6 + ' ' + listhub.item7 + ' ' + listhub.item8 + ' ' + listhub.item9 + ' ' + listhub.item10 %>

</td>

<% end %>

</tr>

<% end %>

</table>

---------------------------------------------------------------------------

class CreateListhubs < ActiveRecord::Migration def self.up create_table :listhubs do |t| t.column :title, :string t.column :item1, :string t.column :item2, :string t.column :item3, :string t.column :item4, :string t.column :item5, :string t.column :item6, :string t.column :item7, :string t.column :item8, :string t.column :item9, :string t.column :item10, :string

ah, so a list having 3 apples is represented by 3 of the columns having value 'apple' ? That's an unusual way of organising your data, and doing that sort of counting in sql is a nightmare. I would do things like this:

class Listhub def values [item1, item2, item3, item4, item5, item6, item7, item8, item9, item10].compact end end

then look at values. You can use group_by to group identical items, ie

values.group_by {|value| value}

will return values like {"apple"=>["apple", "apple", "apple"], "banana"=>["banana"], "pear"=>["pear", "pear"]}

and then it is simple enough to use map on that to get counts for each key, eg

values.group_by {|v| v}.map {|key, items|[key, items.length]}

#=> [["apple", 3], ["banana", 1], ["pear", 2]] (assuming that values evaluated to ["apple", "pear", "apple", "apple", "banana", "pear"])

Fred

Colin_Law1 · June 7, 2009, 6:43am

Have you considered having a separate table of list_items with a HABTM relationship between the listhub and the list_items? Alternatively if an Apple on one list should be a different object to one on another (in other words are the list_items objects in their own right or are they object types) then listhub has_many list_items and list_item belongs_to listub. These may appear to be more complex solutions but in the long run might prove easier. Your counting for example would be much easier.

Colin