Habtm and insert_sql


#1

Briefly, I want to create an :insert_sql attribute for
a has_and_belongs_to_many relationship, and then add items to that
relationship using push_with_attributes. Is that possible?

Less briefly:

I have a legacy postgresql database where one of the join tables has an
ID column, so I had to set up the association with a custom :finder_sql
attribute, as follows:

class Topic < ActiveRecord::Base
[…]
has_and_belongs_to_many :child_documents, :class_name => “Document”,
:join_table => “topic_subtopic”,
:foreign_key => “topic_key”, :association_foreign_key =>
“child_topic_key”,
:finder_sql => ‘select t.*, ts.display_header from topics t,
topic_subtopic ts where t.id = ts.child_topic_key and ts.topic_key =
#{id} and url is not null’

The structure of topic_subtopic, as you can partially deduce from the
above, is:
Column | Type | Modifiers
-----------------±--------±---------------------------------------------------------------
id | integer | not null
topic_key | integer | not null
child_topic_key | integer | not null
display_header | text

If I do this:
t = Topic.find(2220)
d = Document.find(3084)
t.child_documents.push_with_attributes d, :display_header => ‘foobart’

It adds the following row to topic_subtopic:
id | topic_key | child_topic_key | display_header
------±----------±----------------±---------------
3084 | 2220 | 3084 | foobart

Looks like it did the right thing, but look at the value of the ‘id’
column, it’s the same as the child_topic_key value (if it was using the
default sequence for id, the value should have been 1937).

So it actually did the wrong thing, and this could blow up if it tries
to insert an id that already exists (the id column is a primary key
column with a unique constraint).

So I guess I want to add an :insert_sql attribute to the
has_and_belongs_to_many relationship, but how do I pass it the value for
display_header?
display_header can be required, if that makes it easier.

I know I can get this to work just by calling find_by_sql and handcoding
the insert query instead of using the habtm relationship, but I find
myself doing that a lot and would really rather know how to do it The
Rails Way.

Can anyone help?
Thanks