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!
--
Posted via http://www.ruby-forum.com/.
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)
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'' }) Even if an ItemList *always* has 4 items, this is likely to save you much time in the long run. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
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?
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 Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
Jeff Schwab wrote:> Jay Covington wrote: >> :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)This method works! Thanks for replying! -- Posted via http://www.ruby-forum.com/.