title: ActiveRecord : LEFT OUTER self-JOIN with conditions on joined table
body: <p>I have an ActiveRecord class with a reflexive association (<em>note : class names are redacted for non-disclosure reasons</em>) :</p>
  
  <pre><code>   class Thing &lt; ActiveRecord::Base
  
       belongs_to :parent, 
                  class_name: 'Thing'
  
       has_many :children, 
                class_name:  'Thing', 
                foreign_key: :parent_id
  
       scope :children, -&gt;{ where(arel_table[:parent_id].not_eq(nil)) }
  
     end
  </code></pre>
  
  <p><strong>I want to get all children, left outer joined to their parent, with an additional condition on the parent if it's present, while still eager-loading the parent.</strong></p>
  
  <p>Usually in this case i do something like :</p>
  
  <pre><code> Thing.includes(:other_thing).merge(OtherThing.some_scope)
  </code></pre>
  
  <p>But it's not possible in this case : as this is a self-join, if I merge a scope from <code>Thing</code> the condition will apply to the children things, not the parents.</p>
  
  <p>I tried to do this in raw SQL :</p>
  
  <pre><code>  scope :some_scope, -&gt;{
      children.joins(&lt;&lt;-SQL).references(:parent)
        LEFT OUTER JOIN things AS parents
          ON  things.parent_id = parents.id 
          AND parents.some_field IN ('foo', 'bar')
      SQL
    }
  </code></pre>
  
  <p>The query is OK, but it seems that the parent does not get eager-loaded (n+1 queries when trying to <code>map(&amp;:parent)</code> on this scope).</p>
  
  <p>For now, my more-or-less working solution is to use a sub query (plucking ids that match the parent condition, then selecting children with <code>parent_id IN</code> this set), but I'd like to know if there is a more appropriate / elegant one.</p>