ERROR: column "engagements.id" must appear in the GROUP BY clause or be used in an aggregate function

0
votes

i have the following query which works

@engagements = Engagement.includes(:participation).where(influencer_authorization_id: current_influencer.id).group_by { |p| p.status }

i am trying to use the Database function instead of the Ruby/Rails function to group and the following is my code

engagements = Engagement.where(influencer_authorization_id: current_influencer.id).group("status")

But i am getting the following error

ERROR: column "engagements.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "engagements".* FROM "engagements" WHERE "engagements... ^

Update

I tried doing the following but still doesnt work

engagements = Engagement.select("engagements.*").where(influencer_authorization_id: current_influencer.id).group("engagement.status")

ERROR

ERROR: missing FROM-clause entry for table "engagement" LINE 1: ...ents"."influencer_authorization_id" = $1 GROUP BY engagement... ^

1
ruby-on-railsrubypostgresqlruby-on-rails-4
Enumerable#group_by is very different from SQL's GROUP BY. group_by is for, more or less, building arrays whose elements are the same with respect to a block. GROUP BY is for grouping rows so that an aggregate function (such as count, max, ...) can be applied to each group. – mu is too short

1 Answers

1
votes

Do you use postgres? The problem is that database can't decide what to show for e.g. 'id' column when 'status' is the same for several records. You can manually write select statement and use aggregate functions. I think this PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function and this GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function are related