Conditions for Multiple Columns?

rubyonrails-talk
1

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!

2

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)

3

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

4

Rob Biedenharn wrote:

5

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

6

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!