Eager loading with has_many :through

Rails
1

I’ve run into an interesting problem that I have been unable to solve.

My code is in the following pastie: Parked at Loopia

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 :frowning:

2

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

3

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.

4

Hi, could you please post your models see their current state?

-Conrad

5

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.

6

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.

7

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.

8

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.

9

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:
e[4;36;1mPost Load Including Associations (0.015000)e[0m
e[0;1mSELECT 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)) e[0m

Of course later in the log I can see queries to find tags:
e[4;36;1mTag Load (0.000000)e[0m e[0;1mSELECT tags.* FROM tags INNER
JOIN taggings ON tags.id = taggings.tag_id WHERE ((taggings.post_id =
91)) e[0m

Here’s the query run when I use Post.find(:all, :include => [:tags])
instead of @tag.posts.find(:all, :include => {:taggings => :tag}):
e[4;35;1mPost Load Including Associations (0.000000)e[0m e[0mSELECT
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) e[0m

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.

10

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.