'PG::GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function' in rails while trying ".group"

TL;DR:

If you have an ordering scope on a column of your ActiveRecord model, that must appear in the result set of the group clause. You need to get rid of the scope with ActiveRecord::Base.unscoped to get SomeArModel.group(:some_column).count(:id_column) working.

Example

If you try to do the following:

Copy Article.group(:visible_to_readers).count(:id)

And you have a default scope on your model:

Copyclass Article < ActiveRecord::Base
  default_scope -> { order(published_at: :desc) }
end

Then you will get the following error:

CopyPG::GroupingError: ERROR:  column "articles.published_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..."articles"  GROUP BY visible_to_readers  ORDER BY "articles"...

Why?

If you look at the generated sql, you will find that the order column is preserved:

CopySELECT COUNT("articles"."id") AS count_id, visible_to_readers AS visible_to_readers FROM "articles"  GROUP BY visible_to_readers  ORDER BY "articles"."published_at" DESC

Therefore the database needs the column in the grouped result-set to do its job ordering the results.

The Fix™:

Use ActiveRecord::Base.unscoped to get rid of the implicit scope constrains:

Copy Article.unscoped.group(visible_to_readers).count(:id)
 => {false=>118, true=>456}