Multiple databases and establishing associations

rubyonrails-talk
1

Hi,

Rails 1.2.6

I've got two models from two different databases that I wish to associate if possible. The association is not based on a primary key for either model, but on a "job_number" field that both tables have (which is unique in both tables). Here's what I tried:

require 'active_record'

class Job < ActiveRecord::Base    establish_connection(:adapter => "oracle", ...)

   self.table_name = 'job'    self.primary_key = 'job_id'

   has_one :schedule, :foreign_key => :job_number end

class Schedule < ActiveRecord::Base    establish_connection(:adapter => "postgresql", ...)

   belongs_to :job, :foreign_key => :job_number end

j = Job.find_by_job_number('12345A') j.schedule => nil # ???

# Also tried: s = Schedule.find_by_job_number('12345A') s.job => nil # ???

No error, but the association should definitely not be nil.

What's the proper way to do what I'm trying? On a side note, how can I inspect the sql that AR is generating?

Thanks,

Dan

2

I've got two models from two different databases that I wish to associate if possible. The association is not based on a primary key for either model, but on a "job_number" field that both tables have (which is unique in both tables). Here's what I tried:

I'm no expert on this one, but considering Rails isn't very excited about working with more than one DB to start with (no way to declare multiple DBs in it's own config system), I can't imagine it doing associations across databases for you -- let alone ones with completely different RDBMS engines.

I would really expect you'll have to build the composition of the object yourself.

how can I inspect the sql that AR is generating?

Look at the file /log/development.log

-- gw

3

> I've got two models from two different databases that I wish to > associate if possible. The association is not based on a primary key > for either model, but on a "job_number" field that both tables have > (which is unique in both tables). Here's what I tried:

I'm no expert on this one, but considering Rails isn't very excited
about working with more than one DB to start with (no way to declare
multiple DBs in it's own config system), I can't imagine it doing
associations across databases for you -- let alone ones with
completely different RDBMS engines.

I would really expect you'll have to build the composition of the
object yourself.

Any suggestions on how to do that exactly?

> how can I inspect the sql that AR is generating?

Look at the file /log/development.log

Ok, thanks. Is there a way to inspect the sql in a standalone AR script?

Thanks,

Dan

4

I've got two models from two different databases that I wish to associate if possible. The association is not based on a primary key for either model, but on a "job_number" field that both tables have (which is unique in both tables). Here's what I tried:

I'm no expert on this one, but considering Rails isn't very excited about working with more than one DB to start with (no way to declare multiple DBs in it's own config system), I can't imagine it doing associations across databases for you -- let alone ones with completely different RDBMS engines.

Actually I can't see why it wouldn't work. All it's going to do is run
select from bars where foo_id = x.

The real problem here is that active record doesn't do assocations
where the foreign key isn't pointing at the primary key on some other
table.

Fred

I would really expect you'll have to build the composition of the object yourself.

Any suggestions on how to do that exactly?

how can I inspect the sql that AR is generating?

Look at the file /log/development.log

Ok, thanks. Is there a way to inspect the sql in a standalone AR script?

not that I know of

5

It's just brute force. You write all the code to coordinate the two AR models, but encapsulate it inside a wrapper object (aka composition).

You build a plain Ruby class composed of a Job object and a Schedule object. You use your existing AR classes for each (minus the associations code).

Rough 2-minute idea:

Class ScheduledJob    def initialize(job_number)      @job = Job.find_by_job_number(job_number)      @schedule = Schedule.find_by_job_number(job_number)    end end

Now you can

   my_job = ScheduledJob.new('12345A')

and get at details of either sub-object

   my_job.job.description    my_job.schedule.completed?

Something like that. Then actions that have to deal with bits of info from each, you just add as methods to ScheduledJob and interface to Job and Schedule as necessary. That way you encapsulate the logic that manages ScheduledJob. From the oustide, you treat ScheduledJob as a single object, but on the inside it's made up of more than one.

How do you save a ScheduledJob? Write a save method that accepts the possible params, then divy up the params into the respective Job and Schedule saves that are necessary. Brute force, but at least you get a nice interface to it all.

-- gw

6

Ok, I think this is the approach I'm going to take, probably combined with some sort of delegation scheme to ease the pain. Many thanks for the suggestion.

Regards,

Dan