title: Complex rails query - unions? sub-select? can I still used named_scope?
body: <p>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.</p>
  
  <p>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 <code>joins_</code> scopes so the joins don't get clobbered.  </p>
  
  <p>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.  <em>(please forgive any syntax errors)</em></p>
  
  <pre><code>class Man &lt; ActiveRecord::Base
    has_many :wives
  
    named_scope :has_wife_named       lambda { |n| { :conditions =&gt; { :wives =&gt; {:name =&gt; n}}}}
    named_scope :has_young_wife_named lambda { |n| { :conditions =&gt; { :wives =&gt; {:name =&gt; n, :age =&gt; 0..30}}}}
    named_scope :has_yw_named_v2      lambda { |n| { :conditions =&gt; ["wives.name = ? AND wives.age &lt;= 30", n]}}
    named_scope :joins_wives         :joins =&gt; :wives
  
    named_scope :has_red_cat          :conditions =&gt; { :cats =&gt; {:color =&gt; 'red'}}        
    named_scope :has_cat_of_color     lambda { |c| { :conditions =&gt; { :cats =&gt; {:color =&gt; c}}}}
    named_scope :has_7yo_cat          :conditions =&gt; { :cats =&gt; {:age =&gt; 7}}
    named_scope :has_cat_of_age       lambda { |a| { :conditions =&gt; { :cats =&gt; {:age =&gt; a}}}}
    named_scope :has_cat_older_than   lambda { |a| { :conditions =&gt; ["cats.age &gt; ?", a] }}
    named_scope :has_cat_younger_than lambda { |a| { :conditions =&gt; ["cats.age &lt; ?", a] }}
    named_scope :has_cat_fatter_than  lambda { |w| { :conditions =&gt; ["cats.weight &gt; ?", w] } }
    named_scope :joins_wives_cats     :joins =&gt; {:wives =&gt; :cats}
  end
  
  class Wife &lt; ActiveRecord::Base
    belongs_to :man
    has_many :cats
  end
  
  class Cat &lt; ActiveRecord::Base
    belongs_to :wife
  end
  </code></pre>
  
  <ol>
  <li><p>I can find men whose wives have cats that are red AND seven years old </p>
  
  <pre><code>@men = Man.has_red_cat.has_7yo_cat.joins_wives_cats.scoped({:select =&gt; 'DISTINCT men'})
  </code></pre>
  
  <p>And I can even find men whose wives have cats that are over 20 pounds and over 6 years old</p>
  
  <pre><code>@men = Man.has_cat_fatter_than(20).has_cat_older_than(5).joins_wives_cats.scoped({:select =&gt; 'DISTINCT men'})
  </code></pre>
  
  <p>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.<br>
  <em>(in subsequent examples, please assume the presence of the appropriate <code>joins_</code> and <code>DISTINCT</code>)</em></p></li>
  <li><p>I can find men with wives named Esther</p>
  
  <pre><code>@men = Man.has_wife_named('Esther')
  </code></pre>
  
  <p>I can even find men with wives named Esther, Ruth OR Ada (sweet!)</p>
  
  <pre><code>@men = Man.has_wife_named(['Esther', 'Ruth', 'Ada'])
  </code></pre>
  
  <p>but I want to find men with wives named Esther AND Ruth AND Ada.</p></li>
  <li><p>Ha ha, only joking, actually, I need this:  I can find men with wives under 30 named Esther</p>
  
  <pre><code>@men = Man.has_young_wife_named('Esther')
  </code></pre>
  
  <p>find men with young wives named Esther, Ruth or Ada</p>
  
  <pre><code>@men = Man.has_young_wife_named(['Esther', 'Ruth', 'Ada'])
  </code></pre>
  
  <p>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.</p></li>
  <li><p>is there a way to test for an inequality with a hash syntax, or do you always have to revert to <code>:conditions =&gt; ["", n]</code> - note the difference between <code>has_young_wife_named</code> and <code>has_yw_named_v2</code> - 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 <code>a..100</code> but then when a wife turns 101 years old she drops off the search. <em>(hmm. can she cook? j/k)</em></p></li>
  <li><p>is there a way to use a scope within a scope?  I'd love it if <code>:has_red_cat</code> could use <code>:has_cat_of_color</code> 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 <code>Wife</code> model.</p></li>
  </ol>
  
  <p>I really don't want to do this in straight SQL without using <code>named_scope</code>, 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?</p>
  
  <p>Thank you!</p>
  
  <p><em>As I was going to St Ives<br>
  I met a man with seven wives<br>
  Each wife had seven sacks<br>
  Each sack had seven cats<br>
  Each cat had seven kits<br>
  Kits, cats, sacks, wives<br>
  How many were going to St Ives?</em></p>