Retrieving count of individual items among the table

Rails
1

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

And the code used for counting each item in the controller (here is
where I’m having trouble)

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

Where I have written BLANK is where I can’t find the right code to
insert. I need to define/pass <%= table.item1 %> into this section, but
I’m quite lost on how to do it exactly. I’ve tried several methods, but
they keep returning nothing or the total amount of items in the table.

Any help would be greatly appreciated!

2

Jay C. 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
[email protected]

3

Marnen Laibow-Koser wrote:

Jay C. 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
[email protected]

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")
4

Jay C. wrote:

Marnen Laibow-Koser wrote:

Jay C. 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
[email protected]

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

5

Frederick C. wrote:

On Jun 6, 3:59�pm, Jay C. [email protected]
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.

The format that I need to achieve is the same list of items but with
counts next to them. So below, I can see that Apples appear in 4
different lists (including this one) while Oranges appear in 5 lists.

“Grocery List”
Apples 4
Pears 1
Oranges 5
Blah… 8
Blah… 10

Hopefully that gives an idea of what I’m trying to do. The closest that
I’ve come to code wise is the below, but I don’t know how to connect the
table output to it or if there is totally another method for
accomplishing the same thing.

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

6

On Jun 6, 5:05 pm, Jay C. [email protected]
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

7

Frederick C. wrote:

On Jun 6, 5:05�pm, Jay C. [email protected]
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:

<% @listhubs.in_groups_of(3, false) do |row_listhubs| %> <% for listhub in row_listhubs %>

<% end %>

<% end %>

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

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

end

end

def self.down
drop_table :listhubs
end
end

8

On Jun 6, 3:59 pm, Jay C. [email protected]
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

9

On Jun 6, 5:38 pm, Jay C. [email protected]
wrote:

‘
’ + listhub.item2 + ‘
’ + listhub.item3+ ‘
’ + listhub.item4+
<% end %>
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

10

2009/6/6 Jay C. [email protected]:

   t.column :item5, :string
   t.column :item6, :string
   t.column :item7, :string
   t.column :item8, :string
   t.column :item9, :string
   t.column :item10, :string

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