Applying a different order to postgres "DISTINCT ON" using rails

Question

I have a rails app:

user has_many :projects

user has_many :tasks, :through => :projects

project has_many :tasks

Each task has a milestone date.

To show a table of project details with next milestone date I am using:

@projects = current_user.tasks.joins(:project).select("distinct on (projects.id) projects.*, tasks.*").reorder("projects.id, tasks.milestone ASC")

This works fine.

I now want to be able to sort the table columns.

According to Postgres DISTINCT ON is not sortable, you have to wrap it in another select statement, ie SELECT * FROM (SELECT DISTINCT ON....) ORDER BY column_3

I did think that the column being ordered could just be worked into the SQL as required, ie (to order by project name DESC):

@projects = current_user.tasks.joins(:project).select("distinct on (projects.name) projects.*, tasks.*").reorder("projects.name DESC, tasks.milestone ASC")

which works but I also want to be able to order by milestone and that doesn't work that way.

Can someone tell me how to convert my rails query so that it can be ordered by any of the columns?

UPDATE

---

I suppose my question is simply how do I wrap an activerecord query in a surrounding SELECT and ORDER BY?

I think I've managed to achieve it using:

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 => params[:page])

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.

Thanks

Answer 1

You're trying to get a set of projects but you're starting with current_user.tasks.

Why not start with current_user.projects, which guarantees distinct projects?

@projects = current_user.projects.includes(:tasks).order("projects.name, tasks.milestone")

Alternative Answer

@projects = current_user.projects.joins(:tasks).select('projects.*, min(tasks.milestone) as next_milestone').order('projects.name').group('projects.id')
@projects.each{|p| puts "#{p.name} #{p.next_milestone}"}

That'll give you one row for each project, with a calculated minimum tasks.milestone value, accessible on the project row result via next_milestone. No extra tasks record, just the next milestone date.

Answer 2

In the user controller:

inner_query = current_user.tasks.next.to_sql
@projects = Task.paginate_by_sql("select * from (#{inner_query}) as user_projects order by user_projects.#{sort_column} #{sort_direction}", :page => params[:page])

And in the task model:

scope :next, select("distinct on (projects.id) projects.*, tasks.*").reorder("projects.id, tasks.milestone ASC")

This way uses the power of postgres to only return the necessary records making the recordset smaller and easier to work with but the trade-off is that the RoR code doesn't look as inviting or as readable as it does with Carlos Drew's suggestion.

Answer 3

To answer this question:

I suppose my question is simply how do I wrap an activerecord query in a surrounding SELECT and ORDER B

Since ActiveRecord 4.0.2 there is now <model>.from.

An example using your models:

inner_query = Project.joins(:tasks).select("DISTINCT ON (projects.id), *") // SELECT DISTINCT ON (projects.id) FROM projects INNER JOIN tasks ON tasks.project_id = projects.id;

You can wrap it in a from:

sorted_query = Project.from(inner_query, :projects).order(:name)