Querying a Self-Referential Comments Table to Find the Comments with Replies, Ordered by the Latest Replies?

In this article, we’ll explore how to query a self-referential comments table in Postgres to find the latest distinct root comments to which a group member has replied. We’ll also provide an explanation of the underlying concepts and SQL queries used.

Understanding the Table Structure

The problem presents us with two tables: comments and group_members. The comments table contains rows for each comment, including a foreign key parent_id that references another row in the same table. This establishes a parent-child relationship between comments, where a comment can have multiple replies.

The group_members table is used to reference the author of each comment via a foreign key group_member_id. We’re interested in finding the latest distinct root comments from a specific group member’s perspective.

Sample Data

For illustration purposes, let’s examine some sample data:

+----+-----------+-----------------+---------------------+
| id | parent_id | group_member_id |     created_at      |
+----+-----------+-----------------+---------------------+
| 1  | NULL      | 1               | 2023-08-01 12:00:00 |
| 2  | NULL      | 2               | 2023-08-01 12:00:01 |
| 3  | NULL      | 2               | 2023-08-01 12:00:02 |
| 4  | 3         | 1               | 2023-08-01 12:00:03 |
| 5  | 2         | 1               | 2023-08-01 12:00:04 |
| 6  | 1         | 1               | 2023-08-01 12:00:05 |
| 7  | 2         | 2               | 2023-08-01 12:00:06 |
| 8  | 2         | 1               | 2023-08-01 12:00:07 |
+----+-----------+-----------------+---------------------+

Initial Query Approach

The initial query attempt, as shown in the Stack Overflow post, attempts to use DISTINCT and ORDER BY to retrieve the latest replies. However, it doesn’t quite achieve the desired ordering by created_at.

SELECT DISTINCT ON (parent_id)
    parent_id,
    created_at
FROM
    comments
WHERE
    comments.group_member_id = 1
    AND comments.parent_id IS NOT NULL
ORDER BY
    comments.parent_id,
    comments.created_at DESC

Optimized Query Approach

The correct query, as provided by the Stack Overflow answer, uses a GROUP BY with a join back into the root comment. This ensures that we get the latest replies from each group member, ordered by their most recent reply date.

SELECT gm.name, r.id, r.group_member_id, r.parent_id, r.created_at,
       max(c.created_at) as last_reply_at
  FROM comments c
       JOIN group_members gm ON gm.id = c.group_member_id
       JOIN comments r ON r.id = c.parent_id
GROUP BY gm.name, r.id, r.group_member_id, r.parent_id, r.created_at
ORDER BY gm.name, max(c.created_at) DESC;

Explanation

The optimized query works as follows:

1. We join the comments table with the group_members table on the group_member_id foreign key.
2. We then join the resulting table with itself (the same comments table) on the parent_id foreign key, effectively linking each comment to its parent.
3. The GROUP BY clause groups the results by the group member’s name, the root comment’s ID, and other relevant columns.
4. The max(c.created_at) expression calculates the most recent reply date for each group member.
5. Finally, we order the results by the group member’s name and the most recent reply date in descending order (newest first).

This approach ensures that we get the latest distinct root comments from each group member, ordered by their most recent reply date.

Active Record or Arel Interpretation

For those familiar with Rails, this problem can be translated into an Active Record query using the following syntax:

Comment.joins(:group_member).where(group_member: { id: 1 }).select('group_members.name, comments.*',
                                                                                   'max(comments.created_at) as last_reply_at')
           .group('group_members.name, comments.id, comments.group_member_id, comments.parent_id, comments.created_at')
           .order('group_members.name, max(comments.created_at) DESC');

Or using Arel:

Comment.select(Comment.as(:comments), 'max(comments.created_at) as last_reply_at')
        .joins(Arel::TableJoin.new(COMMENT.table_name => :comments))
          .where(COMMENTS.group_member_id.eq(1))
          .group('COMMENTS.name, COMMENTS.id, COMMENTS.group_member_id, COMMENTS.parent_id, COMMENTS.created_at')
          .order('COMMENTS.name, max(comments.created_at) DESC');

Last modified on 2023-10-07