title: Rails 4, ActiveRecord SQL subqueries
body: <p>A <code>:parent has_many :children</code> and I am trying to retrieve the age of the oldest child for a parent as an attribute on the <code>parent</code>. I'm open to any solution that accomplishes that efficiently.</p>
  
  <p>The reason I'm trying to do a subquery is to let the DB do the <code>n+1</code> overhead instead of making a separate DB request for each parent. Both are inefficient, but using a subquery seems more efficient.</p>
  
  <pre><code># attributes: id
  class Parent &lt; ActiveRecord::Base
    has_many :children
  
    # Results in an (n+1) request
    def age_of_oldest_child
      children.maximum(:age)
    end
  end
  
  # attributes: id, parent_id, age
  class Child &lt; ActiveRecord::Base
    belongs_to :parent
  end
  </code></pre>
  
  <p>Sample use case:</p>
  
  <pre><code>parent = Parent.first.age_of_oldest_child # =&gt; 16
  
  parents = Parent.all
  parents.each do |parent|
    puts parent.age_of_oldest_child # =&gt; 16, ...
  end
  </code></pre>
  
  <p>My attempt:</p>
  
  <pre><code>sql = "
    SELECT 
      (SELECT
        MAX(children.age)
        FROM children
        WHERE children.parent_id = parents.id
      ) AS age_of_oldest_child
    FROM
      parents;
  "
  
  Parent.find_by_sql(sql)
  </code></pre>
  
  <p>This returns an array of maximum ages for all parents; I would like to restrict this to just 1 parent or also have it included as an attribute on a parent when I retrieve all parents.</p>
  
  <h1>Update 2015-06-19 11:00</h1>
  
  <p>Here is a workable solution I came up with; are there more efficient alternatives?</p>
  
  <pre><code>class Parent &lt; ActiveRecord::Base
    scope :with_oldest_child, -&gt; { includes(:oldest_child) }
  
    has_many :children
    has_one :oldest_child, -&gt; { order(age: :desc).select(:age, :parent_id) }, class_name: Child
  
    def age_of_oldest_child
      oldest_child &amp;&amp; oldest_child.age
    end
  end
  </code></pre>
  
  <p>Example usage:</p>
  
  <pre><code># 2 DB queries, 1 for parent and 1 for oldest_child
  parent = Parent.with_oldest_child.find(1)
  
  # No further DB queries
  parent.age_of_oldest_child # =&gt; 16
  </code></pre>