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

ruby-on-rails-3 activerecord sql-order-by distinct-on
user1116573 picture user1116573 · Nov 7, 2012 · Viewed 12.2k times · Source

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

Carlos Drew picture Carlos Drew · Jun 25, 2013

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.

Related questions

"Order by" result of "group by" count?

This query Message.where("message_type = ?", "incoming").group("sender_number").count will return me an hash. OrderedHash {"1234"=>21, "2345"=>11, "3456"=>63, "4568"=>100} Now I want to order by count of each group. How can I do that within the query.

Read More
How do you manually execute SQL commands in Ruby On Rails using NuoDB

I'm trying to manually execute SQL commands so I can access procedures in NuoDB. I'm using Ruby on Rails and I'm using the following command: ActiveRecord::Base.connection.execute("SQL query") The "SQL query" could be any SQL command. Like …

Read More
Check if record exists from controller in Rails

In my app a User can create a Business. When they trigger the index action in my BusinessesController I want to check if a Business is related to the current_user.id: If yes: display the business. If no: redirect …

Read More