On Jan 8, 6:38 am, "kwe...@pobox.com" <kurt.we...@gmail.com> wrote:
> I get something like:
> SELECT "comments".* FROM "comments"
> INNER JOIN "people" ON "people"."id" = "comments"."person_id"
> INNER JOIN "articles" ON "articles"."id" = "comments"."article_id"
> INNER JOIN "article_people" ON "articles"."id" =
> "article_people"."article_id"
> INNER JOIN "people" "authors_articles" ON "authors_articles"."id" =
> "article_people"."person_id"
> WHERE ("people"."name" = 'Bob')
>
> Two or more things are happening, here:
> 1. The reference to Frank is completely missing.
> 2. The reference to Bob is being applied to the comment author, NOT
> the article author.
>
> What am I doing wrong?
> Or
> What is arel doing wrong?
> And
> How do I make it right?
What's happening is that arel doesn't know that one where is talking
about one person alias and the other a different one. It thinks you're
setting 2 contradictory wheres and picks the last (ie bob)
Arel itself certainly knows how to do this sort of thing (indeed it's
sort of why Arel exists, since this sort of stuff is nighmareish to
deal without a high level abstraction of what queries are), since you
can do something like
comments = Arel::Table.new(:comments)
posts = Arel::Table.new(:posts)
users = Arel::Table.new(:users)
authorships = Arel::Table.new(:authorships)
authors = users.alias
puts comments.join(users).on(comments[:user_id].eq(users[:id]))\
.join(posts).on(comments[:post_id].eq(posts[:id]))\
.join(authorships).on(posts[:id].eq(authorships[:post_id]))\
.join(authors).on(authorships[:user_id].eq(authors[:id]))\
.where(users[:name].eq('Bob').and(authors[:name].eq('Frank'))).project(Arel.sql('*')).to_sql
which outputs
SELECT * FROM "comments"
INNER JOIN "users" ON "comments"."user_id" = "users"."id"
INNER JOIN "posts" ON "comments"."post_id" = "posts"."id"
INNER JOIN "authorships" ON "posts"."id" = "authorships"."post_id"
INNER JOIN "users" "users_2" ON "authorships"."user_id" =
"users_2"."id"
WHERE "users"."name" = 'Bob' AND "users_2"."name" = 'Frank'
I'm not sure quite how you get rails to do this for you though. I
remember the code (in join_dependency.rb) that builds joins from
declared associations being a bit of a nightmare to get your head
around and it doesn't seem to be anymore transparent.
You could change your second scope to
scope :on_article_by, lambda { |name| joins(:post
=> :users).where(User.arel_table.alias('users_posts')
[:name].eq(name).to_sql) }
But clearly this assumes that that you know what the table alias for
the 'correct' users table is, and will change according to
combinations of scopes (eg the above only works
for .by('Frank').on_article_by('Bob'), it wouldn't work on
just .on_article_by('Bob')). You could might able to extract what the
already existant joins are and devine from that what the table alias
should be or something like that, but this will be (I feel) quite
fiddly
Fred
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to rubyonrails-t...@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
