Grouping a timestamp field by date in Ruby On Rails / PostgreSQL

Question

I am trying to convert the following bit of code to work with PostgreSQL.

After doing some digging around I realized that PostgreSQL is much stricter (in a good way) with the GROUP BY than MySQL but for the life of me I cannot figure out how to rewrite this statement to satisfy Postgres.

def show
  show! do
    @recent_tasks = resource.jobs.group(:task).order(:created_at).limit(5)
  end
end

PG::Error: ERROR:  column "jobs.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ND "jobs"."project_id" = 1 GROUP BY task ORDER BY created_at...
                                                         ^
: SELECT  COUNT(*) AS count_all, task AS task FROM "jobs"  WHERE "jobs"."deleted_at" IS NULL AND "jobs"."project_id" = 1 GROUP BY task ORDER BY created_at DESC, created_at LIMIT 5

sufleR · Accepted Answer · 2012-11-27 22:03:19Z

1

You cannot use column in order which is not in group by.
You can do something like

@recent_tasks = resource.jobs.group(:task, :created_at).order(:created_at).limit(5)

but it will change result

You can also

@recent_tasks = resource.jobs.group(:task).order(:task).limit(5)

or

@recent_tasks = resource.jobs.group(:task).order('count(*) desc').limit(5)

answered Nov 27, 2012 at 22:03

sufleR

2,99320 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Grouping a timestamp field by date in Ruby On Rails / PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related