Postgres - Group by multiple columns, Rails

Question

I have a table named property_audit_version_histories. i am fetching the records using the following code

@version_logs = PropertyAuditVersionHistory
                    .includes(:property_audit_version, :user)
                    .where(property_audit_version_id: params[:id])

The result contain 3 records, 2 of which have the same action and user_id Now i need to group the records using the columns action, user_id

I am getting the following error when i try to group the records

@version_logs = PropertyAuditVersionHistory
                        .includes(:property_audit_version, :user)
                        .where(property_audit_version_id: params[:id])
                        .group("action, user_id")

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

Based on the thread PG::GroupingError: ERROR: column "events.id" must appear in the GROUP BY clause or be used in an aggregate function i have modified the code as follows

@version_logs = PropertyAuditVersionHistory
                    .includes(:property_audit_version, :user)
                    .group("property_audit_version_histories.id")
                    .where(property_audit_version_id: params[:id])
                    .group("action, user_id")

Now the error is gone but still the result is having 3 records. After grouping i expect only 2 records.

Any idea on how to fix this?

SELECT "property_audit_version_histories".* FROM "property_audit_version_histories" WHERE "property_audit_version_histories"."property_audit_version_id" = $1 GROUP BY property_audit_version_histories.id, action, user_id [["property_audit_version_id", 44]] — prajeesh, Mar 14 '18 at 12:18

GorillaApe · Answer 1 · 2018-03-14T12:57:19.857

0

You cant select all columns like mysql in postgresql when doing aggregrates.

So I guess this should work.

@version_logs = PropertyAuditVersionHistory    
                    .where(property_audit_version_id: params[:id])
                    .group("action", "user_id", "property_audit_version_id")
                    .select("user_id", "action", "property_audit_version_id")

I dont know how is your model but this should work. If you need more fields let me know

edited Mar 14 '18 at 12:57

answered Mar 14 '18 at 12:28

GorillaApe

3,611
10
63
106

The problem is that i need to include property_audit_version_histories.created_at in the select condition. Once i add that an error appears saying that created_at should be added in the group condition also. After adding that there will be 3 records. – prajeesh Mar 14 '18 at 12:50
Could you try with the new version (updated answer) ? It should work. @version_logs.each { |vl| vl.property_audit_version.created_at } – GorillaApe Mar 14 '18 at 12:57

score 0 · Answer 2 · answered Mar 15 '18 at 04:27

What you need to do is specify which columns you want with

  .select("user_id")

But for the columns you know will be the same use max(columnName) as columnName

for example:

.select("MAX(user_id) as user_id, action")

Make sure you are 100% sure that those columns will be the same value after grouping.

Postgres - Group by multiple columns, Rails

2 Answers2