title: Applying a different order to postgres "DISTINCT ON" using rails
body: <p>I have a rails app:</p>
  
  <p><code>user has_many :projects</code></p>
  
  <p><code>user has_many :tasks, :through =&gt; :projects</code></p>
  
  <p><code>project has_many :tasks</code></p>
  
  <p>Each task has a milestone date.</p>
  
  <p>To show a table of project details with next milestone date I am using:</p>
  
  <pre><code>@projects = current_user.tasks.joins(:project).select("distinct on (projects.id) projects.*, tasks.*").reorder("projects.id, tasks.milestone ASC")
  </code></pre>
  
  <p>This works fine.</p>
  
  <p>I now want to be able to sort the table columns.</p>
  
  <p>According to Postgres <code>DISTINCT ON</code> is not sortable, you have to wrap it in another select statement, ie <code>SELECT * FROM (SELECT DISTINCT ON....) ORDER BY column_3</code></p>
  
  <p>I did think that the column being ordered could just be worked into the SQL as required, ie (to order by project name DESC):</p>
  
  <pre><code>@projects = current_user.tasks.joins(:project).select("distinct on (projects.name) projects.*, tasks.*").reorder("projects.name DESC, tasks.milestone ASC")
  </code></pre>
  
  <p>which works but I also want to be able to order by milestone and that doesn't work that way.</p>
  
  <p>Can someone tell me how to convert my rails query so that it can be ordered by any of the columns?</p>
  
  <p>UPDATE</p>
  
  <hr>
  
  <p><strong>I suppose my question is simply how do I wrap an activerecord query in a surrounding <code>SELECT</code> and <code>ORDER BY</code>?</strong></p>
  
  <p>I think I've managed to achieve it using:</p>
  
  <pre><code>inner_query = current_user.tasks.select("distinct on (projects.id) projects.*, tasks.*").reorder("projects.id, tasks.milestone ASC").to_sql
  @projects = Task.paginate_by_sql("select * from (#{inner_query}) as user_projects order by user_projects.name", :page =&gt; params[:page])
  </code></pre>
  
  <p>Is that the best way or can someone think of a better way? - find/paginate_by_sql seems like a workaround and I would have preferred to stay within the realms of activerecord query.</p>
  
  <p>Thanks</p>