Conditions for Multiple Columns?

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!

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@AgileConsultingLLC.com

Rob Biedenharn wrote:

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@AgileConsultingLLC.com

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!