Conditions for Multiple Columns?

Rails
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 C. 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)

3

On Jun 2, 2009, at 10:17 AM, Jeff S. wrote:

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 B. http://agileconsultingllc.com
[email protected]

4

Rob B. wrote:

item_list_id: integer
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?

5

On Jun 2, 2009, at 11:43 AM, Jeff S. wrote:

code I
apple_query = item_ids.map {|id| “item#{id} = ‘Apple’” }.join(" OR
list: string
has_many :item_list_items
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 B. http://agileconsultingllc.com
[email protected]

6

Jeff S. wrote:

Jay C. 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!