Rails - Jun 2009 - Retrieving count of individual items among the table

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 + ''</br>'' + @statcount %>
<%= table.item2 + ''</br>'' + @statcount %>
<%= table.item3 + ''</br>'' + @statcount %>
<%= table.item4 + ''</br>'' + @statcount %>
<%= table.item5 + ''</br>'' + @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!
-- 
Posted via http://www.ruby-forum.com/.

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-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

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-sbuyVjPbboAdnm+yROfE0A@public.gmane.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")
> 
-- 
Posted via http://www.ruby-forum.com/.

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-sbuyVjPbboAdnm+yROfE0A@public.gmane.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
-- 
Posted via http://www.ruby-forum.com/.

On Jun 6, 3:59 pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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

Frederick Cheung wrote:
> On Jun 6, 3:59�pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt>
> 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


-- 
Posted via http://www.ruby-forum.com/.

On Jun 6, 5:05 pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Frederick Cheung wrote:
> > On Jun 6, 3:59 pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> > 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:
>
What I meant by that is what is the structure of your data (columns,
etables etc.)

Fred

Frederick Cheung wrote:
> On Jun 6, 5:05�pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7m5VldFQK4jKA@public.gmane.orgt>
> 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) +
''<br>'' + listhub.item1 +
''<br>'' + listhub.item2 + ''<br>''
+ listhub.item3+ ''<br>'' + listhub.item4+
''<br>'' + listhub.item5+ ''<br>'' +
listhub.item6 + ''<br>'' + listhub.item7
+ ''<br>'' + listhub.item8 +
''<br>'' + listhub.item9 + ''<br>''
+
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




    end
  end

  def self.down
    drop_table :listhubs
  end
end
-- 
Posted via http://www.ruby-forum.com/.

On Jun 6, 5:38 pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Frederick Cheung wrote:
> > On Jun 6, 5:05 pm, Jay Covington
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> > 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) +
''<br>'' + listhub.item1 +
> ''<br>'' + listhub.item2 +
''<br>'' + listhub.item3+ ''<br>'' +
listhub.item4+
> ''<br>'' + listhub.item5+
''<br>'' + listhub.item6 + ''<br>''
+ listhub.item7
> + ''<br>'' + listhub.item8 +
''<br>'' + listhub.item9 + ''<br>''
+
> 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

>     end
>   end
>
>   def self.down
>     drop_table :listhubs
>   end
> end
> --
> Posted viahttp://www.ruby-forum.com/.

2009/6/6 Jay Covington
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>:
>...
>
> 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
>
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