Sorry for the long post…
I’m having difficulties with accessing my data using has_many through
associations. For simplicity, I’ll reference the Agile Web D.
example on this topic.
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
I’m dealing with legacy data and so the glitch to this set up is that
the articles table is has id as the primary key and article_id is
another field in the table. I want the readings table to connect to the
articles table via article.article_id, not article.id.
The end result that I want to see is providing a user (user.id = 1) and
then output a report that shows me a list of all of the articles that
user 1 has read and tell me each time user 1 read the article.
User 1
Article id = 1
Reading id = 1 3/23/2008
Reading id = 2 3/14/2008
Reading id = 3 3/7/2008
Article id = 2
Reading id = 4 3/15/2008
Reading id = 5 3/3/2008
Right now, the query that I get back is
select articles.id
from articles
left outer join readings
readings.article_id = articles.id
where user_id = 1
Any ideas on how to change my relationships so that I can connect
readings to articles?
THANKS!!!
Becca G. 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
Mark B. wrote:
Add the foreign keys to the has_many side, too:
Article
has_many :readings, :foreign_key => :article_id
I now have this
Article
has_many :readings, :foreign_key => :article_id
has_many :users, :through => :readings
Readings
belongs_to :article, :foreign_key => :article_id
belongs_to :user, :foreign_key => :user_id
Users
has_many :readings, :foreign_key => :user_id
has_many :articles, through => readings
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)
Becca G. 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.
Mark B. 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?
Becca G. 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.
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…
Mark B. 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?
Becca G. wrote:
@articles = Articles.find(:all, :conditions => “user_id = 1”, :include
=> :readings)
And:
@articles = Article. pre_mover_legacy_id_find, :include => :readings
?
I’ve now had a chance to test this and it is working ok here. I get:
Article Load Including Associations (0.000314) SELECT
articles.“article_id” AS t0_r0, articles.“table_id” AS t0_r1,
articles.“title” AS t0_r2, articles.“created_at” AS t0_r3,
articles.“updated_at” AS t0_r4, readings.“id” AS t1_r0,
readings.“article_id” AS t1_r1, readings.“user_id” AS t1_r2,
readings.“created_at” AS t1_r3, readings.“updated_at” AS t1_r4 FROM
articles LEFT OUTER JOIN readings ON readings.article_id =
articles.article_id
And for:
@articles = Article. pre_mover_legacy_id_find :all,
:include => :users,
:conditions => [“users.id = ?”, 1]
I get:
Article Load Including Associations (0.000344) SELECT
articles.“article_id” AS t0_r0, articles.“table_id” AS t0_r1,
articles.“title” AS t0_r2, articles.“created_at” AS t0_r3,
articles.“updated_at” AS t0_r4, users.“id” AS t1_r0, users.“username” AS
t1_r1, users.“created_at” AS t1_r2, users.“updated_at” AS t1_r3 FROM
articles LEFT OUTER JOIN readings ON (articles.“article_id” =
readings.“article_id”) LEFT OUTER JOIN users ON (users.“id” =
readings.“user_id”) WHERE (users.id = 1)
Becca G. 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…
You need to specify the primary key for the article table.
This is REALLY not recommended.
Julian
Mark B. 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