small medium large xlarge

Generic-user-small
01 Aug 2008, 19:11
Wayde Gilliam (18 posts)

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

Samr_small_pragsmall
02 Aug 2008, 01:59
Sam Ruby (634 posts)

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”:http://ryandaigle.com/articles/2008/3/24/what-s-new-in-edge-rails-has-one-through 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”:http://wiki.rubyonrails.org/rails/pages/ThroughAssociations and “this tutorial”:http://wiki.rubyonrails.org/rails/pages/Beginner+Howto+on+has_many+:through may prove helpful.

Generic-user-small
02 Aug 2008, 20:46
Wayde Gilliam (18 posts)

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
  • There should be on record in totals for any distinct set of user and company.

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

Samr_small_pragsmall
02 Aug 2008, 23:13
Sam Ruby (634 posts)

While has_one “doesn’t support”:http://dev.rubyonrails.org/ticket/2231 :finder_sql, it does support :conditions which should do what you want.

Untested [“based on this”:http://groups.google.ca/group/rubyonrails-talk/browse_thread/thread/0483961278555797]:

:has_one => ['user_id = ? and company_id = ?', '#{self.userid}', '#{self.companyid}']
Generic-user-small
04 Aug 2008, 03:30
Wayde Gilliam (18 posts)

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

You must be logged in to comment