2

I have a database structure I am working with, where a Node relates to one of two possible fields in the Interviewees (either clientnode_id OR caregivernode_id):

tbl_nodes
+----+-----------+
| id | node_Name |
+----+-----------+
|  5 | some name |
+----+-----------+

tbl_interviewees
+----+---------------+------------------+
| id | clientnode_id | caregivernode_id |
+----+---------------+------------------+
|  1 |             5 |             NULL |
+----+---------------+------------------+

I am simply trying to define a has_many in the Node model where matches from either field are included. The equivalent SQL would be: 

SELECT * FROM tbl_nodes LEFT JOIN tbl_interviewees ON (tbl_nodes.id=tbl_interviewees.clientnode_id OR tbl_nodes.id=tbl_interviewees.caregivernode_id)

I have currently attempted the advice on the following links to no avail (most use deprecated syntax):

Rails 3: Use of lambda with scopes in model

Rails has_many with dynamic conditions

http://guides.rubyonrails.org/association_basics.html#belongs-to-association-reference Rails has_many conditions

Probably just something fiddly with the syntax, so any help would be appreciated. I know this is wrong but this is my most recent attempt:

class Node < ActiveRecord::Base
  self.table_name_prefix = :tbl_
  has_many :interviewees, -> { where("clientnode_id=? OR caregivernode_id=?", self.id, self.id) }
end
Community
  • 1
  • 1
musicman3569
  • 91
  • 9

2 Answers2

1

Try this

@nodes = Node.joins(:interviewees).where("interviewees.clientnode_id=:node_id OR interviewees.caregivernode_id=:node_id", node_id: <your id>)

By scope, write scope with parameter in the model

class Node < ActiveRecord::Base
  has_many :interviewees
  scope :interviews_by_node, ->(node_id) { joins(:interviewees).where("interviewees.clientnode_id=:node_id OR interviewees.caregivernode_id=:node_id", node_id: node_id)
end

Call the scope in the controller action

class NodesController < ApplicationController

  def index
    @nodes = Node.interviews_by_node(5)  
  end

end

I hope this would be helpful.

Nitin Srivastava
  • 1,414
  • 7
  • 12
0

@Nitin Thanks! I was able to tweak your idea to get the behavior I wanted as follows:

class Node < ActiveRecord::Base
  # replaces using a "has_many :interviewees" since it is joined off 2 different fields
  def interviewees
    Interviewee.by_node(self.id)
  end
end

class Interviewee < ActiveRecord::Base
  # called by Node model to get Interviewee association
  scope :by_node, ->(node_id) { where("clientnode_id=? OR caregivernode_id=?", node_id, node_id) }

  # replaces "belongs_to :node" since it is joined off 2 different  fields
  def node
    Node.where("id = ? OR id = ?", self.clientnode_id, self.caregivernode_id)
  end
end

I feel like doing it this way helps keep the encapsulation a little better, since Node shouldn't have to know the specific fields that Interviewees has.

This allows me to use the same .node and .interviewees method names and keep convention. Cheers!

musicman3569
  • 91
  • 9