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