Rails - Nov 2007 - Complicated controller find with lots of has_one and boolean filters

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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 1 Nov 2007, at 19:11, thoraxe wrote:
>
> 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.
How about Barn.find(:all, :joins => ''inner join westerns on barn_id
=
barns.id'', :conditions => ...)

Fred

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Nov 1, 3:22 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 1 Nov 2007, at 19:36, thoraxe wrote:
>
> On Nov 1, 3:22 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> 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.
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---