php - How can i get records of id those are connected in multiple row value

Solution:

If you are running MySQL 8.0, a typical approach is to use a recursive query to traverse the hierarchical data structure. This produces the desired result with a single query, so this is much more efficient that iterating in php.

Copy code

The anchor of the recursive query selects the "first" comment of the post, which is we identify with condition{-code-2}. Then, the recursive part just follows the relationships until they exhaust.

This gives you a flat structure that lists all comment's posts, with an addition column named{-code-3} that indicates the depth of each element. You can use that information to handle the display in your application.

Demo on DB Fiddle:

 Id | User_id | type             | content | item_id | secondary_item_id | {-code-3}
--: | ------: | :     

Solution:

You can use the below code to get your output in SQL.

WITH comment_activity(Id, User_id, type, content, post_id, secondary_item_id)
AS
(Select   879 ,     1     ,  'activity_comment' ,  'Amazing' ,      833     ,  833 UNION
Select   907 ,     168   ,  'activity_comment' ,  'Great'   ,      833     ,  879  UNION 
Select   908 ,     1     ,  'activity_comment' ,  'Welcome' ,      833     ,  907  
)

Select post_id as post,
ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY ID) as RN, 
content as comment,
User_id,
CASE WHEN ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY ID) = 1
     THEN 'First_Comment (' + content + ')'
     ELSE 'Reply_' + CONVERT(VARCHAR(20),ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY ID) - 1) + '( ' + content + ')'
     END as Thread

from comment_activity
WHERE type = 'activity_comment'
AND post_id = 833
Copy code

Solution:

Actually Lajos Arpad's solution could work with some changes in the db. You add another column parent_item_id. Which is basically the first comment of that chain of comments. In your example it would be 879 for all the 3 rows.

Now this could work

select * from <table name> 
where item_id = <item_id> 
group by parent_item_id 
order by secondary_item_id asc
Copy code

Solution:

Let's first understand how we can load the raw data:

select Id, content, item_id, secondary_item_id
from table_one
where item_id = 833
order by item_id, secondary_item_id;
Copy code

The query above loads the comments on post 833 and orders them by the post id and the parent comment id. So far, so good. Now, assuming that you have the raw data stored in$rawResults, you can do the following:

$map = [];
$parsedResults = [];
foreach ($rawResults as $rawResult) {
    if ($rawResult["secondary_item_id"] === $rawResult["item_id"]) {
        $parsedResults[$rawResult["Id"]] = [
            "content" => $rawResult["content"],
            "children" => []
        ];
        $map[$rawResult["Id"]] = $parsedResults[$rawResults["Id"]];
    } else {
        $map[$rawResult["secondary_item_id"]]["children"][$rawResult["Id"]]= [
            "content" => $rawResult["content"],
            "children" => []
        ]
    }
}
Copy code

Now, you have a hierarchy of elements, the main levels are main comments, to which you can find the answers in the children item. You can now traverse your hierarchical array like

function getComments($parsedResults, $prefix = "--- ") {
    $output = "";
    foreach ($parsedResults as $parsedResult) {
        $output .= $prefix.$parsedResult["content"]."\n";
        if (count($parsedResult["children"])) $output .= getComments($parsedResult["children"], "---".$prefix);
    }
    return $output;
}
Copy code