Forum: Ruby on Rails Eager loading with has_many :through

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
R. Elliott M. (Guest)
on 2007-03-09 12:18
I've run into an interesting problem that I have been unable to solve.

My code is in the following pastie: http://pastie.caboo.se/45781

User goes to /tag/tagname/, the action finds the tag with that slug and
uses that to find posts with that tag.  Unfortunately, for whatever
reason, eager loading those Post's Tags just doesn't work.  I've tried
the following:

This example is the one I pretty much saw on every site dealing with
eager loading with has_many :through.  It doesn't work, though.  I get
the error: "Association named 'tags' was not found; perhaps you
misspelled it?"  No I didn't misspell it, has_many :tags is right in my
Post model.
@tags.posts.find(:all, :include => {:taggings => :tags})

This gives me no error, HOWEVER, based on development logs, queries are
still being run during rendering.  So it's not halting the app but at
the same time it's not actually doing anything.  And yes, :tag is meant
to be singular in this example, although I'm not sure why AR likes this
more than plural (?), I'm using has_many :tags, not has_one :tag :
@tags.posts.find(:all, :include => {:taggings => :tag})

This gives me the error, "Mysql::Error: #42000Not unique table/alias:
'taggings':".  I don't know what that's supposed to mean.
@tags.posts.find(:all, :include => [:tags])

However, this works fine, no errors, and no queries during rendering:
Post.find(:all, :include => [:tags])

But of course that gives me all Posts instead of Posts with a specific
Tag.  I am baffled as to why the last example succeeds where the other
ones fail.

Really clueless here.  Please help :(
Chris H. (Guest)
on 2007-03-09 15:18
(Received via mailing list)
I don't know if there is a specific reason you're using has_many
:through this way, because what you have is a classic habtm situation.
 has_many :through is typically used when your join table represents a
model, where as habtm uses only a table containing id columns, such as
what you have.

why not try:

@threads = @tag.posts.find(:all, ..., :include => :tags )

where your models are

class Post < ActiveRecord::Base
  has_and_belongs_to_many :tags, :join_table => "taggings"
end

class Tag < ActiveRecord::Base
  has_and_belongs_to_many :posts, :join_table => "taggings"
end

and no Tagging model
R. Elliott M. (Guest)
on 2007-03-09 22:06
That was my original setup.  However, then I decided that I wanted to
have additional fields in the join table.  Doing this with HABTM is
deprecated; has_many :through and using a join model is the recommended
solution to a join table with additional attributes.
Conrad T. (Guest)
on 2007-03-09 22:36
(Received via mailing list)
Hi, could you please post your models see their current state?

-Conrad
Evan W. (Guest)
on 2007-03-09 22:39
Conrad T. wrote:
> Hi, could you please post your models see their current state?
>
> -Conrad

Tagging has a pair of belong_to's, not has_many's. So it has a singular
relationship to Tag, not plural.

Evan W.
Evan W. (Guest)
on 2007-03-09 22:44
I wrote:
> Tagging has a pair of belong_to's, not has_many's. So it has a singular
> relationship to Tag, not plural.

I see perhaps that you have already tried that, although you
misunderstood why it was necessary? Either way, what SQL query does that
generate that causes the new alias error?

Evan W.
R. Elliott M. (Guest)
on 2007-03-09 23:11
> I see perhaps that you have already tried that, although you
> misunderstood why it was necessary? Either way, what SQL query does that
> generate that causes the new alias error?
It made sense sort of but it contradicted what I read in another
tutorial.  But, like I said, it doesn't raise an error but doesn't seem
to really work, either.

Anyway, I guess this would be the full query, this is what I get with
@tags.posts.find(:all, :include => :tags)

Mysql::Error: #42000Not unique table/alias: 'taggings': SELECT
posts.`id` AS t0_r0, posts.`parent_id` AS t0_r1, posts.`created_on` AS
t0_r2, posts.`bumped_on` AS t0_r3, posts.`author_name` AS t0_r4,
posts.`author_tripcode` AS t0_r5, posts.`author_address` AS t0_r6,
posts.`subject` AS t0_r7, posts.`text` AS t0_r8, posts.`password` AS
t0_r9, posts.`ip_address` AS t0_r10, posts.`sticky` AS t0_r11, tags.`id`
AS t1_r0, tags.`name` AS t1_r1, tags.`slug` AS t1_r2 FROM posts  LEFT
OUTER JOIN taggings ON (taggings.post_id = posts.id)  LEFT OUTER JOIN
tags ON (tags.id = taggings.tag_id)  INNER JOIN taggings ON posts.id =
taggings.post_id    WHERE ((taggings.tag_id = 1))


I'm not really an expert on MySQL so I can't even really tell what that
all means.
Evan W. (Guest)
on 2007-03-10 00:00
It's generating a conflicting (and apparently useless) double join for
some reason. What SQL gets generated if you do @tags.posts.find(:all,
:include => {:taggings => :tag}), instead?

Evan W.
R. Elliott M. (Guest)
on 2007-03-10 00:41
Evan W. wrote:
>
> It's generating a conflicting (and apparently useless) double join for
> some reason. What SQL gets generated if you do @tags.posts.find(:all,
> :include => {:taggings => :tag}), instead?
>
> Evan W.

I'm guessing this from the development log is what you mean:
  Post Load Including Associations (0.015000)
SELECT posts.`id` AS t0_r0, posts.`parent_id` AS t0_r1,
posts.`created_on` AS t0_r2, posts.`bumped_on` AS t0_r3,
posts.`author_name` AS t0_r4, posts.`author_tripcode` AS t0_r5,
posts.`author_address` AS t0_r6, posts.`subject` AS t0_r7, posts.`text`
AS t0_r8, posts.`password` AS t0_r9, posts.`ip_address` AS t0_r10,
posts.`sticky` AS t0_r11, taggings_posts.`id` AS t1_r0,
taggings_posts.`post_id` AS t1_r1, taggings_posts.`tag_id` AS t1_r2,
taggings_posts.`disabled` AS t1_r3, tags.`id` AS t2_r0, tags.`name` AS
t2_r1, tags.`slug` AS t2_r2 FROM posts LEFT OUTER JOIN taggings
taggings_posts ON taggings_posts.post_id = posts.id LEFT OUTER JOIN tags
ON tags.id = taggings_posts.tag_id INNER JOIN taggings ON posts.id =
taggings.post_id WHERE ((taggings.tag_id = 1)) 

Of course later in the log I can see queries to find tags:
  Tag Load (0.000000)   SELECT tags.* FROM tags INNER
JOIN taggings ON tags.id = taggings.tag_id WHERE ((taggings.post_id =
91)) 


Here's the query run when I use Post.find(:all, :include => [:tags])
instead of @tag.posts.find(:all, :include => {:taggings => :tag}):
  Post Load Including Associations (0.000000)   SELECT
posts.`id` AS t0_r0, posts.`parent_id` AS t0_r1, posts.`created_on` AS
t0_r2, posts.`bumped_on` AS t0_r3, posts.`author_name` AS t0_r4,
posts.`author_tripcode` AS t0_r5, posts.`author_address` AS t0_r6,
posts.`subject` AS t0_r7, posts.`text` AS t0_r8, posts.`password` AS
t0_r9, posts.`ip_address` AS t0_r10, posts.`sticky` AS t0_r11, tags.`id`
AS t1_r0, tags.`name` AS t1_r1, tags.`slug` AS t1_r2 FROM posts LEFT
OUTER JOIN taggings ON (taggings.post_id = posts.id) LEFT OUTER JOIN
tags ON (tags.id = taggings.tag_id) 

That's the query that seems to work -- there are no queries for Tags
being run during rendering in that case, of course it returns too many
Posts.
R. Elliott M. (Guest)
on 2007-03-10 03:05
Okay, it seems like this works:

tag = Tag.find(num)
Post.find(:all, :conditions => ['tags.id = ?', tag.id], :include =>
[:tags])

But it seems like the :include => [:tags] argument is 100% necessary, or
I get something like:
Mysql::Error: #42S22Unknown column 'tags.id' in 'where clause': SELECT *
FROM posts WHERE (tags.id = 1)

This is good enough for me I guess.  I should have tried this earlier.
I figured since 'tags.id' is not an actual column in the posts table
then I couldn't have it in :conditions.  I also always looked at
:include as "extra"; ie I thought any query would work without it, but
this seems to not be the case in my situation.  I would have never
thought that the absence of :include would _break_ a query.

I don't know a whole lot about Rails or MySQL, but I can say in an ideal
world, @tags.posts.find(:all, :include => [:tags]) would work just fine.
I don't think what I was trying to do was unreasonable...  I don't know
if it counts as a bug or something that could be but is not quite
implemented or what.
This topic is locked and can not be replied to.