导轨 - 多重索引键协会 [英] Rails - Multiple Index Key Association

问题描述

似乎有许多方法来处理多个外键关联。每一个方式，我已经接近这有他们的平局的背影，当我是新来的Rails我相信其他人遇到过类似的情况，我可能做某件事情早已解决。

There seem to be a number of ways to handle a multiple foreign key association. Each way I have approached this has their draw backs, and as I am new to Rails I am convinced others have come across a similar scenario and I am probably working on something solved long ago.

我的问题是：

什么是处理多索引键关联的有效途径，同时还保留了所有其他的Rails SQL改性剂（如：包括等）

我的情况是：

我有如下（简化）表关联，它是用来通过链路连接的人给其他人：

I have a table association as follows (simplified), which is used to connect people to other people via links:

People
+----+-----------+
| id | name      |
+----+-----------+
| 1  | Joe       |
+----+-----------+
| 2  | Sally     |
+----+-----------+
| 3  | Bob       |
+----+-----------+                

Links
+----+-----------+---------+
| id | origin_id | rcvd_id |
+----+-----------+---------+
| 1  | 2         | 1       |
+----+-----------+---------+
| 2  | 1         | 3       |
+----+-----------+---------+                        
| 3  | 3         | 2       |
+----+-----------+---------+                        

从上面的链接表中的行1中，人们可以看到，一个人（萨利= 2）被链接到另一人（乔= 1）。

From row 1 of the above Links table, one can see that a Person (Sally = 2) is linked to another Person (Joe = 1).

这是容易的，我发现所有一个人的链接，如果我的外键是origin_id。但是，这也只能说明人们发起的链接。在我的情况，我需要看到所有的链接不管他们发起或由人接收。例如，如果我要问所有的Sally的链接（莎莉= 2），结果我想应该是：

It is easy for me to find all of a Persons Links if my foreign key was "origin_id". But this would only show People originating a Link. In my scenario I need to see all links regardless if they were originated or received by a Person. If for example I were to ask for all of Sally's links (Sally = 2), the result I would want would be:

Links
+----+-----------+---------+
| id | origin_id | rcvd_id |
+----+-----------+---------+
| 1  | 2         | 1       |
+----+-----------+---------+                        
| 3  | 3         | 2       |
+----+-----------+---------+                        

因此​​，我有2个索引键，无论是origin_id和rcvd_id。

Hence I have 2 index keys, both "origin_id" and "rcvd_id".

此可以得到解决的一种方法是用一个方法：

One way this could be solved is with a Method:

class Person < ActiveRecord::Base
  has_many  :link_origins,  :class_name => "Link", :foreign_key => :origin_id, :dependent => :destroy
  has_many  :link_rcvds,    :class_name => "Link", :foreign_key => :rcvd_id, :dependent => :destroy

def links
  origin_person + rcvd_person
end

然而，这是没有效率。例如，这需要整个集合被从数据库中收集，然后才做了PAGINATE方法的工作（我用的是will_paginate gem是项目），这违背了点作为PAGINATE应加快这一进程，通过限制记录拨通了电话。不限制记录后，已经完成整个集合。

However, this is not efficient. For example this requires the entire collection to be gathered from the database and only then does the paginate method work (I am using the will_paginate gem), which defeats the point as paginate should speed up the process by limiting the number of records called. Not limit the records after the entire collection is already done.

此外，上述不会允许我这样称呼，例如，Joe.links（：先）.origin_id.name。不完全是这样code，但意思我不能叫上一个图像中的origin_id的人的详细信息，如联系方法不知道origin_id是关系到人民表。

Also, the above will not allow me to call for example, Joe.links(:first).origin_id.name. Not exactly this code but meaning I could not call the Person details on the origin_id of a selected link, as the links method does not know that origin_id is related to the People table.

到目前为止最可行的解决办法似乎是：finder_sql

So far the most workable solution seems to be the :finder_sql.

class Person < ActiveRecord::Base
  has_many :links, :finder_sql => 'SELECT * FROM links WHERE (links.origin_id = #{id} or links.rcvd_id = #{id})'

这给所有环节，其中PERSON_ID匹配无论是Links.origin_id或Links.rcvd_id。

This gives all links where the Person_id matches either the Links.origin_id or the Links.rcvd_id.

此选项的一面，就是利用：finder_sql，排除了因为Rails的所有其他SQL修饰符 不知道如何分析和修改您提供的SQL。例如，我将无法使用：包括与选项：finder_sql

The down side of this option, is that using :finder_sql, rules out all the other sql modifiers since Rails doesn't know how to parse and modify the SQL you provide. For example I would not be able to use the :include option with the :finder_sql.

所以，现在我使用的是：finder_sql，解决方案。但似乎有可能得到这个协会，我不需要这样的方式做的远：finder_sql。例如，有没有办法写，同时保留了Rails的SQL修饰符的自定义的SQL字符串，Active Record的供应。

So, right now I am using the :finder_sql, solution. But it seems there might be a away of getting this association done in such a way that I don't need a :finder_sql. For example, is there a way to write a custom sql string while retaining the Rails sql modifiers that Active Record supplies.

以上任何想法？

推荐答案

我没有找到解决办法，但事实证明，我可能问错了问题。我还没有发现远有多个索引键，因为我没有问实施一些自定义的SQL打破不同轨道的帮手。

I did find the solution to this, however it turned out I was probably asking the wrong question. I have not found away to have multiple index keys as I asked without implementing some custom sql which breaks different rails helpers.

我想我的问题仍然有效，但我怎么没解决，这是看问题的方式不同。我刚刚创建的关联，因为它们是：

I guess my question still stands, but how I did resolve this was to look at the problem differently. I just created the associations as they are:

belongs_to :rcvd_person, :class_name => 'Person', :foreign_key => :rcvd_id
belongs_to :origin_person, :class_name => 'Person', :foreign_key => :origin_id

和一个自定义的SQL语句：

And a custom sql statement:

class Person...
  has_many :links, :finder_sql => 'SELECT * FROM links WHERE origin_id = #{id} OR rcvd_id = #{id}'
end

然后我就能够操纵我怎么想我查看记录。如果其他人在做类似的事情，我所做的：

Then I was able to manipulate the records how I wanted in my view. In case anyone else is doing something similar, I did:

<% person.links.each do |link| %> 

  <% if link.origin_id == person.id %>
    <%= link.rcvd_person.given_name %>
  <% else %>
    <%= link.origin_person.given_name %>
  <% end  %>

<% end %>

这篇关于导轨 - 多重索引键协会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！