I need to merge the comment columns based on the row with the column name ID I had a SQL query in order to merge replies to the commented posts considering count values to the replied posts i.e, comment_counts. I require to display nested comments within a single thread from the news feed post.
Below is my SQL query
SELECT DISTINCT ft.ID as ID, ft.userid, ft.content, ft.timestamp, ft.comments as comment_counts, ftc.comment, ftc.timestamp as comment_timestamp, uq.username, uq.avatar
FROM users uq, feed_item ft
LEFT JOIN feed_item_comment ftc ON ftc.postid = ft.ID
LEFT JOIN user_friends uf ON uf.friendid = ftc.userid
LEFT JOIN users u ON u.ID = uf.friendid WHERE uq.ID = ft.userid AND ft.userid
IN
(SELECT u.ID FROM users u WHERE u.ID
IN (SELECT uf.friendid FROM user_friends uf WHERE uf.status = '2' AND uf.userid = '".$this->user->info->ID."')
OR
u.ID
IN (SELECT uf.userid FROM user_friends uf WHERE uf.status = '2' AND uf.friendid = '".$this->user->info->ID."')
OR
u.ID = '".$this->user->info->ID."'
) ORDER BY ft.ID DESC, ftc.timestamp DESC
Actual Result:
This was the result obtained from the above query
ID userid content timestamp comment_counts comment comment_timestamp username avatar
___________________________________________________________________________________________________________________________________________
3 1 This is manju 13:12:31 2 manju comment 13:17:31 manju 1698862132.png
3 1 This is manju 13:12:31 2 new cmt 14:00:15 manju 1698862132.png
2 14 How are you doing? 13:06:42 2 Fine 15:00:15 Nishanth default.png
2 14 How are you doing? 13:06:42 2 Not Good 15:05:10 Nishanth default.png
1 14 How are you? 14:07:00 2 Look Good 20:00:00 Nishanth default.png
1 14 How are you? 14:07:00 2 So I'm! 20:10:00 Nishanth default.png
For the single news feed post with multiple comments displaying in separate feeds with nested comments. Although nested comments were displaying repeatedly. But with separate news feed thread
Expected Result:
ID userid content timestamp comment_counts comment1 comment1_timestamp comment2 comment2_timestamp username avatar
_______________________________________________________________________________________________________________________________________________________
3 1 This is manju 13:12:31 2 manju comment 13:17:31 new cmt 14:00:15 manju 1698862132.png
2 14 How are you doing? 13:06:42 2 Fine 15:00:15 Not Good 15:05:10 Nishanth default.png
1 14 How are you? 14:07:00 2 Look Good 20:00:00 So I'm! 20:10:00 Nishanth default.png
I need to display only nested comments (not repeated) with only one single news post as a thread
For instance for single news feed with the message "How are you doing" as I have commented with "Fine" and "Not Good"
For the post "How are you doing" it is repeating twice. Because I have commented twice with the text "Fine" and "Not Good"
How should I need to prevent nested comments repeated twice within the single news feed as a thread.
By using GROUP_CONCAT(ftc.comment) as replies
ID userid content timestamp comment_counts comment comment_timestamp username avatar replies
__________________________________________________________________________________________________________________________________________________________________________
1 14 How are you? 14:07:00 2 Look Good 20:00:00 Nishanth default.png Look Good,Fine,Not Good,manju comment,new cmt,Look...
By using GROUP_CONCAT(ftc.comment) as replies
ID userid content timestamp comment_counts comment comment_timestamp username avatar replies
__________________________________________________________________________________________________________________________________________________________________________
1 14 How are you? 14:07:00 2 Look Good 20:00:00 Nishanth default.png Look Good,Fine,Not Good,manju comment,new cmt,Look...
How am I suppose to write the SQL query in order to merge the rows from results obtained in order to get the desired/expected results?
Try below using row_number() and conditional aggregation
select id, userid,content,timestamp,comment_count,
min(case when seq=1 then comment end) as comment1,
min(case when seq=1 then comment_timestamp end) as commenttime1,
min(case when seq=2 then comment end) as comment2,
min(case when seq=2 then comment_timestamp end) as commenttime2
from
(
select *,row_number() over(partition by id,userid order by comment_timestamp) as seq from
(SELECT DISTINCT ft.ID as ID, ft.userid, ft.content, ft.timestamp, ft.comments as comment_count, ftc.comment, ftc.timestamp as comment_timestamp, uq.username, uq.avatar FROM users uq,
feed_item ft
LEFT JOIN feed_item_comment ftc ON ftc.postid = ft.ID
LEFT JOIN user_friends uf ON uf.friendid = ftc.userid
LEFT JOIN users u ON u.ID = uf.friendid WHERE uq.ID = ft.userid AND ft.userid
IN
(SELECT u.ID FROM users u WHERE u.ID
IN (SELECT uf.friendid FROM user_friends uf WHERE uf.status = '2' AND uf.userid = 1)
OR u.ID IN
(SELECT uf.userid FROM user_friends uf WHERE uf.status = '2' AND uf.friendid = 1)
OR
u.ID = 1
)ORDER BY ft.ID DESC, ftc.timestamp DESC)X)Y
group by id, userid,content,timestamp,comment_count