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.
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.