0

I am getting this error, the query works in SQLite.

ActiveRecord::StatementInvalid (PG::GroupingError: ERROR:  COLUMN "receipts.is_read" must appear IN the GROUP BY clause OR be used IN an aggregate FUNCTION

Unsure what to do, it also occurs when I remove the coalesce part. I am not sure what else I can specify to make it work. Any ideas?

SELECT DISTINCT conversations.id, COALESCE(CASE WHEN receipts.is_read = 't' THEN 't' END, 'f') AS READ, conversations.updated_at, p1.nickname, p2.nickname
FROM conversations
INNER JOIN notifications ON notifications.conversation_id = conversations.id
INNER JOIN receipts ON receipts.notification_id = notifications.id
INNER JOIN profiles p1 ON p1.id = notifications.sender_id
INNER JOIN profiles p2 ON p2.id = receipts.receiver_id
WHERE (receipts.receiver_id = #{SELF.id} ) AND notifications.sender_id != #{SELF.id} OR (notifications.sender_id = #{SELF.id}) AND receipts.receiver_id != #{SELF.id}
GROUP BY conversations.id
ORDER BY conversations.updated_at DESC
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
bezzoon
  • 1,755
  • 4
  • 24
  • 52
  • Your version of Postgres? – Erwin Brandstetter Jul 04 '14 at 13:25
  • possible duplicate of [PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function](http://stackoverflow.com/questions/18061285/postgresql-must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-functi) – pozs Jul 04 '14 at 13:29
  • Your query will probably work if you remove `group by conversation_id` or when you remove `distinct` and add all other field names in `select` clause to `group by` – Tomas Greif Jul 04 '14 at 13:31

1 Answers1

1

You have an invalid mix of DISTINCT and GROUP BY and some un-grouped columns. After replacing the mess with DISTINCT ON (and some other clean-up) this would work, but it's unclear, what you actually want to achieve:

SELECT DISTINCT ON (c.id)
       c.id, COALESCE(r.is_read, FALSE) AS read
     , c.updated_at, p1.nickname AS nickname1, p2.nickname AS nickname2
FROM   conversations c
JOIN   notifications n USING (conversation_id)
JOIN   receipts      r USING (notification_id)
JOIN   profiles      p1 ON p1.id = n.sender_id
JOIN   profiles      p2 ON p2.id = r.receiver_id
WHERE (r.receiver_id =  #{SELF.id} AND n.sender_id <> #{SELF.id} OR
       r.receiver_id <> #{SELF.id} AND n.sender_id =  #{SELF.id})
ORDER  BY c.id, c.updated_at DESC;

This gets you the row with the latest conversations.updated_at out of each group sharing the same conversations.id. Explanation:
Select first row in each GROUP BY group?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @bezzoon: Oh, `id` was missing in `ORDER BY`, sorry. Tested in 9.3. You did replace `#{SELF.id}` with an actual id, right? Also, assuming `is_read` is an actual `boolean` column for lack of information. – Erwin Brandstetter Jul 04 '14 at 18:08
  • Brandlsetter "is_read" boolean DEFAULT 'f -- , and i am using 9.3.4, I put schema data here! And yeah I am using an actual ID = ) !! https://gist.github.com/hassanshaikley/75c3d45bc3c77635324d – bezzoon Jul 04 '14 at 18:18
  • 1
    @bezzoon: Some clients don't accept duplicate column names. I added aliases. – Erwin Brandstetter Jul 04 '14 at 18:21