has_one ... multiple columns ... advice?

I didn’t see this addressed in the latest pdf but I was wondering the best way to resolve this relationship in AR …

Table A is related to Table B via two columns (lets call them col1 and col2). Table A should only have one related record in Table B and it should be read only. What is the best way to implement such a relationship?

What I have is a hack of the has_many which looks like this:

has_many :tableB,
  :class_name => 'TableB',
  :readonly => true,
  :finder_sql => 'select t.* from tableb t ' +
                  'where t.colb1 = #{col1} ' +
                    'and t.colb2 = #{col2} '
  :limit => 1

Any thoughts?

Thanks – wg

Without a full description of your tables, it is hard to say, but a common technique that often works is to model the relationship itself a separate table (with columns of its own), and use has_many :through. As of 2.1, it appears that has_one :through is now supported too, though I have yet to try it myself.

This is mentioned in chapter 12, and this description and this tutorial may prove helpful.

Thanks Sam for the reply.

Is there any way to simply create a has_one where the join is on two columns (none of which include the primary key)?

Here’s some more info on the all the tables involved … maybe this helps with explaining the situation:

• 4 tables invovled: user, company, user_company_relationship, totals
• totals is a denormalized table that contains a bunch of aggregations for users, companies and user_company_relationships

  class UserCompanyRelationship < ActiveRecord::Base
    belongs_to :user
    belongs_to :company
    has_many :totals,
      :class_name => 'Total',
      :readonly => true,
      :finder_sql => 'select t.* from totals t ' +
                      'where t.userid = #{user_id} ' +
                      'and t.companyid = #{company_id}'
      :limit => 1

Does that help explain things better? As you can see, the user_company_relationship table is using the user class’ user_id and the company’s company_id to get at that one record. It really is a readonly has_one relationship … I’m just not sure the best way to code this in AR.

Thanks – wg

While has_one doesn’t support :finder_sql, it does support :conditions which should do what you want.

Untested [based on this]:

:has_one => ['user_id = ? and company_id = ?', '#{self.userid}', '#{self.companyid}']

I’ve tried this … but the totals tables doesn’t have a foreign key to user_company_table. This produces the following exception:

ActiveRecord::StatementInvalid: PGError: ERROR: column totals.user_company_relationship_id does not exist
: SELECT * FROM “totals” WHERE (“totals”.user_company_relationship_id = 100 AND (user_id = 1 and company_id = 2)) LIMIT 1

Is there a way to make AR not look for the foreign key? And instead just use the condition?

Thanks again – wg