Rails group order by user last created post or last created comment

Question

I have the following relationship, a discussion which has_many posts which has_many comments

I am trying to implement a personalised user dashboard which orders lastest discussions depending on the user very last change on a particular discussion, so if a user makes a post on a discussion, this discussion should be on the top of the dashboard and if a user makes a comment on any of the posts in a given discussion, this discussion should also now be on the top of his dashboard.

Until now i can only get them to work separately, so i am struggling to combine them.

Only order by last Post:

@discussions = Discussion.joins(:posts).where(posts: {user: current_user}).group('discussions.id').order('MAX(posts.created_at) DESC')

Only order by last Comment:

@discussions = Discussion.joins(:comments).where(comments: {user: current_user}).group('discussions.id').order('MAX(comments.created_at) DESC')

My Attempt to combine them which doesn't work:

@discussions = Discussion.joins(:posts,:comments).where(posts: {user: current_user}).where(comments: {user: current_user}).group('discussions.id').order('MAX(posts.created_at) DESC', 'MAX(comments.created_at) DESC')

This only works depending on what i write on the .order but both never work..

Any help will be much appreciated or any other way of achieving the purpose is also welcome!

Update:

A kind user helped me get a bit further but still not quite the results i am looking for

@discussions = Discussion.joins(:posts,:comments).where(posts: {user: current_user}).where(comments: {user: current_user}).group('discussions.id').order('GREATEST(MAX(posts.created_at), MAX(comments.created_at)) DESC')

The problem here is, that it only works if both a post and a comment exist within a discussion, but i want it to be if either is created the discussion list should be updated with the most recent changed discussion on top.

Thang · Answer 1 · 2020-02-12T02:45:36.660

1

query = """
SELECT discussions.*, MAX(sub2.MostRecentDate)
FROM discussions INNER JOIN (
  SELECT posts.id, posts.discussion_id,
    CASE
      WHEN sub1.MostRecentCommentDate IS NULL OR posts.updated_at >= sub1.MostRecentCommentDate THEN posts.updated_at
      ELSE sub1.MostRecentCommentDate
    END AS MostRecentDate
  FROM posts LEFT OUTER JOIN (
    SELECT comments.post_id, MAX(comments.updated_at) AS MostRecentCommentDate
    FROM comments
    WHERE comments.user_id = ?
    GROUP BY comments.post_id
  ) AS sub1
  ON sub1.post_id = posts.id AND posts.user_id = ?
) AS sub2
ON discussions.id = sub2.discussion_id
GROUP BY discussions.id
ORDER BY MAX(sub2.MostRecentDate) DESC
"""

Discussion.find_by_sql(Discussion.send(:sanitize_sql_array, [query, current_user.id, current_user.id]))

First, get maximum of updated_at on comments table grouped by post_id

SELECT comments.post_id, MAX(comments.updated_at) AS MostRecentCommentDate
FROM comments
GROUP BY comments.post_id AS sub1

Result:

|post_id|MostRecentCommentDate|
|-------|---------------------|
|  ...  |        ....         |

Next, left joins posts table with the result table above and compare the sub1.MostRecentCommentDate with posts.updated_at to get the final MostRecentDate

SELECT posts.id, posts.discussion_id,
  CASE
    WHEN sub1.MostRecentCommentDate IS NULL OR posts.updated_at >= sub1.MostRecentCommentDate THEN posts.updated_at
    ELSE sub1.MostRecentCommentDate
  END AS MostRecentDate
FROM posts LEFT OUTER JOIN (
...
) AS sub2

Result:

|discussion_id|MostRecentDate|
|-------------|--------------|
|     ...     |     ....     |

Finally, joins the discussion table with result table sub2.

SELECT discussions.*, MAX(sub2.MostRecentDate) 
FROM discussions 
INNER JOIN (
...
) AS sub2
ON discussions.id = sub2.discussion_id
GROUP BY discussions.id
ORDER BY MAX(sub2.MostRecentDate) DESC

Will give us a table of discussions order by sub2.MostRecentDate

Note:

The return value of find_by_sql() is an array not an ActiveRecord_Relation.

The reason of calling sanitize_sql_array is to safely pass parameter to the raw query. Reference here

edited Feb 12 '20 at 02:45

answered Feb 11 '20 at 11:05

Thang

811
1
5
12

This works, but it is the same for all users, i need it to be per user for example user1 last made a post or comment on discussion1, so his list would be like discussion1 - discussion 2 - discussion3 User2 last made a post or comment on discussion 3 so his list would be discussion3 - discussion1 - discussion3 – Steytz Feb 11 '20 at 19:49
I basically want to create a latest discussion list for every single user judging on their last post or comment, so maybe we could filter the post and comment user id with the current user id, but i dont know how, this query goes way beyond my current understanding :/ – Steytz Feb 11 '20 at 20:00
What are the foreign_key of User table on Comment and Post? – Thang Feb 12 '20 at 01:48
I assume the foreign_key is user_id for both the table, I have updated my answer. – Thang Feb 12 '20 at 02:29
Just tried the update, it somehow is still not working it will show the exact same list of discussions for all users, instead of making a list per user depending on their own changes.., so sad this is so hard to achieve :/ – Steytz Feb 12 '20 at 18:28
Are you sure you have `comments.user_id = ?` and `posts.user_id = ?`. Are the column name of user foreign key on `Comment` and `Post` `user_id`? – Thang Feb 13 '20 at 07:59
The following relationships are relevant a Discussion has_many_post, a post belongs to a discussion, a post has_many comments, a comment belongs to a post, a discussion has_many comments through posts, a User has_many posts, a post belongs to a user, a User has many comments , a comment belongs to a user – Steytz Feb 13 '20 at 09:15
I still dont know how can the result includes other users if you specify `comments.user_id = ?` and `posts.user_id` as in my latest update – Thang Feb 13 '20 at 10:50
Maybe on the way i am calling it on the controller method i am doing: @discussions = Discussion.find_by_sql(Discussion.send(:sanitize_sql_array, [query, current_user.id, current_user.id])) – Steytz Feb 13 '20 at 15:17
On console the correct user Id is shown, i wonder what am i doing wrong, i for instance did not quite get ''The return value of find_by_sql() is an array not an ActiveRecord_Relation'' part. – Steytz Feb 13 '20 at 18:30

Rails group order by user last created post or last created comment

1 Answers1