Legacy association without primary key

Rails
1

I have a legacy database with which I need to interface. There are two
tables that are joined together by two fields, and neither field is the
primary key of its associated table. Is constructing an association
between these tables beyond the capability of Rails?

2

James Reeves wrote:

I have a legacy database with which I need to interface. There are two
tables that are joined together by two fields, and neither field is the
primary key of its associated table. Is constructing an association
between these tables beyond the capability of Rails?

You can use the :finder_sql option on associations to define how your
aoosicated objects are retrieved.

class Word
has_many :sentences,
:finder_sql => ‘SELECT * FROM sentences WHERE body LIKE
%#{self.word}%’
end

3

Alex W. wrote:

You can use the :finder_sql option on associations to define how your
aoosicated objects are retrieved.

I note that Rails cannot infer joins from the SQL, however; it’s just a
one-shot device. Is there a way of specifying a fragment of SQL such
that I can take advantage of eager loading (via :include) in finds?
Otherwise, I’m going to have an SQL statement fired off for every row in
a rather large table!

4

Do these tables have primary keys? If so, they can be models just like
anything else. You’ll just use find_by_sql to retrieve the data.

If you are not able to represent them with ActiveRecord models, you
always
have the option of using straight DBI to connect to the database and
retrieve data.

believe it’s possible in Rails, just not as easy.

5

James/Brian

its possible in rails to use the DBI interface in 2 ways

  1. using require ‘dbi’; con = DBI.connect("DBI::OCI8::your db
    name… #assuming its oracle
    and then proceeding with prepared statement or adhoc sql execute on
    DB
    handle

  2. or accessing connection object from your model object and using
    execute
    method to execute arbitary sql
    i.e. yourModelInstance.connection.execute(“your model related sql”)

yourModelInstance can be accessed in any member function using ‘self’

The only problem I faced while doing this was that there was no easy way
of
populating my modelInstance with data retreived from call to execute,
until
and unless I manually want to retreieve values from DBI::Row objects or
Cursor.fetch

Let me know how it goes.

-daya

6

James:

Maybe we’re over-thinking this: Would you be able to paste your
migrations
and your model’s associations so we could see tem and offer a better
solution? You can email me off list if it’s confidential.

7

Brian H. wrote:

Do these tables have primary keys? If so, they can be models just like
anything else. You’ll just use find_by_sql to retrieve the data.

Fortunately, they do have primary keys (though their relationship is
defined by other fields). Is it possible to retrieve an ActiveRecord and
its associations via find_by_sql?