Topic: dual foreing keys

I have a table of Users.  In the table are employees and clients (all of them can logon to the website).  I also have a table called requests.  The request has a foreign key to the users table to link who made the request (a client in the user table).

I would like to have a second foreign key in the request table that links to the users table for the employee the request is assigned to.

Is that possible?  Or do I need to rethink this and have a separate table for employees?

Thanks

Re: dual foreing keys

I think this could be done in one model.

class Request < ActiveRecord::Base
 belongs_to :users # foreign_key "user_id"
 belongs_to :employees, :class_name => "User" # foreign_key "employee_id"
end

Re: dual foreing keys

How about this: a users table, an employees table, a clients table, and a requests table.

Table users:
id  :integer

Table employees:
id  :integer
user_id  :integer (foreign key)

Table clients:
id  :integer
user_id  :integer (foreign key)

Table requests:
id  :integer
employee_id  :integer (foreign key)
class Employee < ActiveRecord::Base
  belongs_to :user
  has_many: requests
end

class Client < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
  has_one :employee
  has_one :client
end

class Requests < ActiveRecord::Base
  belongs_to :employee
end

Re: dual foreing keys

Thanks for the help.

If I add the Employees table and the Clients table. 
And each has a foreign key to the Users table.
Then the Request table has a foreign key to the Employee table and the employee table has a foreign key to the Users table.

How would I display the employee's first_name (from the Users table) in a list of Requests?
Logic - use request.employee_id to find the employee, then use employee.user_id to find the user and his first name.
Currently, it's only one step:
<%= User.find(request.user_id).first_name %>

Re: dual foreing keys

olhor wrote:

I think this could be done in one model.

class Request < ActiveRecord::Base
 belongs_to :users # foreign_key "user_id"
 belongs_to :employees, :class_name => "User" # foreign_key "employee_id"
end

If I did it this way, wouldn't it need to be like this or does the class do the same thing?

class Request < ActiveRecord::Base
 belongs_to :users # foreign_key "user_id"
 belongs_to :users # foreign_key "employee_id"
end

Re: dual foreing keys

reddirt wrote:

How would I display the employee's first_name (from the Users table) in a list of Requests?
Logic - use request.employee_id to find the employee, then use employee.user_id to find the user and his first name.
Currently, it's only one step:
<%= User.find(request.user_id).first_name %>

I think all you would need to do is add a has_many :through to the User class I posted above, so that it looks like this:

class User < ActiveRecord::Base
  has_one :employee
  has_one :client
  has_many :requests, :through => :employee 
end

Re: dual foreing keys

First of all I would like to correct myself. Did not know why I used plural form and forgot about clients tongue... maybe tired...
Should be:

class Request < ActiveRecord::Base
  belongs_to :client, :class_name => "User" # foreign_key "client_id"
  belongs_to :employee, :class_name => "User" # foreign_key "employee_id"
end

The User model could look like this:

class User < ActiveRecord::Base
  has_many :requests_for_me, :class_name => "Request", :association_foreign_key => "employee_id" # if user is an 'employee'
  has_many :my_requests, :class_name => "Request", :association_foreign_key => "client_id" # if user is a 'client'
end

... and finding first name of employee:

Request.all.each do |request|
  User.find(request.employee_id).first_name
end

or even better:

Request.all.each do |request|
  request.employee.first_name
end

Last edited by olhor (2012-04-20 15:35:03)