Arel circular relationship question/problem

Rails
1

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?

2

On Jan 8, 6:38am, “[email protected]” [email protected] wrote:

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