Multiple join conditions

gistfile1.md

Here's the simplest way I can think of to describe the problem.

Using the classic posts, authors and comments I need to query for an authors posts and any comments (there may be zero to many comments) for a specific author. Database is postgressql 9.2 and Rails 4.0.

The query should come out something like (this isn't valid sql, need to show that author_id needs to be dynamic):

SELECT * 
FROM POSTS
JOIN AUTHORS ON posts.author_id = authors.id
LEFT OUTER JOIN comments ON comments.posts_id = posts.id AND comments.author_id = #{author_id}
WHERE posts.author_id = #{author_id}

The second condition on the join is the secret sauce here that gives the right results and allows this to be eager loaded (not to mention extremely fast). The author_id in that second condition should be dynamic too.

In postgresql rewriting this query to move the second join condition to the WHERE clause does not produce correct results. (In this case, the post has to have comments from the author_id for it to be included in the results.)

How do I get the second join condition in active record? Resorting to a n+1 situation is not an option for performance this needs to be eager loaded or at worst done with two queries.

If this ever gets picked up by a search engine, I've included some working code that shows how to deal with this. Notice the lambda on the has_many :author_comments. Thanks to @erniemiller.

#!/usr/bin/env ruby

gem 'activerecord'
gem 'squeel', "~> 1.1"
gem 'sqlite3'

require "active_record"
require "squeel"

ActiveRecord::Base.establish_connection adapter: "sqlite3", database: ":memory:"

silence_stream(STDOUT) do
  ActiveRecord::Migration.verbose = false

  ActiveRecord::Schema.define do
    create_table :authors, :force => true do |t|
      t.string :name
    end

    create_table :posts, :force => true do |t|
      t.belongs_to :author
      t.string     :title
    end

    create_table :comments, :force => true do |t|
      t.belongs_to :author
      t.belongs_to :post
      t.text       :body
    end
  end
end

class Post < ActiveRecord::Base
  belongs_to :author
  has_many :comments
  # This will only work with eager_load, since an include will not necessarily
  # have the posts table present.
  has_many :author_comments, -> { where 'comments.post_id = posts.id' },
    :through => :author, :source => :comments
end

class Author < ActiveRecord::Base
  has_many :posts
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :author
  belongs_to :post
end

ernie  = Author.create!(:name => 'Ernie Miller')
post_1 = ernie.posts.create!(:title => 'Hello, world!')
post_2 = ernie.posts.create!(:title => 'Hello, again!')
paul   = Author.create!(:name => 'Paul Lamb')
post_1.comments.create!(:author => ernie, :body => 'hello')
post_1.comments.create!(:author => paul, :body => 'hello to you!')
post_2.comments.create!(:author => paul, :body => 'FIRST POST!')

ActiveRecord::Base.logger = Logger.new(STDOUT)

posts = ernie.posts.eager_load(:author_comments).to_a

puts '===> POSTS LOADED! NO MORE QUERIES SHOULD EXECUTE.'

first_post  = posts.first
second_post = posts.last

puts first_post.title
puts first_post.author_comments.loaded?
puts first_post.author_comments.map(&:body)

puts second_post.title
puts second_post.author_comments.loaded?
puts second_post.author_comments.map(&:body)