Complex rails query - unions? sub-select? can I still used named_scope?

Question

Part of why I love Rails is that I hate SQL - I think it's more like an assembly language that should be manipulated with higher level tools such as ActiveRecord. I seem to have hit the limits of this approach, however, and I'm out of my depth with the SQL.

I have a complex model with lots of sub-records. I also have a set 30-40 named_scopes that implement the business logic from the client. These scopes get chained together conditionally, which is why I have those joins_ scopes so the joins don't get clobbered.

I've got a couple of them that don't work right, or at least not how the client wants them to work. Here's a rough idea of the model structure, with a few named scopes (not all needed for the example) that illustrate my approach and indicate my problems. (please forgive any syntax errors)

class Man < ActiveRecord::Base
  has_many :wives

  named_scope :has_wife_named       lambda { |n| { :conditions => { :wives => {:name => n}}}}
  named_scope :has_young_wife_named lambda { |n| { :conditions => { :wives => {:name => n, :age => 0..30}}}}
  named_scope :has_yw_named_v2      lambda { |n| { :conditions => ["wives.name = ? AND wives.age <= 30", n]}}
  named_scope :joins_wives         :joins => :wives

  named_scope :has_red_cat          :conditions => { :cats => {:color => 'red'}}        
  named_scope :has_cat_of_color     lambda { |c| { :conditions => { :cats => {:color => c}}}}
  named_scope :has_7yo_cat          :conditions => { :cats => {:age => 7}}
  named_scope :has_cat_of_age       lambda { |a| { :conditions => { :cats => {:age => a}}}}
  named_scope :has_cat_older_than   lambda { |a| { :conditions => ["cats.age > ?", a] }}
  named_scope :has_cat_younger_than lambda { |a| { :conditions => ["cats.age < ?", a] }}
  named_scope :has_cat_fatter_than  lambda { |w| { :conditions => ["cats.weight > ?", w] } }
  named_scope :joins_wives_cats     :joins => {:wives => :cats}
end

class Wife < ActiveRecord::Base
  belongs_to :man
  has_many :cats
end

class Cat < ActiveRecord::Base
  belongs_to :wife
end

1. I can find men whose wives have cats that are red AND seven years old

   @men = Man.has_red_cat.has_7yo_cat.joins_wives_cats.scoped({:select => 'DISTINCT men'})
   

   And I can even find men whose wives have cats that are over 20 pounds and over 6 years old

   @men = Man.has_cat_fatter_than(20).has_cat_older_than(5).joins_wives_cats.scoped({:select => 'DISTINCT men'})
   

   But that's not what I want. I want to find the men whose wives have amongst them at least one red cat and one seven year old cat, which need not be the same cat, or to find the men whose wives have amongst them at least one cat above a given weight and one cat older than a given age.
   (in subsequent examples, please assume the presence of the appropriate joins_ and DISTINCT)

2. I can find men with wives named Esther

   @men = Man.has_wife_named('Esther')
   

   I can even find men with wives named Esther, Ruth OR Ada (sweet!)

   @men = Man.has_wife_named(['Esther', 'Ruth', 'Ada'])
   

   but I want to find men with wives named Esther AND Ruth AND Ada.

3. Ha ha, only joking, actually, I need this: I can find men with wives under 30 named Esther

   @men = Man.has_young_wife_named('Esther')
   

   find men with young wives named Esther, Ruth or Ada

   @men = Man.has_young_wife_named(['Esther', 'Ruth', 'Ada'])
   

   but as above I want to find men with young wives named Esther AND Ruth AND Ada. Fortunately, the minimum is fixed in this case, but it would be nice to specify a minimum age as well.

4. is there a way to test for an inequality with a hash syntax, or do you always have to revert to :conditions => ["", n] - note the difference between has_young_wife_named and has_yw_named_v2 - I like the first better, but the range only works for finite values. If you're looking for an old wife, I guess you could use a..100 but then when a wife turns 101 years old she drops off the search. (hmm. can she cook? j/k)

5. is there a way to use a scope within a scope? I'd love it if :has_red_cat could use :has_cat_of_color somehow, or if there was some way to use the scope from a child record in its parent, so I could put the cat related scopes into the Wife model.

I really don't want to do this in straight SQL without using named_scope, unless there's something else actually nicer - suggestions for plugins and whatnot greatly appreciated, or direction into the sort of SQL I'll need to learn. A friend suggested that UNIONs or sub-searches would work here, but those don't seem to be discussed much in the context of Rails. I don't yet know anything about views - would they be useful? Is there a rails-happy way to make them?

Thank you!

As I was going to St Ives
I met a man with seven wives
Each wife had seven sacks
Each sack had seven cats
Each cat had seven kits
Kits, cats, sacks, wives
How many were going to St Ives?

Answer 1

You used the most native solution for Rails. Straight SQL will have the same performance so there is no reason to use it.

Answer 2

Well, I've had great results with named_scopes like these:

named_scope :has_cat_older_than   lambda { |a| { :conditions => ["men.id in ( select man_id from wives where wives.id in ( select wife_id from cats where age > ? ) )", a] } }

and

named_scope :has_young_wife_named lambda { |n| { :conditions => ["men.id in ( select man_id from wives where name = ? and age < 30)", n] } }

I can now successfully do

Member.has_cat_older_than(6).has_young_wife_named('Miriam').has_young_wife_named('Vashti')

and get what I'm expecting. These scopes don't require the use of the joins, and they seem to play well with the other styled joins.

w00t!

Commentary elicited on whether this is an efficient way to do this, or if there is a more 'rails-y' way. Some way to include a scope from another model as an sql subquery fragment could be useful...

Answer 3

I've used construct_finder_sql to accomplish the subselect of one named_scope within another. It may not be for everyone, but using it allow us to DRY up a couple of named_scopes we used for reports.

Man.has_cat_older_than(6).send(:construct_finder_sql,{})

Try that in your script/console.