# Legacy association without primary key

**URL:** <https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005>\
**Category:** Rails\
**Created:** [August 2, 2006, 6:31pm UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005 "2006-08-02T18:31:50Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 2, 2006, 6:31pm UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/1 "2006-08-02T18:31:50Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 3, 2006, 1:24am UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/2 "2006-08-03T01:24:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 3, 2006, 11:26am UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/3 "2006-08-03T11:26:51Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 3, 2006, 12:58pm UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/4 "2006-08-03T12:58:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 3, 2006, 1:02pm UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/5 "2006-08-03T13:02:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 4, 2006, 5:46am UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/6 "2006-08-04T05:46:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![James\_Reeves](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/77aa72/32.png) [@James\_Reeves](https://www.ruby-forum.com/u/James_Reeves)\
**Post date:** [August 3, 2006, 1:16pm UTC](https://www.ruby-forum.com/t/legacy-association-without-primary-key/101005/7 "2006-08-03T13:16:05Z")

</div>

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?