title: Rails 4+, ordering list of parent records by children
body: <p>I have a page that displays a lot of data and I'm working on optimizing it and I'm struggling with balancing reducing DB queries with getting the proper data in the order I need.</p>
  
  <p>A <code>Transaction</code> is the top-level element, and each <code>transaction</code> has multiple <code>workflows</code> and each <code>workflow</code> has many <code>milestones</code>.  I order the <code>workflows</code> and <code>milestones</code> by a <code>position:integer</code> attribute.</p>
  
  <pre><code># Top-level element
  class Transaction &lt; ApplicationRecord
    has_many :workflows
    has_many :team_members
  end
  
  # Parent element for milestones
  class Workflow &lt; ApplicationRecord
    belongs_to :transaction
    has_many :milestones
  end
  
  # Unique individual
  class Person &lt; ApplicationRecord
    has_many :team_members
  end
  
  # Acts as a join between Transactions and Persons
  class TeamMember &lt; ApplicationRecord
    belongs_to :transaction
    belongs_to :person
    belongs_to :team_member_type
  end
  
  class TeamMemberType &lt; ApplicationRecord
    has_many :team_members
  end
  </code></pre>
  
  <p>In my controller this is what I have:</p>
  
  <pre><code>def show
    @transaction = Transaction.includes(
      team_members: [:person, :team_member_type],
      workflows: [:milestones]
    ).find(params[id])
  end
  </code></pre>
  
  <p>This gathers all the data I need in 1 DB query, but the problem is when I iterate over the milestones for a workflow, I need it to be sorted by the <code>position</code> attribute.</p>
  
  <p>I can grab the data from the DB and then sort it with Ruby, but that seems inefficient and I would prefer to get it all done in the DB properly (if possible).</p>
  
  <p>If I were to write it with an <code>N+1</code> issue, it would look like this:</p>
  
  <pre><code>&lt;% @workflows.order(:position).each do |workflow| %&gt;
    &lt;%= workflow.name %&gt;
    &lt;% workflow.milestones.order(:position).each do |milestone| %&gt;
      &lt;%= milestone.name %&gt;
    &lt;% end %&gt;
  &lt;% end %&gt;
  </code></pre>
  
  <p>I think everyone agrees that's a bad idea, but I'm struggling to figure out how to sort the data and optimize my DB calls at the same time.</p>
  
  <p>I also sort the <code>team_members</code> by the <code>team_member_type.value</code> attribute, but that's the same question as above.</p>
  
  <pre><code>&lt;% @team_members.includes(:team_member_type, :person).order("team_member_types.value").each do |team_member| %&gt;
    ...
  &lt;% end %&gt;
  </code></pre>
  
  <p>My main objective is efficiency.  If I need to use some Query objects to clean it up and raw SQL, I'm ok with that, I'm just not extremely proficient at SQL and would prefer using ActiveRecord if reasonable.</p>