多个连接到相同型号使用多个belongs_to的关联 [英] Multiple joins to the same model using multiple belongs_to associations

问题描述

我有两个belongs_to的协会会员类的类协议 - 为一级和二级引用的：

I have a class Agreement that has two belongs_to associations to a Member class - referenced as primary and secondary:

class Agreement < ActiveRecord::Base
  belongs_to :primary, class_name: 'Member'
  belongs_to :secondary, class_name: 'Member'
  ...

  def self.by_member(member_attribute_hash)
    # returns any agreements that has a primary OR secondary member that matches any of the values 
    # in the member_attribute_hash
    ...
  end
end

会员类没有该协定类的相关知识 - 它并不需要：

The Member class has no knowledge of the association with the Agreement class - it does not need to:

class Member < ActiveRecord::Base
  # contains surname, given_names, member_number
  ...

  def self.by_any(member_attribute_hash)
    # returns any member where the member matches on surname OR given_names OR member_number
    ...
  end
end

我想要做的是搜索，其中主要或次要构件的一套标准匹配的所有协议。

What I would like to do is search for all agreements where the primary or secondary member matches a set of criteria.

从previous工作（<一个href=\"http://stackoverflow.com/questions/14139609/can-you-add-clauses-in-a-where-block-conditionally-when-using-squeel\">see问题＃14139609 ），我已经整理出了如何构建有条件的地方为子句Member.by_any（）。

From previous work (see question #14139609), I've sorted out how to build the conditional where clause for Member.by_any().

试图重新使用方法，同时寻求协议使我试试这个：

Trying to reuse that method while search for Agreements led me to try this:

class Agreement < ActiveRecord::Base
  ...

  def self.by_member(member_attribute_hash)
    Agreement.joins{primary.outer}.merge(Member.by_any(member_attribute_hash)).joins{secondary.outer}.merge(Member.by_any(member_attribute_hash))
  end
end

在控制台运行此，以 member_attribute_hash = {姓：弗洛伊德'} ，生成的SQL不履行第二个加盟成员生成的别名

On running this in the console, with a member_attribute_hash = {surname: 'Freud'}, the generated SQL fails to honour the alias generated for the second join to member:

SELECT "agreements".* 
FROM "agreements" 
  LEFT OUTER JOIN "members" 
        ON "members"."id" = "agreements"."primary_id" 
  LEFT OUTER JOIN "members" "secondarys_agreements" 
        ON "secondarys_agreements"."id" = "agreements"."secondary_id" 
WHERE "members"."surname" ILIKE 'Freud%' 
  AND "members"."surname" ILIKE 'Freud%'

注意WHERE子句中的重复的情况。这将返回协议，其中的主要成员都像'弗洛伊德的姓，但忽略次要构件条件，因为没有流动的别名是通过合并。

Notice the duplicate conditions in the WHERE clause. This will return Agreements where the primary Member has a surname like 'Freud', but ignores the secondary Member condition because the alias is not flowing through the merge.

任何想法？

推荐答案

努力理解这一点之后，我结束了一个Squeel <$替换 Member.by_any 范围C $ C>筛：

After struggling to understand this, I ended up replacing the Member.by_any scope with a Squeel sifter:

class Member < ActiveRecord::Base
  # contains surname, given_names, member_number
  ...

  def self.by_any(member_attribute_hash)
    # returns any member where the member matches on surname OR given_names OR member_number
    squeel do
      [
        (surname =~ "#{member[:surname]}%" if member[:surname].present?),
        (given_names =~ "#{member[:given_names]}%" if member[:given_names].present?),
        (member_number == member[:member_number] if member[:member_number].present?)
      ].compact.reduce(:|)
      # compact to remove the nils, reduce to combine the cases with |
    end
  end
end

唯一的区别（code-WISE），bewteen的筛和范围是替换的其中，在筛与 squeel 的范围。

The only difference (code-wise), bewteen the sifter and the scope is the replacement of the where in the scope with squeel in the sifter.

所以，而是采用了合并从协议模型访问 Member.by_any 范围，我是现在能够引用会员：从协议模型by_any 筛。它看起来像：

So, instead of using a merge to access the Member.by_any scope from the Agreement model, I was now able to reference the Member :by_any sifter from the Agreement model. It looked like:

class Agreement < ActiveRecord::Base
  ...

  def self.by_member(member_attribute_hash)
    Agreement.joins{primary.outer}.where{primary.sift :by_any, member_attribute_hash}.joins{secondary.outer}.where{secondary.sift :by_any, member_attribute_hash}
  end
end

本固定走样的问题 - 的开始庆祝的：

This fixed the aliasing issue - begin celebrating!:

SELECT "agreements".* 
FROM "agreements" 
  LEFT OUTER JOIN "members" 
        ON "members"."id" = "agreements"."primary_id" 
  LEFT OUTER JOIN "members" "secondarys_agreements" 
        ON "secondarys_agreements"."id" = "agreements"."secondary_id" 
WHERE "members"."surname" ILIKE 'Freud%' 
  AND "secondarys_agreements"."surname" ILIKE 'Freud%'

但我还是没有得到我预期的结果 - 的庆典搁置的。什么问题？在和 where子句中是错误的。多一点挖（一夜远离电脑）后，刷新头脑决定试试这个：

but I still wasn't getting the results I expected - celebration put on hold. What was wrong? The AND in the where clause was wrong. After a bit more digging (and a night away from the computer), a refreshed mind decided to try this:

class Agreement < ActiveRecord::Base
  ...

  def self.by_member(member_attribute_hash)
    Agreement.joins{primary.outer}.joins{secondary.outer}.where{(primary.sift :by_any, member_attribute_hash) | (secondary.sift :by_any, member_attribute_hash)}
  end
end

生产这种

SELECT "agreements".* 
FROM "agreements" 
  LEFT OUTER JOIN "members" 
        ON "members"."id" = "agreements"."primary_id" 
  LEFT OUTER JOIN "members" "secondarys_agreements" 
        ON "secondarys_agreements"."id" = "agreements"."secondary_id" 
WHERE ((("members"."surname" ILIKE 'Freud%') 
   OR ("secondarys_agreements"."surname" ILIKE 'Freud%')))

甜啊... 重启庆典的......现在我得到了有根据一个筛子定义的规则相匹配的一级或二级会员协议。

Ah sweet... restart celebration... now I get Agreements that have a primary or secondary Member that matches according to the rules defined in a single sifter.

和所有他上Squeel所做的工作，一个大的喊出进入厄尼·米勒。

And for all the work he's done on Squeel, a big shout-out goes to Ernie Miller.

这篇关于多个连接到相同型号使用多个belongs_to的关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！