CODEWITHSUNDEEP
ruby-on-railspostgresql
Zedrian
Zedrian

Reputation: 909

Rails Postgres Error GROUP BY clause or be used in an aggregate function

In SQLite (development) I don't have any errors, but in production with Postgres I get the following error. I don't really understand the error.

PG::Error: ERROR:  column "commits.updated_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...mmits"."user_id" = 1 GROUP BY mission_id ORDER BY updated_at...
                                                             ^
: SELECT COUNT(*) AS count_all, mission_id AS mission_id FROM "commits"  WHERE "commits"."user_id" = 1 GROUP BY mission_id ORDER BY updated_at DESC

My controller method:

  def show
    @user = User.find(params[:id])
    @commits = @user.commits.order("updated_at DESC").page(params[:page]).per(25)
    @missions_commits = @commits.group("mission_id").count.length
  end

UPDATE:

So i digged further into this PostgreSQL specific annoyance and I am surprised that this exception is not mentioned in the Ruby on Rails Guide.

I am using psql (PostgreSQL) 9.1.11

So from what I understand, I need to specify which column that should be used whenever you use the GROUP_BY clause. I thought using SELECT would help, which can be annoying if you need to SELECT a lot of columns.

Interesting discussion here

Anyways, when I look at the error, everytime the cursor is pointed to updated_at. In the SQL query, rails will always ORDER BY updated_at. So I have tried this horrible query:

@commits.group("mission_id, date(updated_at)")
        .select("date(updated_at), count(mission_id)")
        .having("count(mission_id) > 0")
        .order("count(mission_id)").length

which gives me the following SQL

SELECT  date(updated_at), count(mission_id) 
FROM "commits"  
WHERE "commits"."user_id" = 1 
GROUP BY mission_id, date(updated_at) 
HAVING count(mission_id) > 0 
ORDER BY updated_at DESC, count(mission_id) 
LIMIT 25 OFFSET 0

the error is the same.

Note that no matter what it will ORDER BY updated_at, even if I wanted to order by something else.

Also I don't want to group the records by updated_at just by mission_id.

This PostgreSQL error is just misleading and has little explanation to solving it. I have tried many formulas from the stackoverflow sidebar, nothing works and always the same error.

UPDATE 2: So I got it to work, but it needs to group the updated_at because of the automatic ORDER BY updated_at. How do I count only by mission_id?

@missions_commits = @commits.group("mission_id, updated_at").count("mission_id").size

Upvotes: 0

Views: 2131

Answers (2)

gertas
gertas

Reputation: 17145

I guest you want to show general number of distinct Missions related with Commits, anyway it won't be number on page.

Try this:

@commits = @user.commits.order("updated_at DESC").page(params[:page]).per(25)
@missions_commits = @user.commits.distinct.count(:mission_id)

However if you want to get the number of distinct Missions on page I suppose it should be:

@missions_commits = @commits.collect(&:mission_id).uniq.count

Update

In Rails 3, distinct did not exist, but pure SQL counting should be used this way:

@missions_commits = @user.commits.count(:mission_id, distinct: true)

Upvotes: 1

Philip Hallstrom
Philip Hallstrom

Reputation: 19879

See the docs for PostgreSQL GROUP BY here:

http://www.postgresql.org/docs/9.3/interactive/sql-select.html#SQL-GROUPBY

Basically, unlike Sqlite (and MySQL) postgres requires that any columns selected or ordered on must appear in an aggregate function or the group by clause.

If you think it through, you'll see that this actually makes sense. Sqlite/MySQL cheat under the hood and silently drop those fields (not sure that's technically what happens).

Or thinking about it another way if you are grouping by a field, what's the point of ordering it? How would that even make sense unless you also had an aggregate function on the ordered field?

Upvotes: 1

Related Questions