Matching nested model association attribute with includes

Question

Suppose I have the following models:

class Post < ActiveRecord::Base
  has_many :authors

class Author < ActiveRecord::Base
  belongs_to :post

And suppose the Author model has an attribute, name.

I want to search for all posts with a given author "alice", by that author's name. Say there is another author "bob" who co-authored a post with alice.

If I search for the first result using includes and where:

post = Post.includes(:authors).where("authors.name" => "alice").first

You'll see that the post only has one author now, even if in fact there are more:

post.authors #=> [#<Author id: 1, name: "alice", ...>]
post.reload
post.authors #=> [#<Author id: 1, name: "alice", ...>, #<Author id: 2, name: "bob", ...>]

The problem seems to be the combination of includes and where, which limits the scope correctly to the desired post, but at the same time hides all associations except for the one that is matched.

I want to end up with an ActiveRecord::Relation for chaining, so the reload solution above is not really satisfactory. Replacing includes by joins solves this, but does not eager load the associations:

Post.joins(:authors).where("authors.name" => "alice").first.authors
#=> [#<Author id: 1, name: "alice", ...>, #<Author id: 2, name: "bob", ...>]
Post.joins(:authors).where("authors.name" => "alice").first.authors.loaded?
#=> false

Any suggestions? Thanks in advance, I've been banging my head over this problem for a while.

Answer 1

I see what you're doing as expected behaviour, at least that's how SQL works... You're restricting the join on authors to where authors.id = 1, so why would it load any others? ActiveRecord just takes the rows that the database returned, it has no way of knowing if there are others, without doing another query based on the posts.id.

Here's one possible solution with a subquery, this will work as a chainable relation, and executes in one query:

relation = Post.find_by_id(id: Author.where(id:1).select(:post_id))

If you add the includes, you will see the queries happen one of two ways:

relation = relation.includes(:authors)

relation.first
# 1. Post Load SELECT DISTINCT `posts`.`id`...
# 2. SQL SELECT `posts`.`id` AS t0_r0, `posts`.`title` AS t0_r1, ...

relation.all.first
# 1. SQL SELECT `posts`.`id` AS t0_r0, `posts`.`title` AS t0_r1, ...

So depending on the scenario, ActiveRecord decides whether to look up the id with a simpler query before loading all the associated authors. Sometimes it makes more sense to run the query in 2 steps.

Answer 2

Coming back to this question after a long long time, I realized there is a better way to do this. The key is to do not one but two joins, one with includes and one with Arel using a table alias:

posts   = Post.arel_table
authors = Author.arel_table.alias("matching_authors")
join    = posts.join(authors, Arel::Nodes::InnerJoin).
                on(authors[:post_id].eq(posts[:id])).join_sources

post = Post.includes(:authors).joins(join).
            where(matching_authors: { name: "Alice" }).first

The SQL for this query is quite long since it has includes, but the key point is that it has not one but two joins, one (from includes) using a LEFT OUTER JOIN on the alias posts_authors, the other (from the Arel join) using an INNER JOIN on the alias matching_authors. The WHERE only applies to the latter alias, so results on the association in the returned results are not limited by this condition.