Databases
StackOverflow2013
Postsdbo
POComplex rails query - unions? sub-select? can I still used named_scope?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComplex rails query - unions? sub-select? can I still used named_scope?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USstephan.com
    1. USstephan.com
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POComplex rails query - unions? sub-select? can I still used named_scope?
      1. USstan
      1. VTFavorite
    2. VO
      singulars
      1. POComplex rails query - unions? sub-select? can I still used named_scope?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POComplex rails query - unions? sub-select? can I still used named_scope?
      1. USumar
      1. VTFavorite
    1. COIt's a really interesting question in the abstract. And I understand that your model and scope names are derived from a nursery rhyme. But it's offputting, for me. Wife :belongs_to Man? Seriously?
      singulars
      1. POComplex rails query - unions? sub-select? can I still used named_scope?
      1. USSarah Mei
    2. COyeah, I thought of that when I was writing it, but the poem had popped into my head and I couldn't get rid of it.
      singulars
      1. POComplex rails query - unions? sub-select? can I still used named_scope?
      1. USstephan.com
    3. COI would like to find a good way to do `UNION`s in named scopes in AR. I know you can do do them in SQLAlchemy.
      singulars
      1. POComplex rails query - unions? sub-select? can I still used named_scope?
      1. USmikelikespie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload