Complicated controller find with lots of has_one and boolean filters

rubyonrails-talk
#1

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.

#2

How about Barn.find(:all, :joins => 'inner join westerns on barn_id = barns.id', :conditions => ...)

Fred

#3

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.

#4

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

#5

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...