Join with ActiveRecord using non-standard schema

ruby-talk
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 `<main>'

Any help will be appreaciated.

Thanks.

···

--
Posted via http://www.ruby-forum.com/.

2

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,

···

On Mon, Aug 6, 2012 at 5:19 AM, Tedi Roca <lists@ruby-forum.com> 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>

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com

twitter: @hassan

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:in
`method_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 `<main>'

But if I try

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

···

--
Posted via http://www.ruby-forum.com/.

4

@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.

···

--
Posted via http://www.ruby-forum.com/.

5

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

···

--
Posted via http://www.ruby-forum.com/.

6

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?

···

On Mon, Aug 6, 2012 at 7:39 AM, T- Di <lists@ruby-forum.com> wrote:

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

Which returned:
NoMethodError: undefined method `name' for #<Agreement:0x1571ac0>

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com

twitter: @hassan

7

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

-Dave

···

On Mon, Aug 6, 2012 at 10:39 AM, T- Di <lists@ruby-forum.com> wrote:

@agreements.first.name

Which returned:
NoMethodError: undefined method `name' for #<Agreement:0x1571ac0>

--
Dave Aronson, Cleared/Remote Ruby on Rails Freelancer
(NoVa/DC/Remote); see www.DaveAronson.com, and blogs at
www.Codosaur.us, www.Dare2XL.com, www.RecruitingRants.com

8

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.

···

On Tue, Aug 7, 2012 at 2:31 AM, T- Di <lists@ruby-forum.com> 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.

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com

twitter: @hassan

9

Hassan Schroeder wrote in post #1071438:

@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

···

On Mon, Aug 6, 2012 at 7:39 AM, T- Di <lists@ruby-forum.com> wrote:

--
Posted via http://www.ruby-forum.com/\.

10

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...

···

On Mon, Aug 6, 2012 at 8:38 AM, T- Di <lists@ruby-forum.com> 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

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com

twitter: @hassan

11

Hassan Schroeder wrote in post #1071445:

···

On Mon, Aug 6, 2012 at 8:38 AM, T- Di <lists@ruby-forum.com> 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

--
Posted via http://www.ruby-forum.com/\.

12

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 Rex

13

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...

···

On Mon, Aug 6, 2012 at 9:09 AM, T- Di <lists@ruby-forum.com> wrote:

That's what I was trying to explain from the begining... :slight_smile:

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com

twitter: @hassan