# Complicated controller find with lots of has\_one and boolean filters

**URL:** <https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969>\
**Category:** Rails\
**Created:** [November 1, 2007, 8:23pm UTC](https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969 "2007-11-01T20:23:24Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![thoraxe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/9dc877/32.png) [@thoraxe](https://www.ruby-forum.com/u/thoraxe)\
**Post date:** [November 1, 2007, 8:23pm UTC](https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969/1 "2007-11-01T20:23:24Z")

</div>

Hi! OK… here is some background. I’m creating a directory of  
stables and things and I’m now trying to build the search portion of  
it. I know that I can hack about using the filters and lots of logic  
to reduce the result set in the controller before handing it off to  
the view, but I am pretty sure that there is a lot more that I can do  
with the find statement to pre-reduce it.

Here’s the layout of some of the models and one of the migrations:

## barn.rb (model)

class Barn \< Contact  
has\_one :barn\_info, :dependent =\> :destroy  
has\_one :western, :dependent =\> :destroy  
has\_one :english, :dependent =\> :destroy  
has\_one :trail, :dependent =\> :destroy  
has\_one :boarding, :dependent =\> :destroy  
has\_one :leasing, :dependent =\> :destroy  
has\_one :hsale, :dependent =\> :destroy  
has\_one :breed, :dependent =\> :destroy  
has\_one :party, :dependent =\> :destroy  
has\_one :camp, :dependent =\> :destroy  
has\_one :ranch, :dependent =\> :destroy  
end

## Western.rb (model)

class Western \< ActiveRecord::Base  
belongs\_to :barn  
end

## Western migration

class CreateWesterns \< ActiveRecord::Migration  
def self.up  
create\_table :westerns do |t|  
t.column :barn\_id, :int  
t.column :pleasure, :boolean  
t.column :reining, :boolean  
t.column :roping, :boolean  
t.column :penning, :boolean  
t.column :cutting, :boolean  
t.column :barrels, :boolean  
t.column :polebending, :boolean  
end  
end

Now, I’ve created a search page with lots of check boxes. For  
example, there is a check box for Western as well as a check box for  
each of the boolean fields in the western table. I’m just using  
check\_box\_tag to create the check boxes and I am naming them with the  
same name as the column name.

I only care about the stuff that is checked. If a Western entry has  
penning and cutting and someone checks only penning, it’s OK to return  
that entry. Because of this, I can just check  
params[:columnname].nil? to see if they checked a box.

Here’s where the quandary is. It seems very un-dry, un-rails-like,  
and just slow as crud (no pun intended) to find all the barns, check  
if they have western entries, then check for the checked columns,  
deleting entries that don’t have the right stuff. But, I have no idea  
how to build the find correctly.

I am guessing that I have to do some find\_by\_sql with some complex  
join statements, but this is where things begin to elude me.

Any help would be appreciated, or any direction towards things to read  
that might help.

---

<div class="post-metadata">

**Author:** ![thoraxe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/9dc877/32.png) [@thoraxe](https://www.ruby-forum.com/u/thoraxe)\
**Post date:** [November 1, 2007, 8:23pm UTC](https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969/2 "2007-11-01T20:23:24Z")

</div>

On 1 Nov 2007, at 19:11, thoraxe wrote:

> Any help would be appreciated, or any direction towards things to read  
> that might help.

How about Barn.find(:all, :joins =\> ‘inner join westerns on barn\_id =  
barns.id’, :conditions =\> …)

Fred

---

<div class="post-metadata">

**Author:** ![thoraxe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/9dc877/32.png) [@thoraxe](https://www.ruby-forum.com/u/thoraxe)\
**Post date:** [November 1, 2007, 8:37pm UTC](https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969/3 "2007-11-01T20:37:29Z")

</div>

On Nov 1, 3:22 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> How about Barn.find(:all, :joins =\> ‘inner join westerns on barn\_id =  
> barns.id’, :conditions =\> …)
> 
> Fred

That has definitely moved me much closer. However, I’m still a little  
stuck:

Barn.find(:all, :joins =\> ‘inner join englishes on barn\_id =  
contacts.id’, :conditions =\> {saddleseat =\> 1}) begets:

ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column  
‘contacts.saddleseat’ in ‘where clause’: SELECT \* FROM contacts inner  
join englishes on barn\_id = contacts.id WHERE (contacts.`saddleseat` =

1. AND ( (contacts.`type` = ‘Barn’ ) )

Now, if you just do the join with no conditions, it works just fine,  
and I can see some barns are returned where saddleseat = 1.

---

<div class="post-metadata">

**Author:** ![thoraxe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/9dc877/32.png) [@thoraxe](https://www.ruby-forum.com/u/thoraxe)\
**Post date:** [November 1, 2007, 9:21pm UTC](https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969/4 "2007-11-01T21:21:21Z")

</div>

On 1 Nov 2007, at 19:36, thoraxe wrote:

> Barn.find(:all, :joins =\> ‘inner join englishes on barn\_id =  
> contacts.id’, :conditions =\> {saddleseat =\> 1}) begets:
> 
> ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column  
> ‘contacts.saddleseat’ in ‘where clause’: SELECT \* FROM contacts inner  
> join englishes on barn\_id = contacts.id WHERE (contacts.`saddleseat` =
> 
> 1. AND ( (contacts.`type` = ‘Barn’ ) )
> 
> Now, if you just do the join with no conditions, it works just fine,  
> and I can see some barns are returned where saddleseat = 1.

When you use a hash of conditions it always assumes the conditions are  
on the ‘main’ table (barns in this case). You need to specify your  
conditions old skool: :conditions =\>[‘saddleseat = ?’, true]

Fred

---

<div class="post-metadata">

**Author:** ![thoraxe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/9dc877/32.png) [@thoraxe](https://www.ruby-forum.com/u/thoraxe)\
**Post date:** [November 2, 2007, 12:52pm UTC](https://www.ruby-forum.com/t/complicated-controller-find-with-lots-of-has-one-and-boolean-filters/118969/5 "2007-11-02T12:52:33Z")

</div>

> When you use a hash of conditions it always assumes the conditions are  
> on the ‘main’ table (barns in this case). You need to specify your  
> conditions old skool: :conditions =\>[‘saddleseat = ?’, true]

Amazing, worked like a charm.

The last thing I’m trying to do is figure out how to build up the  
conditions string ex:  
:conditions =\> [‘saddleseat = ? AND jumping = ?’, 1, 1]

I saw somewhere how to concatenate strings using \<\< which would work  
find if I added the AND but I’m not sure how to handle the stuff after  
the string. I can’t find the page that I’m looking for that I found  
the other day…