'GroupingError: ERROR: column " " must appear in the GROUP BY clause or be used in an aggregate function
I am trying to create a list of unique patients who have left comments. The code words fine until I upload to heroku where it doesnt work in postgresql.
This is my Ruby .erb code to create the list:
<% @comments.group(:patient_id).each_with_index do |comment, index| %>
<div class="profile-post color-one">
<span class="profile-post-numb"><%= index+1 %></span>
<div class="profile-post-in">
<h3 class="heading-xs"><a><%= link_to "#{comment.patient.first_name} #{comment.patient.last_name}", comment_path(comment) %></a></h3>
<p>Lastest comment from <%= time_ago_in_words(comment.created_at) %> ago<i class="pull-right"><%= link_to "Edit", edit_comment_path(comment) %> <%= link_to "Delete", comment_path(comment), method: :delete, data: {confirm: 'Are you sure you want to delete'} %></i></p>
</div>
</div>
<% end %>
@comments is defined in the controller as:
def index
@comments = current_clinician.comments.order("created_at desc")
end
heroku logs give me this as the error message:
PG::GroupingError: ERROR: column "comments.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "comments".* FROM "comments" WHERE comments"."clini... ^ SELECT "comments".* *FROM "comments" WHERE "comments"."clinician_id" = $1 GROUP BY patient_id ORDER BY created_at desc
I have tried the solutions from a other SO questions, such as 20942477. Which said that I should add the field comments.id to my group clause:
<% @comments.group(:patient_id, :"comments.id").each_with_index do |comment, index| %>
This gets rid of the error on heroku but defeats the purpose of the group command - it no longer only shows unique patients but instead lists all of them.
I also tried the solutions from 1780893. Which said that I should change the ORDER BY:
@comments = current_clinician.comments.order("substring(created_at,1.8) desc")
Which gives this error locally:
SQLite3::SQLException: no such function: substring: SELECT "comments".* FROM "comments" WHERE "comments"."clinician_id" = ? ORDER BY substring(created_at,1.8) desc
I realize this is a common problem, I am inexperienced with SQL so I am having trouble getting the code so it will work in both my development and production environments. The answers I have read on SO aren't using Ruby to get SQL and go over my experience level.
Solution 1:[1]
You cannot combine SELECT *
with GROUP BY some_column
in Postgres because that's a contradiction (unless it selects from a single table and some_column
is its PK). All non-aggregated columns (used in the SELECT
, HAVING
or ORDER BY
clause outside an aggregate function) must be in the GROUP BY
list - where the primary key column(s) cover(s) all columns of a table. Else it would be undefined which value to pick from the aggregated set.
When
GROUP BY
is present, or any aggregate functions are present, it is not valid for theSELECT
list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.
A certain other RDBMS is known to play dirty tricks here and allow this and pick arbitrary values...
You seem to want a list of unique patients that have commented, with the latest comment each. The simplest way in Postgres is with DISTINCT ON
:
SELECT DISTINCT ON (patient_id) *
FROM comments
WHERE clinician_id = $1
ORDER BY patient_id, created_at DESC NULLS LAST;
But this won't fly with SQLite - which should not be in the loop to begin with. See:
NULLS LAST
is only relevant if created_at
can be NULL:
Details for DISTINCT ON
:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 |