Join with ActiveRecord using legay database

[troca]

Hello,

I'm struggling to make a join between tables work on a non-standard schema. I know the proper way of doing it would be migrating the schema and using the ActiveRecord convention but as long as I'm using this to consume data for testing purposes, that's not an option.

The join can be made using the key 'AGREEMENT_TYPE_ID' that exists in both tables.

I have the following in the models definition:

class Agreement < ActiveRecord::Base
    self.table_name = 'AGREEMENTS'
    self.primary_key = 'AGREEMENT_ID'
    has_one :AgreementType, 
    :foreign_key => 'AGREEMENT_TYPE_ID'
end 

class AgreementType < ActiveRecord::Base
    self.table_name = 'AGREEMENT_TYPES'
    self.primary_key = 'AGREEMENT_TYPE_ID'
    belongs_to :Agreement, 
    :foreign_key => 'AGREEMENT_TYPE_ID'
end

I'm trying to access the contents of the field 'name' from the 'AGREEMENT_TYPE_ID' table as follows:

irb(main):081:0> @output = Agreement.first.AgreementType.name
NoMethodError: undefined method `name' for nil:NilClass
        from (irb):81
        from C:/tools/Ruby193/bin/irb:12:in `<main>'

Any clues?

Thanks

[Taywin]

Hmm... I am not sure what you are doing here. Are you trying to associate the table as 1 to 1?

When you use association 'has_one' and 'belongs_to', the table which use the association 'has_one' does not need to have a field of the other's ID, but the one with 'belongs_to' needs to have a field of ID associated to the other. Do you have your current table schema?

In your case, the assignments should not need any field associated with the assignment_types table, but the assignment_types table should have a field called 'assignment_id' (or could be different but then you need to adjust it in the model). Your current problem is the database design field name because it has overlapsed name. What you could do without migration is to reverse it.

# contains assignment_type_id field
class Agreement < ActiveRecord::Base
  belongs_to :agreement_type  # should not use capitalize for calling
  ...
end

# ignore assignment_type_id field 
class AgreementType < ActiveRecord::Base
  has_one :agreement
  ...
end

# and when you call for it
@output = Assignment.find(a_id)  # retrieve only 1 record
puts @output.assignment_type.name  # assume that the record has assignment type

If not, you should do it the right way (migrate) and the association could be...

# does not have assignment_type_id field
class Agreement < ActiveRecord::Base
  has_one :agreement_type  # should not use capitalize for calling
  ...
end

# should have assignment_id field in the table
class AgreementType < ActiveRecord::Base
  belongs_to :agreement  # if the assignment_id does not exists, need to add
                         # :foreign_key to it
  ...
end

# and when you call for it
@output = Assignment.find(a_id)  # retrieve only 1 record
puts @output.assignment_type.name  # assume that the record has assignment type

[troca]

Thanks Taywin