has_many, through, foreign key problems

rubyonrails-talk
1

Becca Girl wrote:

Article has_many :readings has_many :users, :through => :readings

Readings belongs_to :article, :foreign_key => :article_id belongs_to :user, :foreign_key => :user_id

Users has_many :readings has_many :articles, through => readings

Add the foreign keys to the has_many side, too:

Article has_many :readings, :foreign_key => :article_id

etc

2

Becca Girl wrote:

But my sql query has not changed. Maybe I need to change the code that generates the query?

@articles = Articles.find(:all, :conditions => "user_id = 1", :include => :readings)

Sorry, I wasn't thinking right. The foreign key specifications on the has_many/belongs_to association refers to the name of the column on the belongs_to side only. Since this column is named correctly anyway you don't need to specify this on either side.

The actual issue here is that you are performing the Rails equivalent of a foreign key lookup and a foreign key must connect to a primary key.

In Rails, when handling legacy tables, the primary key of a table is not always the one you want Rails to see as the primary key. In your example, the Article model (presumably using an articles table?) has a database column of id which is the table's primary key, but from the Rails perspective, the article_id column is the primary key. Use:

class Article self.primary_key = :article_id has_many :readings has_many :users, :through => :readings

class Reading belongs_to :article belongs_to :user

If the same situation occurs in the User class, then do the same thing with that association.

3

Mark Bush wrote:

class Article self.primary_key = :article_id has_many :readings has_many :users, :through => :readings

class Reading belongs_to :article belongs_to :user

Thanks Mark. Here's a funny thing though, the primary key of article is not article_id, it's actually id. The article_id is actually how these two tables connect to each other. So the primary key needs to be used in other areas of the site, but not in this situation. Again, legacy data is making it a bit more difficult.

Any way to tell this to not use id and instead use article_id in this instance?

4

This is probably out of my depth, but I'll blunder in anyway. Could maybe a view rescue you here? If all you need to do is rename some fields, maybe you could create a view & point rails at that?

Just a thought...

5

Becca Girl wrote:

Any way to tell this to not use id and instead use article_id in this instance?

The primary key is a class attribute. You could try adding interfaces to these queries in your Article model where the primary key is temporarily changed. Something like:

class Article   # association stuff

  def self.article_id_find *args     self.primary_key = :article_id     find *args   ensure     self.primary_key = :id   end

If this is running under mongrel it should be ok as mongrel is single threaded, but it's a bit of a kludge.

6

Mark Bush wrote:

class Article   # association stuff

  def self.article_id_find *args     self.primary_key = :article_id     find *args   ensure     self.primary_key = :id   end

If this is running under mongrel it should be ok as mongrel is single threaded, but it's a bit of a kludge.

I dropped in your code (using my field names), but the query stays the same. Ahead of this method, I have self.primary_key defined since due to legacy data it's table_id, not id.

table structure: table_id (primary key) article_id (links to readings)

So my code now looks like this

self.primary_key = "table_id"

def self.pre_mover_legacy_id_find *args   self.primary_key = :article_id   find *args ensure   self.primary_key = "table_id" end

The sql query still tries to join readings.article_id to article.table_id instead of article.article_id.

@articles = Articles.find(:all, :conditions => "user_id = 1", :include => :readings)

Any guidance on what @articles should look like now or is there something else I should change somewhere else?

7

Mark Bush wrote:

@articles = Article. pre_mover_legacy_id_find :all,                                     :include => :users,                                     :conditions => ["users.id = ?", 1]

You've been so great! Hopefully you have a little more time.

Here are all of my parts and pieces that are throwing an error.

Models:

class Article has_many :readings has_many :users, :through => :readings self.primary_key = "table_id"

def self.article_id_find *args   self.primary_key = :article_id   find *args ensure   self.primary_key = "table_id" end end # class Article

class Reading belongs_to :article belongs_to :user end # class Reading

class User has_many :readings has_many :articles, :through => :readings end # class User

UsersController

@articles = Article.article_id_find :all, :include => :users, :conditions => ["users.id = ?", 1]

Error: uninitialized constant UsersController::Articles

8

Becca Girl wrote:

Error: uninitialized constant UsersController::Articles

This indicates that you are referring to "Articles" (plural) instead of "Article" in the UsersController somewhere and Rails can't work out what it should be...

To be sure the query is working, try the #article_id_find method in the console...

9

You need to specify the primary key for the article table.

This is REALLY not recommended.

Julian