复杂的铁路查询 - 工会？子选择？我还可以使用named_scope吗？

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.

我喜欢Rails的部分原因是我讨厌SQL - 我认为它更像是一种汇编语言,应该使用更高级别的工具(如ActiveRecord)进行操作。然而,我似乎已经达到了这种方法的极限,而且我已经超出了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.

我有一个包含大量子记录的复杂模型。我还有一组30-40个named_scopes,它们从客户端实现业务逻辑。这些范围有条件地链接在一起,这就是为什么我有这些连接范围,所以连接不会被破坏。

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

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

我甚至可以找到那些妻子有超过20磅和超过6岁的猫的男人
```
@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)

但那不是我想要的。我想找到那些妻子中至少有一只红猫和一只七岁猫的男人,他们不一定是同一只猫,或者找到那些妻子中至少有一只猫超过给定体重的男人。一只比规定年龄大的猫。 (在后续示例中,请假设存在相应的连接_和DISTINCT)
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!)

我甚至可以找到名叫Esther,Ruth OR Ada的妻子的男人(甜蜜!)
```
@men = Man.has_wife_named(['Esther', 'Ruth', 'Ada'])
```
but I want to find men with wives named Esther AND Ruth AND Ada.

但我想找到名叫Esther和Ruth AND Ada的妻子的男人。
Ha ha, only joking, actually, I need this: I can find men with wives under 30 named Esther

哈哈,只是开玩笑,其实,我需要这个:我可以找到30岁以下名叫以斯帖的妻子
```
@men = Man.has_young_wife_named('Esther')
```
find men with young wives named Esther, Ruth or Ada

找到名叫Esther,Ruth或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.

但如上所述,我想找到名叫Esther和Ruth AND Ada的年轻妻子的男人。幸运的是,在这种情况下,最小值是固定的,但是指定最小年龄也是很好的。
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)

有没有办法用哈希语法测试不等式,或者你总是要恢复到:conditions => [“”,n] - 注意has_young_wife_named和has_yw_named_v2之间的区别 - 我喜欢第一个更好,但是范围仅适用于有限值。如果你正在寻找一位老太太,我想你可以使用一个......,但是当一位妻子年满101岁时,她会放弃搜索。 (嗯。她可以做饭吗?j / k)
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.

有没有办法在范围内使用范围?我喜欢它,如果:has_red_cat可能会以某种方式使用:has_cat_of_color,或者如果有某种方法可以使用其父级中的子记录中的范围,那么我可以将与猫相关的范围放入Wife模型中。

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?

我真的不想在没有使用named_scope的情况下在直接SQL中执行此操作,除非实际上有更好的东西 - 插件的建议和诸如此类的非常感谢,或指向我需要学习的SQL类型。一位朋友建议UNIONs或子搜索在这里工作,但在Rails的上下文中似乎没有多少讨论。我对视图一无所知 - 它们会有用吗?是否有一种铁路开心的方式来制作它们?

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?

当我去圣艾夫斯时,我遇到了一个有七个妻子的男人每个妻子有七个麻袋每个麻袋有七只猫每只猫有七个套装套装,猫,麻袋,妻子有多少人要去圣艾夫斯?

3 个解决方案

#1

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

好吧,我使用这样的named_scopes得到了很好的结果:

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

评论引出了这是否是一种有效的方法,或者是否有更多的“轨道式”方式。将另一个模型的范围包含为sql子查询片段的某种方法可能很有用......

#2

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.

我使用了construct_finder_sql来完成另一个中的一个named_scope的子选择。它可能不适合所有人,但使用它可以让我们干掉我们用于报告的几个named_scope。

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

Try that in your script/console.

在脚本/控制台中尝试一下。

#3

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

您使用了Rails最本机的解决方案。直接SQL将具有相同的性能,因此没有理由使用它。

#1