Complex Rail Requests - Unions? choose sub? Can i use named_scope?

Question

Part of why I love Rails is that I hate SQL - I think it is more like an assembly language that needs to be manipulated with higher-level tools like ActiveRecord. However, it seems that I have encountered the limitations of this approach, and I am not in my depth with SQL.

I have a complex model with many subrecords. I also have a set of 30-40 named_scopes that implement business logic from the client. These areas are conditionally bound, so I have those joins_ , so the connections do not fail.

I have a couple of them that do not work correctly or, at least, not how the client wants them to work. Here is a rough idea of ​​the structure of a model with several namespaces (not all necessary for the example) that illustrate my approach and point out 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 

• I can find men whose wives have cats, red and seven year olds

   @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 is not what I want. I want to find men whose wives among them have at least one red cat and one seven-year-old cat, which does not have to be the same cat, or find men whose wives among them have at least one cat above a certain weight and one cat is older than a certain age.
  (in the following examples, please assume the corresponding joins_ and DISTINCT joins_ )

• 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 Hell (sweet!)

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

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

• Haha, just joking, in fact, 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, in this case the minimum is fixed, but it would be nice to also indicate the minimum age.

• Is there a way to check the inequality with the hash syntax or do you always need to return to :conditions => ["", n] - pay attention to the difference between has_young_wife_named and has_yw_named_v2 - I like the first one better, but the range only works for final values. If you are looking for an old wife, I think you could use a..100 , but then, when his wife turns 101, she stops searching. (Hmm, can she cook? J / k)

• Is there a way to use scope within scope? I would love it if :has_red_cat could somehow use :has_cat_of_color , or if there was some way to use the region from the child entry in its parent object, so I could bind the cat's snap regions to the Wife model.

I really do not want to do this in direct SQL without using named_scope , unless there is something even more pleasant - suggestions for plugins and something very valuable, or a direction to the SQL type that I will need to learn. A friend suggested that UNION or sub-searches will work here, but they don't seem to be discussed in the context of Rails. I still don't know anything about the views - would they be useful? Are rails a happy way to make them?

Thanks!

How I was going to St. Ives. I met a man with seven wives. Each wife had seven bags. Each bag had seven cats. Each cat had seven sets.
Sets, cats, bags, wives
How many of them were going to St. Yves?

Answer 1

Ok, I had great results with named_scope like this:

 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] } } 

Now I can successfully execute

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

and get what I expect. These areas do not require the use of unions, and they seem to blend well with other stylized unions.

w00t!

The comment raised whether this is an effective way to do this, or if there is more "rails-y". Some way to include a region from another model as a fragment of a sql subquery may be useful ...

Answer 2

I used construct_finder_sql to execute the subtask of one named_scope in another. It may not be for everyone, but using it allows us to DRY a couple of named packages that we used for reporting.

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

Try script / in the console.

Answer 3

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