Forum: Ruby on Rails Conditions for Multiple Columns?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jay C. (Guest)
on 2009-06-02 06:08
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!
Jeff S. (Guest)
on 2009-06-02 19:04
(Received via mailing list)
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)
Rob B. (Guest)
on 2009-06-02 19:24
(Received via mailing list)
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
removed_email_address@domain.invalid
Jeff S. (Guest)
on 2009-06-02 19:44
(Received via mailing list)
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?
Rob B. (Guest)
on 2009-06-02 20:45
(Received via mailing list)
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
removed_email_address@domain.invalid
Jay C. (Guest)
on 2009-06-03 02:52
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!
This topic is locked and can not be replied to.