Databases
StackOverflow2013
Postsdbo
POWhat to do when the foreign key is over-riden by the table it belongs to?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat to do when the foreign key is over-riden by the table it belongs to?
    primarykey
    data
    text
    <p>I apologize for the confused worded question and the potentially confusing explanation to follow, but, frankly, I'm confused.</p> <p>I have a <strong>contact_link</strong> model that <code>:belongs_to</code>, among other models, a <strong>person</strong> model. The <strong>person</strong> model <code>:has_many :events through =&gt; :contact_link</code>. </p> <p>The thing is the <strong>person</strong> model has its <code>:id</code> field (primary_key) and also a <code>:person_id</code> field (that sort of functions as a "public" key). </p> <p>The problem is when I try to construct a <strong>contact_link</strong>, rails, by convention, seeks to look in a <strong>contact_link.people</strong> link table for the <code>:id</code> associated with the <strong>person</strong> table in a column named <code>person_id</code>. It ends up using the <code>:person_id</code>, which isn't used for associations, thinking it's the <code>:id</code> field of the <strong>person</strong> table.</p> <p>Does this make sense? and if so, what can I do to get around it. It's work code so I can't change the schema. Here's my code:</p> <pre><code>class RecordOfContactImporter def self.import_data(contact_record_file) Rails.application.csv_impl.parse(contact_record_file, :headers =&gt; true, :header_converters =&gt; :symbol) do |row| next if row.header_row? if participant = Participant.where(:p_id =&gt; row[:participant_id]).first person = get_person_record(row) person.save! event = get_event_record(row, participant) event.save! contact = get_contact_record(row, event, person) contact.save! contact_link = get_contact_link_record(row, event, person, contact) p contact_link.valid? if contact_link.valid? contact_link.save! else # File.open(contact_link_import_error_log, 'a') {|f| f.write("[#{Time.now.to_s(:db)}] contact_link record invalid for - #{row} - #{contact_link.errors.map(&amp;:to_s)}\n") } end else # TODO: log row in missig participants file end end end def self.contact_link_import_error_log dir = "#{Rails.root}/log/contact_link_import_error_logs" FileUtils.makedirs(dir) unless File.exists?(dir) log_path = "#{dir}/#{Date.today.strftime('%Y%m%d')}_import_errors.log" File.open(log_path, 'w') {|f| f.write("[#{Time.now.to_s(:db)}] \n\n") } unless File.exists?(log_path) log_path end def self.get_person_record(row) person = Person.where(:person_id =&gt; row[:person_id]).first person = Person.new(:person_id =&gt; row[:person_id]) if person.blank? person.first_name = row[:person_first_name] unless row[:person_first_name].blank? person.last_name = row[:person_last_name] unless row[:person_last_name].blank? person end def self.get_event_record(row, participant) start_date = Date.strptime(row[:event_start_date], '%m/%d/%y') event = Event.where(:participant_id =&gt; participant.id, :event_type_code =&gt; row[:event_type], :event_start_date =&gt; start_date).first event = Event.new(:participant_id =&gt; participant.id, :event_type_code =&gt; row[:event_type], :event_start_date =&gt; start_date) if event.blank? event.participant = participant event.event_type_other = row[:event_type_other] unless row[:event_type_other].blank? event.disposition = row[:disposition] unless row[:disposition].blank? event.event_disposition_category_code = row[:event_disposition_category] unless row[:event_disposition_category].blank? event.event_start_time = row[:event_start_time] unless row[:event_start_time].blank? event.event_breakoff_code = row[:event_breakoff] unless row[:event_breakoff].blank? event.event_comment = row[:event_comment] unless row[:event_comment].blank? event end def self.get_contact_record(row, event, person) contact_date = Date.strptime(row[:contact_date], '%m/%d/%y') pre_existing_contact = nil ContactLink.where(:event_id =&gt; event.id, :person_id =&gt; person.id).all.each do |cl| contact = Contact.where(:id =&gt; cl.contact_id).first pre_existing_contact = contact if contact.contact_date_date == contact_date &amp;&amp; contact.contact_start_time == row[:contact_start_time] pre_existing_contact end contact = pre_existing_contact unless pre_existing_contact.nil? contact = Contact.new() if contact.blank? contact.psu_code = row[:psu_code] unless row[:psu_code].blank? contact.contact_disposition = row[:contact_disposition] unless row[:contact_disposition].blank? contact.contact_type_code = row[:contact_type] unless row[:contact_type].blank? contact.contact_type_other = row[:contact_type_pther] unless row[:contact_type_pther].blank? contact.contact_date = row[:contact_date] unless row[:contact_date].blank? contact.contact_start_time = row[:contact_start_time] unless row[:contact_start_time].blank? contact.contact_end_time = row[:contact_end_time] unless row[:contact_end_time].blank? contact.language_code = row[:language] unless row[:language].blank? contact.language_other = row[:language_other] unless row[:language_other].blank? contact.interpret_code = row[:interpret] unless row[:interpret].blank? contact.interpret_other = row[:interpret_other] unless row[:interpret_other].blank? contact.location_code = row[:location] unless row[:location].blank? contact.location_other = row[:location_other] unless row[:location_other].blank? contact.contact_private_code = row[:contact_private] unless row[:contact_private].blank? contact.who_contacted_code = row[:who_contacted] unless row[:who_contacted].blank? contact.contact_comment = row[:contact_comment] unless row[:contact_comment].blank? contact end def self.get_contact_link_record(row, event, person, contact) contact_link = ContactLink.where(:person_id =&gt; person.id, :event_id =&gt; event.id, :contact_id =&gt; contact.id).first contact_link = ContactLink.new(:person_id =&gt; person.id, :event_id =&gt; event.id, :contact_id =&gt; contact.id) #if contact_link.blank? populate_contact_link_attributes(contact_link, row) populate_contact_link_ncs_coded_attributes(contact_link, row) contact_link end def self.populate_contact_link_attributes(contact_link, row) [ :id, :psu_code, :contact_link_id, :contact_id, :event_id, :instrument_id, :staff_id, :person_id, :provider_id, :transaction_type ].each do |attribute| contact_link.send("#{attribute}=", row[attribute]) unless row[attribute].blank? end end def self.populate_contact_link_ncs_coded_attributes(pbs_list, row) [ :psu ].each do |attribute| contact_link.send("#{attribute}_code=", row[attribute]) unless row[attribute].blank? end end end </code></pre> <p>Here's the test code:</p> <pre><code># -*- coding: utf-8 -*- require 'spec_helper' module NcsNavigator::Core describe RecordOfContactImporter do context "uploading csv data" do describe ".import_data" do before(:each) do # create Participants and a particular participant for an exisiting record [11111111, 22222222, 11112222, 22221111].each { |id| Participant.create :p_id =&gt; id } participant = Participant.where(:p_id =&gt; '11111111').first # create a Person person = Person.create! :person_id =&gt; 11111111 # create an event associated with a the particular participant event = Event.new :event_type_code =&gt; 15, :event_start_date =&gt; '2012-04-10' event.participant = participant event.save! # create a contact with a colliding date and time contact = Contact.create!(:contact_date_date =&gt; Date.parse('2012-04-10'), :contact_start_time =&gt; '15:00') # create a contact link to bring the contact together with the person and event ContactLink.create!(:person =&gt; person, :event =&gt; event, :contact =&gt; contact, :staff_id =&gt; 1) end it "finds a person if one exists or creates one if it doesn't" do Person.count.should == 1 RecordOfContactImporter.import_data(File.open("#{Rails.root}/spec/fixtures/data/ROC_Code_lists_and_Dispositions.csv")) Person.count.should == 4 end it "finds an event if one exists or creates one if it doesn't" do Event.count.should == 1 RecordOfContactImporter.import_data(File.open("#{Rails.root}/spec/fixtures/data/ROC_Code_lists_and_Dispositions.csv")) Event.count.should == 4 end it "finds a contact if one exists or creates one if it doesn't" do Contact.count.should == 1 RecordOfContactImporter.import_data(File.open("#{Rails.root}/spec/fixtures/data/ROC_Code_lists_and_Dispositions.csv")) Contact.count.should == 4 end it "creates a ContactLink from the data" do ContactLink.count.should == 1 RecordOfContactImporter.import_data(File.open("#{Rails.root}/spec/fixtures/data/ROC_Code_lists_and_Dispositions.csv")) ContactLink.count.should == 4 end it "associates a ContactLink with a Person, Event, and Contact" do person = Person.first contact = Contact.first event = Event.first ContactLink.first.person.should == person end end end context ".get_person_record" do context "with an existing person record" do let(:person) { Factory(:person, :person_id =&gt; "bob") } it "finds the record by person_id" do row = get_first_data_row_from_csv("existing_person") person.person_id.should == row[:person_id] RecordOfContactImporter.get_person_record(row).should == person end it "sets the first name if given in row" do row = get_first_data_row_from_csv("existing_person") person.first_name.should == "Fred" person = RecordOfContactImporter.get_person_record(row) person.first_name.should == "Bobby" end it "does not update first name if the data in the row first name is blank" do row = get_first_data_row_from_csv("existing_person_with_blank_name") person.first_name.should == "Fred" person = RecordOfContactImporter.get_person_record(row) person.first_name.should == "Fred" end end context "without an existing person record" do before(:each) do @row = get_first_data_row_from_csv("existing_person_with_blank_name") end it "builds a new person record if no person exists" do person = RecordOfContactImporter.get_person_record(@row) person.class.should == Person end end end end end </code></pre> <p>Here's the rest of the code:</p> <p>Person</p> <pre><code># == Schema Information # Schema version: 20120607203203 # # Table name: people # # id :integer not null, primary key # psu_code :integer not null # person_id :string(36) not null # prefix_code :integer not null # first_name :string(30) # last_name :string(30) # middle_name :string(30) # maiden_name :string(30) # suffix_code :integer not null # title :string(5) # sex_code :integer not null # age :integer # age_range_code :integer not null # person_dob :string(10) # person_dob_date :date # deceased_code :integer not null # ethnic_group_code :integer not null # language_code :integer not null # language_other :string(255) # marital_status_code :integer not null # marital_status_other :string(255) # preferred_contact_method_code :integer not null # preferred_contact_method_other :string(255) # planned_move_code :integer not null # move_info_code :integer not null # when_move_code :integer not null # date_move_date :date # date_move :string(7) # p_tracing_code :integer not null # p_info_source_code :integer not null # p_info_source_other :string(255) # p_info_date :date # p_info_update :date # person_comment :text # transaction_type :string(36) # created_at :datetime # updated_at :datetime # being_processed :boolean # response_set_id :integer # # -*- coding: utf-8 -*- # A Person is an individual who may provide information on a participant. # All individuals contacted are Persons, including those who may also be Participants. require 'ncs_navigator/configuration' class Person &lt; ActiveRecord::Base include MdesRecord acts_as_mdes_record :public_id_field =&gt; :person_id, :date_fields =&gt; [:date_move, :person_dob] ncs_coded_attribute :psu, 'PSU_CL1' ncs_coded_attribute :prefix, 'NAME_PREFIX_CL1' ncs_coded_attribute :suffix, 'NAME_SUFFIX_CL1' ncs_coded_attribute :sex, 'GENDER_CL1' ncs_coded_attribute :age_range, 'AGE_RANGE_CL1' ncs_coded_attribute :deceased, 'CONFIRM_TYPE_CL2' ncs_coded_attribute :ethnic_group, 'ETHNICITY_CL1' ncs_coded_attribute :language, 'LANGUAGE_CL2' ncs_coded_attribute :marital_status, 'MARITAL_STATUS_CL1' ncs_coded_attribute :preferred_contact_method, 'CONTACT_TYPE_CL1' ncs_coded_attribute :planned_move, 'CONFIRM_TYPE_CL1' ncs_coded_attribute :move_info, 'MOVING_PLAN_CL1' ncs_coded_attribute :when_move, 'CONFIRM_TYPE_CL4' ncs_coded_attribute :p_tracing, 'CONFIRM_TYPE_CL2' ncs_coded_attribute :p_info_source, 'INFORMATION_SOURCE_CL4' belongs_to :response_set # surveyor has_many :response_sets, :class_name =&gt; "ResponseSet", :foreign_key =&gt; "user_id" has_many :contact_links, :order =&gt; "created_at DESC" has_many :instruments, :through =&gt; :contact_links has_many :events, :through =&gt; :contact_links has_many :addresses has_many :telephones has_many :emails has_many :household_person_links has_many :household_units, :through =&gt; :household_person_links has_many :participant_person_links has_many :participants, :through =&gt; :participant_person_links # validates_presence_of :first_name # validates_presence_of :last_name validates_length_of :title, :maximum =&gt; 5, :allow_blank =&gt; true accepts_nested_attributes_for :addresses, :allow_destroy =&gt; true accepts_nested_attributes_for :telephones, :allow_destroy =&gt; true accepts_nested_attributes_for :emails, :allow_destroy =&gt; true before_save do self.age = self.computed_age if self.age.blank? end ## # How to format the date_move attribute # cf. MdesRecord # @return [String] def date_move_formatter '%Y-%m' end ## # Determine the age of the Person based on the date of birth # @return [Integer] def computed_age return nil if dob.blank? now = Time.now.utc.to_date offset = ((now.month &gt; dob.month || (now.month == dob.month &amp;&amp; now.day &gt;= dob.day)) ? 0 : 1) now.year - dob.year - offset end ## # Display text from the NcsCode list GENDER_CL1 # cf. sex belongs_to association # @return [String] def gender sex.to_s end ## # Override to_s to return the full name of the Person # aliased as :name and :full_name # @return [String] def to_s "#{first_name} #{last_name}".strip end alias :name :to_s alias :full_name :to_s ## # Helper method to set first and last name from full name # Sets first name if there is no space in the name # @param [String] def full_name=(full_name) full_name = full_name.split if full_name.size &gt;= 2 self.last_name = full_name.last self.first_name = full_name[0, (full_name.size - 1) ].join(" ") else self.first_name = full_name end end ## # Override default setter to also set Date value for use in calculations def person_dob=(dob) self[:person_dob] = dob begin self.person_dob_date = Date.parse(dob) rescue # Date entered is unparseable end end def self_link participant_person_links.detect { |ppl| ppl.relationship_code == 1 } end private :self_link ## # The participant record associated with this person if any whose # relationship is self def participant self_link.try(:participant) end ## # Create or update the participant record associated with this person whose # relationship is self def participant=(participant) ppl = self_link if ppl ppl.participant = participant else participant_person_links.build(:relationship_code =&gt; 1, :person =&gt; self, :participant =&gt; participant, :psu =&gt; self.psu) end end ## # A Person is a Participant if there is an association on the participant table # @return [Boolean] def participant? !participant.nil? end ## # The Participant ppg_status local_code (cf. NcsCode) if applicable # @return [Integer] def ppg_status participant.ppg_status.local_code if participant &amp;&amp; participant.ppg_status end ## # Based on the current state, pregnancy probability group, and # the intensity group (hi/lo) determine the next event # cf. Participant.upcoming_events # @return [String] def upcoming_events events = [] if participant? participant.upcoming_events.each { |e| events &lt;&lt; e } else events &lt;&lt; "Pregnancy Screener" end events end ## # Builds a new ResponseSet for the Person associated with the given Survey # and pre-populates questions to which we have previous data. # # @param [Survey] # @return [ResponseSet] def start_instrument(survey) # TODO: raise Exception if survey is nil return if survey.nil? build_instrument(survey).tap do |instr| instr.build_response_set(:survey =&gt; survey, :user_id =&gt; self.id) prepopulate_response_set(instr.response_set, survey) end end def prepopulate_response_set(response_set, survey) # TODO: determine way to know about initializing data for each survey reference_identifiers = ["prepopulated_name", "prepopulated_date_of_birth", "prepopulated_ppg_status", "prepopulated_local_sc", "prepopulated_sc_phone_number", "prepopulated_baby_name", "prepopulated_childs_birth_date"] response_type = "string_value" reference_identifiers.each do |reference_identifier| question = nil survey.sections_with_questions.each do |section| section.questions.each do |q| question = q if q.reference_identifier == reference_identifier break unless question.nil? end break unless question.nil? end if question answer = question.answers.first value = case reference_identifier when "prepopulated_name" response_type = "string_value" name when "prepopulated_date_of_birth" response_type = "string_value" dob.to_s when "prepopulated_ppg_status" response_type = "integer_value" ppg_status when "prepopulated_local_sc" response_type = "string_value" NcsNavigatorCore.study_center_name when "prepopulated_sc_phone_number" response_type = "string_value" NcsNavigatorCore.study_center_phone_number else # TODO: handle other prepopulated fields nil end response_set.responses.build(:question =&gt; question, :answer =&gt; answer, response_type.to_sym =&gt; value) end end response_set end ## # Returns the number of times (0 based) this instrument has been taken for the given survey # @param [Survey] # @return [Fixnum] def instrument_repeat_key(survey) response_sets_for_survey = response_sets.select { |rs| rs.survey.title == survey.title } response_sets_for_survey.blank? ? 0 : response_sets_for_survey.size - 1 end ## # Return the currently active ContactLink, if a person is associated with a Contact through # a ContactLink and that ContactLink has not been closed (cf. Event#closed? and Contact#closed?) # # @return [ContactLink] def current_contact_link open_contact_links = contact_links.select { |link| !link.complete? } return nil if open_contact_links.blank? return open_contact_links.first if open_contact_links.size == 1 # TODO: what to do if there is more than one open contact? end ## # Create a new Instrument for the Person associated with the given Survey. # # @param [Survey] # @return [ResponseSet] def build_instrument(survey) Instrument.new(:psu_code =&gt; NcsNavigatorCore.psu, :instrument_version =&gt; Instrument.determine_version(survey.title), :instrument_type =&gt; InstrumentEventMap.instrument_type(survey.title), :person =&gt; self, :survey =&gt; survey) end ## # Determine if this Person has started this Survey # @param [Survey] # @return [true, false] def started_survey(survey) ResponseSet.where(:survey_id =&gt; survey.id).where(:user_id =&gt; self.id).count &gt; 0 end ## # Get the most recent instrument for this survey # @param [Survey] # @return [Instrument] def instrument_for(survey) ins = Instrument.where(:survey_id =&gt; survey.id).where(:person_id =&gt; self.id).order("created_at DESC") ins.first end ## # Convenience method to get the last incomplete response set # @return [ResponseSet] def last_incomplete_response_set rs = response_sets.last rs.blank? ? nil : (rs.complete? ? nil : rs) end ## # Convenience method to get the last completed survey # @return [ResponseSet] def last_completed_survey rs = response_sets.last rs.complete? ? rs.survey : nil end ## # Given a data export identifier, return the responses this person made for that question # @return [Array&lt;Response&gt;] def responses_for(data_export_identifier) Response.includes([:answer, :question, :response_set]).where( "response_sets.user_id = ? AND questions.data_export_identifier = ?", self.id, data_export_identifier).all end ## # Returns all DwellingUnits associated with the person's household units # @return[Array&lt;DwellingUnit] def dwelling_units household_units.collect(&amp;:dwelling_units).flatten end ## # Returns true if a dwelling_unit has a tsu_is and this person has an association to the # tsu dwelling unit through their household # @return [true,false] def in_tsu? dwelling_units.map(&amp;:tsu_id).compact.size &gt; 0 end ## # Returns the primary cell phone number for this person, or nil if no such # phone record exists. def primary_cell_phone cell_code = Telephone.cell_phone_type.to_i primary_contacts(telephones, :phone_rank_code) do |ts| ts.detect { |t| t.phone_type_code == cell_code } end end ## # Returns the primary cell phone number for this person, or nil if no such # phone record exists. def primary_home_phone home_code = Telephone.home_phone_type.to_i primary_contacts(telephones, :phone_rank_code) do |ts| ts.detect { |t| t.phone_type_code == home_code } end end ## # Returns the primary address for this person, or nil if no such address # record exists. def primary_address primary_contacts(addresses, :address_rank_code, &amp;:first) end ## # Returns the primary email for this person, or nil if no such email record # exists. def primary_email primary_contacts(emails, :email_rank_code, &amp;:first) end ## # @private def primary_contacts(collection, code_key) yield collection.select { |c| c.send(code_key) == 1 } end private def dob return person_dob_date unless person_dob_date.blank? return Date.parse(person_dob) if person_dob.to_i &gt; 0 &amp;&amp; !person_dob.blank? &amp;&amp; (person_dob !~ /^9/ &amp;&amp; person_dob !~ /-9/) return nil end end class PersonResponse attr_accessor :response_class, :text, :short_text, :reference_identifier attr_accessor :datetime_value, :integer_value, :float_value, :text_value, :string_value end </code></pre> <p>Contact Link</p> <pre><code># -*- coding: utf-8 -*- # == Schema Information # Schema version: 20120607203203 # # Table name: contact_links # # id :integer not null, primary key # psu_code :integer not null # contact_link_id :string(36) not null # contact_id :integer not null # event_id :integer # instrument_id :integer # staff_id :string(36) not null # person_id :integer # provider_id :integer # transaction_type :string(255) # created_at :datetime # updated_at :datetime # # -*- coding: utf-8 -*- # Each Contact Link record associates a unique combination # of Staff Member, Person, Event, and/or Instrument that occurs during a Contact.  # There should be at least 1 contact link record for every contact. class ContactLink &lt; ActiveRecord::Base include MdesRecord acts_as_mdes_record :public_id_field =&gt; :contact_link_id ncs_coded_attribute :psu, 'PSU_CL1' belongs_to :contact belongs_to :person belongs_to :event belongs_to :instrument, :inverse_of =&gt; :contact_link belongs_to :provider # belongs_to :staff # references public_id of staff in ncs_staff_portal delegate :participant, :to =&gt; :event # Validating :contact_id instead of :contact prevents a reload of # the associated contact object when creating a contact link # alone. This provides a huge speedup in the importer; if validating # :contact is necessary, we should provide a scoped validation so it # can be </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsteve_gallagher
    1. USsteve_gallagher
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThat seems strange - Rails should by convention look to match the `:person_id` attribute on the `ContactLink` record with the `:id` of a `Person` record. How are you constructing a `ContactLink` record?
      singulars
      1. POWhat to do when the foreign key is over-riden by the table it belongs to?
      1. USRob d'Apice
    2. COCan you post the actual code for the `Person` and `ContactLink` models?
      singulars
      1. POWhat to do when the foreign key is over-riden by the table it belongs to?
      1. USBrandan
    3. CO@Robd'Apice That's what I thought but I found this: "Foreign Key The foreign key is named with the singular version of the target table name with _id appended to it, e.g. order_id in the items table where we have items linked to the orders table." So it would make sense that its targeting the :person_id field
      singulars
      1. POWhat to do when the foreign key is over-riden by the table it belongs to?
      1. USsteve_gallagher
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload