There seem to be several ways to handle multiple foreign key associations. Each path I went to has its back spins, and since I am new to Rails, I am convinced that others are faced with a similar scenario, and I am probably working on something that has been resolved a long time ago.
My question is:
What would be an efficient way to handle multiple index key association while retaining all other Rails sql modifiers (such as: include, etc.)?
My scenario:
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 | +----+-----------+---------+
From line 1 of the above link table, it can be seen that Person (Sally = 2) is associated with another Person (Joe = 1).
It’s easy for me to find all the links for people if my foreign key was “origin_id”. But that would only mean "People Getting Connected." In my script, I need to see all the links, regardless of whether they were created or received by the Person. If, for example, I would ask for all of Sally's references (Sally = 2), the result I would like would be the following:
Links +----+-----------+---------+ | id | origin_id | rcvd_id | +----+-----------+---------+ | 1 | 2 | 1 | +----+-----------+---------+ | 3 | 3 | 2 | +----+-----------+---------+
Therefore, I have 2 index keys, both "origin_id" and "rcvd_id".
One way to solve this problem is to use the 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
However, this is inefficient. For example, this requires collecting the entire collection from the database, and only then does the paginate method work (I use the will_paginate pearl), which defeats the point, since paginate speeds up the process by limiting the number of named records. Do not limit your entries after completing the entire collection.
In addition, the above will not allow me to call, for example, Joe.links (: first) .origin_id.name. Not exactly this code, but it means that I could not call Person data about the origin_id of the selected link, because the link method does not know that origin_id is associated with the People table.
So far, the most workable solution has been: finder_sql.
class Person < ActiveRecord::Base has_many :links, :finder_sql => 'SELECT * FROM links WHERE (links.origin_id = #{id} or links.rcvd_id = #{id})'
This gives all links where Person_id matches either Links.origin_id or Links.rcvd_id.
The downside of this option is that the use of: finder_sql excludes all other sql modifiers with Rails does not know how to parse and modify the SQL that you provide. For example, I could not use the include: with: finder_sql parameter.
So right now I'm using a solution: finder_sql. But it seems that it may be that this association cannot be made in such a way that I do not need: finder_sql. For example, is there a way to write a custom sql string by storing the sql Rails modifiers that provide Active Record.
Any ideas on the above?