Join with ActiveRecord using non-standard schema

Ruby
1

Hi,

I’m pretty new with Ruby and have been struggling for a while how to
make work a join between tables 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
existing data for testing purposes, that’s not an option.

Tables:

  • AGREEMENTS
  • AGREEMENT_TYPES

Both tables contain a column ‘AGREEMENT_TYPE_ID’ that could be used for
the join. It is also the primary key for the ‘AGREEMENT_TYPES’ table.

This is the models definition:

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

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

What I wanted to achive is to consume a field called ‘name’ from the
AGREEMENT_TYPES table. This is the instantiation:

puts Agreements.joins(:Agreement_Types).explain

EXPLAIN for: SELECT “AGREEMENTS”.* FROM “AGREEMENTS” INNER JOIN
“AGREEMENT_TYPES” ON “AGREEMENT_TYPES”.“AGREEMENT_TYPE_ID” =
“AGREEMENTS”.“AGREEMENT_ID”

Using include also seems to list only the AGREMENTS fields:

@agr = Agreement.find(:all, :include => :AgreementType)

And it does not let me access the ‘name’ field:

irb(main):081:0> puts @agr.name
NoMethodError: undefined method name' for #<Array:0x1cfb258> from (irb):81 from C:/tools/Ruby193/bin/irb:12:in’

Any help will be appreaciated.

Thanks.

2

On Mon, Aug 6, 2012 at 5:19 AM, Tedi R. [email protected] wrote:

@agr = Agreement.find(:all, :include => :AgreementType)

And it does not let me access the ‘name’ field:

irb(main):081:0> puts @agr.name
NoMethodError: undefined method `name’ for #Array:0x1cfb258

There’s a reason to use meaningful names :slight_smile:

In this case your variable should be @agreements (plural) because
an ActiveRecord find all returns an array, which of course has no
‘name’ method though e.g. @agreements.first.name should work.

Note that the error message is telling you exactly that :slight_smile:

HTH,

3

Tried with this:

@agreements = Agreement.find(:all, :include => :AgreementType)
@agreements.first.name

Which returned:
NoMethodError: undefined method name' for #<Agreement:0x1571ac0> from C:/tools/Ruby193/lib/ruby/gems/1.9.1/gems/activemodel-3.2.7/lib/active_model/attribute_methods.rb:407:inmethod_missing’
from
C:/tools/Ruby193/lib/ruby/gems/1.9.1/gems/activerecord-3.2.7/lib/active_record/attribute_methods.rb:149:in
method_missing' from (irb):25 from C:/tools/Ruby193/bin/irb:12:in’

But if I try

@agreements.first.agreement_id U(which is a field on the Agreements
table), it works correctly.

4

On Mon, Aug 6, 2012 at 7:39 AM, T- Di [email protected] wrote:

@agreements = Agreement.find(:all, :include => :AgreementType)
@agreements.first.name

Which returned:
NoMethodError: undefined method `name’ for #Agreement:0x1571ac0

So why did you think “@agr.name” from your first example would do
anything useful if Agreement doesn’t have a name method?

Or is it AgreementType that has a name and that’s what you wanted?

5

On Mon, Aug 6, 2012 at 10:39 AM, T- Di [email protected] wrote:

@agreements.first.name

Which returned:
NoMethodError: undefined method `name’ for #Agreement:0x1571ac0

Are you sure there’s a name method on Agreement? It’s telling you there
isn’t.

-Dave

6

On Mon, Aug 6, 2012 at 8:38 AM, T- Di [email protected] wrote:

The table AgreementType contains the field ‘name’ that I want to use.

Quick recap from the first message:

  • There are 2 tables for which I created individual classes:
    a. Agreement
    b. AgreementType
  • The field ‘AGREEMENT_TYPE_ID’ is present in both tables, so I can use
    it to join their contents.
  • The field ‘name’ is on the AGREEMENT_TYPE_ID

OK, so it’s not a method of Agreement. Then you’re looking for e.g.
@agreements.first.agreement_type.name

Maybe worth reviewing the Rails guide on AR relationships…

7

Hassan S. wrote in post #1071445:

On Mon, Aug 6, 2012 at 8:38 AM, T- Di [email protected] wrote:

The table AgreementType contains the field ‘name’ that I want to use.

Quick recap from the first message:

  • There are 2 tables for which I created individual classes:
    a. Agreement
    b. AgreementType
  • The field ‘AGREEMENT_TYPE_ID’ is present in both tables, so I can use
    it to join their contents.
  • The field ‘name’ is on the AGREEMENT_TYPE_ID

OK, so it’s not a method of Agreement. Then you’re looking for e.g.
@agreements.first.agreement_type.name

Maybe worth reviewing the Rails guide on AR relationships…

That’s what I was trying to explain from the begining… :slight_smile:

@agreements.first.agreement_type.name
returns: NoMethodError: undefined method `agreement_type’ for
#Agreement:0x1571ac0

@agreements.first.AgreementType.name
returns: NoMethodError: undefined method `name’ for nil:NilClass

8

Hassan S. wrote in post #1071438:

On Mon, Aug 6, 2012 at 7:39 AM, T- Di [email protected] wrote:

@agreements = Agreement.find(:all, :include => :AgreementType)
@agreements.first.name

Which returned:
NoMethodError: undefined method `name’ for #Agreement:0x1571ac0

So why did you think “@agr.name” from your first example would do
anything useful if Agreement doesn’t have a name method?

Or is it AgreementType that has a name and that’s what you wanted?

The table AgreementType contains the field ‘name’ that I want to use.

Quick recap from the first message:

  • There are 2 tables for which I created individual classes:
    a. Agreement
    b. AgreementType
  • The field ‘AGREEMENT_TYPE_ID’ is present in both tables, so I can use
    it to join their contents.
  • The field ‘name’ is on the AGREEMENT_TYPE_ID
9

This is probably not gonna be helpful, but… have you tried using an
ORM that isn’t inherently broken, for example Sequel? I’ve used it
once for interfacing with external database and had no problems
setting up a join across eight tables with variously named fields. I
can post bits of my code if you wish.

– Matma R.

10

@Hassan I thought I should use AgreementType in the has_one clause as
this is how I’m naming the class I’m refering to. I’m a bit lost now.

@Matma, thanks for the advice. I’m gonna give it a try a couple more
hours and if I don’t progress I will go back to Sequel, which is was I
was using before. Just wanted to take the oportunity to learn a bit
about AR, but it’s maybe worth doing so when creating a new schema.

11

On Mon, Aug 6, 2012 at 9:09 AM, T- Di [email protected] wrote:

That’s what I was trying to explain from the begining… :slight_smile:

Well, until the obvious errors are removed from your code, there’s not
going to be much progress :slight_smile:

Which (referring back to your original post) includes things like e.g.

has_one :AgreementType <= should be has_one :agreement_type

It’s perfectly possible to work with a non-standard schema, but you
need to have the basic syntax right…

12

Finally got it working.

Couple of notes for the newbies like me trying to learn:

  • ClassName is translated to class_name in ActiveRecord.
  • On each class I had to add also the primary_key to make it work.

So here are the corrected models:

class AgreementType < ActiveRecord::Base
self.table_name = ‘AGREEMENT_TYPES’
self.primary_key = ‘AGREEMENT_TYPE_ID’
belongs_to :agreement, :primary_key => :agreement_type_id,
:foreign_key => :agreement_type_id
end

class Agreement < ActiveRecord::Base
self.table_name = ‘AGREEMENTS’
self.primary_key = ‘AGREEMENT_ID’
has_one :agreement_type, :primary_key => :agreement_type_id,
:foreign_key => :agreement_type_id
end

13

On Tue, Aug 7, 2012 at 2:31 AM, T- Di [email protected] wrote:

@Hassan I thought I should use AgreementType in the has_one clause as
this is how I’m naming the class I’m refering to. I’m a bit lost now.

Please read the Rails guide for ActiveRecord relationships,specifically
the “has_one”/“belongs_to” examples. Look at which one of those is
applied to the model that includes the foreign key. And of course look
at the syntax of the entries.

AR will handle your schema fine, if you set up your models properly.