On Jun 2, 2009, at 11:43 AM, Jeff Schwab wrote:
> Rob Biedenharn wrote:
>> On Jun 2, 2009, at 10:17 AM, Jeff Schwab wrote:
>>> Jay Covington wrote:
>>>> Hello,
>>>>
>>>> I have a table called "Itemlist" and have multiple columns in it
>>>> such as
>>>> "item1", "item2", "item3", "item4". These columns often have
>>>> repetitious
>>>> data between them and I'm trying to count it. The only working
>>>> code I
>>>> have so far is:
>>>>
>>>> statcount = Itemlist.count(:all, :condition => {:item1 => "Apple"})
>>>>
>>>> And this works fine for retrieving the count for "Apple" in all the
>>>> :item1 fields, but I need to retrieve the count for "Apple" from
>>>> the
>>>> columns :item2, :item3, and :item4 as well as :item1. Any ideas?
>>>> Thanks!
>>> item_ids = 1..4
>>> apple_query = item_ids.map {|id| "item#{id} = 'Apple'" }.join(" OR
>>> ")
>>> apple_count = ItemList.count(:all, :conditions => apple_query)
>>
>> Yuck! Can you change the schema? Granted, these names are lame, but
>> I'm guess that you have better information from which to confer
>> better
>> ones:
>>
>> ItemList
>> id: integer
>> list: string
>>
>> ItemListItem
>> id: integer
>> item_list_id: integer
>> item: string
>>
>> (and add an index on item_list_id)
>>
>>
>> class ItemList < ActiveRecord::Base
>> has_many :item_list_items
>> end
>>
>> class ItemListItem < ActiveRecord::Base
>> belongs_to :item_list
>> end
>>
>> statcount = ItemListItem.count(:conditions => { :item => 'Apple' })
>
> That looks like a fundamentally different operation, counting apples,
> rather than lists that contain apples. Won't any list containing
> multiple apples be over-counted?
Oh, I thought that's what you implied by "but I need to retrieve the
count for "Apple" from the columns :item2, :item3, and :item4 as well
as :item1."
If you want the count of ItemList that have at least one ItemListItem
that is "Apple", that would be:
statcount = ItemListItem.find(:all, :select => 'DISTINCT item_list_id',
:conditions => { :item => 'Apple' }).size
Or if you're not afraid of a little SQL,
statcount = ItemList.select_value("SELECT COUNT(DISTINCT
item_lists.id) FROM item_lists JOIN item_list_items ON
item_list_items.item_list_id = item_lists.id WHERE
item_list_items.item = 'Apple'")
[but I'd normally throw a sanitize_sql in there and parameterize the
'Apple']
-Rob
Rob Biedenharn http://agileconsultingllc.com
r...@agileconsultingllc.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
