I'm trying to sort by the newest messages first, including the sub-messages. For example, if someone adds a sub-comment to an old message at the bottom, it would be moved to the top.
Here is the code I'm using:
$comments = Comment::where('discussion_id' , $info['id'])->where('parent_id', 0)->orderby('id' , 'desc')->paginate(6);
if parent_id is 0, it is not a sub-comment.
Just order by created_at column.
$comments = Comment::where('discussion_id' , $info['id'])->where('parent_id', 0)->orderby('created_at' , 'desc')->paginate(6);
It's going to be a bit tricky to produce a query that will work well for you. Especially if you have to support a deeply nested hierarchy of comments (for example comments on Reddit).
Usually comments sections are loaded very often so even if you write this query it will be slow. For that reason, I suggest a solution with an additional table field followed by additional code when you're inserting new comments.
Add a new timestamp field to the comments table and call it touched_on. Every time you insert a new sub-comment update the touched_on of every parent comment in the hierarchy. Now you can edit your code like this:
$comments = Comment::where('discussion_id' , $info['id'])->where('parent_id', 0)->orderBy('touched_on' , 'desc')->paginate(6);
If your comments are not editable then you can even use updated_on column for this but that depends on your requirements.
With this approach, your inserts will be a bit slower but your query for loading comments is super simple and fast. I would take that trade-off any day. Also, if you really need deeply nested comment threads I would look into more robust structures (nested sets maybe) than only using parent_id.