与ActiveRecord的表自加盟 [英] Self-join on a table with ActiveRecord

问题描述

我有一个ActiveRecord名为名称包含各种名义语言。

I have an ActiveRecord called Name which contains names in various Languages.

class Name < ActiveRecord::Base
  belongs_to :language

class Language < ActiveRecord::Base
  has_many :names

在一种语言查找名字是很容易的：

Finding names in one language is easy enough:

Language.find(1).names.find(whatever)

但我需要找到配对的其中两个语言1和语言2具有相同的名称。在SQL中，这要求一个简单的自联接：

But I need to find matching pairs where both language 1 and language 2 have the same name. In SQL, this calls for a simple self-join:

SELECT n1.id,n2.id FROM names AS n1, names AS n2
  WHERE n1.language_id=1 AND n2.language_id=2
    AND n1.normalized=n2.normalized AND n1.id != n2.id;

我怎么可以这样做与ActiveRecord的查询？请注意，我需要找到对的名称（=本场比赛的双方），在语文1名，恰好匹配的东西不只是一个列表。

How can I do a query like this with ActiveRecord? Note that I need to find pairs of names (= both sides of the match), not just a list of names in language 1 that happens to match with something.

有关加分，替换 n1.normalized = n2.normalized 与 n1.normalized LIKE n2.normalized ，由于该领域可能包含SQL通配符。

For bonus points, replace n1.normalized=n2.normalized with n1.normalized LIKE n2.normalized, since the field may contain SQL wildcards.

我也开至不同的建模数据的想法，但我想preFER，以避免各语言的表，如果我能。

I'm also open to ideas about modeling the data differently, but I'd prefer to avoid having separate tables for each language if I can.

推荐答案

试试这个：

ids = [1,2]
Name.all(:select    => "names.id, n2.id AS id2",
         :joins     => "JOIN names AS n2 
                              ON n2.normalized = names.normalized AND 
                                 n2.language_id != names.language_id AND
                                 n2.language_id IN (%s)" % ids.join(','),
         :conditions => ["names.language_id IN (?)", ids]
).each do |name|
  p "id1 : #{name.id}"
  p "id2 : #{name.id2}"
end

PS：请确保您净化传递给连接条件的参数

PS: Make sure you sanitize the parameters passed to the join condition.

这篇关于与ActiveRecord的表自加盟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！