# Conditions for Multiple Columns?

**URL:** <https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302>\
**Category:** Rails\
**Created:** [June 2, 2009, 4:08am UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302 "2009-06-02T04:08:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jayco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c77e96/32.png) [@jayco](https://www.ruby-forum.com/u/jayco)\
**Post date:** [June 2, 2009, 4:08am UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302/1 "2009-06-02T04:08:43Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![jayco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c77e96/32.png) [@jayco](https://www.ruby-forum.com/u/jayco)\
**Post date:** [June 2, 2009, 5:04pm UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302/2 "2009-06-02T17:04:39Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![jayco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c77e96/32.png) [@jayco](https://www.ruby-forum.com/u/jayco)\
**Post date:** [June 2, 2009, 5:24pm UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302/3 "2009-06-02T17:24:01Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![jayco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c77e96/32.png) [@jayco](https://www.ruby-forum.com/u/jayco)\
**Post date:** [June 2, 2009, 5:44pm UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302/4 "2009-06-02T17:44:39Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jayco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c77e96/32.png) [@jayco](https://www.ruby-forum.com/u/jayco)\
**Post date:** [June 2, 2009, 6:45pm UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302/5 "2009-06-02T18:45:39Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![jayco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c77e96/32.png) [@jayco](https://www.ruby-forum.com/u/jayco)\
**Post date:** [June 3, 2009, 12:52am UTC](https://www.ruby-forum.com/t/conditions-for-multiple-columns/169302/6 "2009-06-03T00:52:43Z")

</div>

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!