Forum: Ruby on Rails habtm and insert_sql

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
Dan T. (Guest)
on 2006-01-03 09:11
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 =>
    :finder_sql => 'select t.*, ts.display_header from topics t,
topic_subtopic ts where = 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 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?
This topic is locked and can not be replied to.