Rails - Jan 2011 - arel circular relationship question/problem

Given a blog where
Person <->> Comment
Comment <<-> Article
Article <<->> Person (an article may be written by more than one
person - though I don''t think this point is important)

I would like to find all the Comments written by Frank associated with
Articles written by Bob.

In person I have
  scope :by, lambda { |name| joins(:person).merge(Person.where(:name
=> name)) }
  scope :on_article_by, lambda { |name| joins(:article
=> :people).merge(Person.where(:name => name)) }

So I can find
  Comment.by("Frank") which works
and
  Comment.on_article_by("Bob") works

However
  Comment.by("Frank").on_article_by("Bob") does not work.

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?

-- 
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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Jan 8, 6:38 am, "kwe...-e+AXbWqSrlAAvxtiuMwx3w@public.gmane.org"
<kurt.we...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.