Forum: Ruby on Rails Retrieving count of individual items among the table

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jay C. (Guest)
on 2009-06-05 05:01
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!
Marnen L. (Guest)
on 2009-06-05 07:40
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
removed_email_address@domain.invalid
Jay C. (Guest)
on 2009-06-06 14:58
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
> removed_email_address@domain.invalid

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")
>
Jay C. (Guest)
on 2009-06-06 18:59
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
>> removed_email_address@domain.invalid
>
> 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 C. (Guest)
on 2009-06-06 19:12
(Received via mailing list)
On Jun 6, 3:59 pm, Jay C. <removed_email_address@domain.invalid>
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
Jay C. (Guest)
on 2009-06-06 20:05
Frederick C. wrote:
> On Jun 6, 3:59�pm, Jay C. <removed_email_address@domain.invalid>
> 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
Frederick C. (Guest)
on 2009-06-06 20:18
(Received via mailing list)
On Jun 6, 5:05 pm, Jay C. <removed_email_address@domain.invalid>
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
Jay C. (Guest)
on 2009-06-06 20:38
Frederick C. wrote:
> On Jun 6, 5:05�pm, Jay C. <removed_email_address@domain.invalid>
> 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
Frederick C. (Guest)
on 2009-06-07 01:41
(Received via mailing list)
On Jun 6, 5:38 pm, Jay C. <removed_email_address@domain.invalid>
wrote:
>
> '<br>' + listhub.item2 + '<br>' + listhub.item3+ '<br>' + 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
Colin L. (Guest)
on 2009-06-07 10:44
(Received via mailing list)
2009/6/6 Jay C. <removed_email_address@domain.invalid>:
>      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
This topic is locked and can not be replied to.