MYSQL - Left join to gather posts and likes/comments from database tables

Question

I'm currently making a feed page with the intensions of having all posts, likes and comments on one page. I have three different tables. One containing the posts, one containing the likes and one containing the comments.

All posts can be shown and displayed correctly. The problem however comes when a post is liked by 2 or more people, it also duplicates the post 2 or more times in the feed. Below is my code:

$findShouts = $pdo->prepare('
SELECT f.id
     , f.username
     , f.name
     , f.text
     , f.timestamp
     , l.likesUsername
     , c.commentsUsername
     , c.commentsName
     , c.commentsText
     , c.commentsTimestamp
  FROM feed f
  LEFT 
  JOIN feedLikes l
    ON l.likesFeedID = f.id 
  LEFT 
  JOIN feedComments c
    ON c.commentsFeedID = f.id 
 WHERE f.name IN (SELECT scoutingUsername 
                    FROM scout 
                   WHERE scoutedUsername =? 
                      OR scoutingUsername =?)  
 ORDER 
    BY f.timestamp DESC    
');

//execute query and variables
$findShouts->execute([$username, $username]);


if ($findShouts->rowCount() > 0)
    {    

//get the shouts for each scout
while($row = $findShouts->fetch(PDO::FETCH_ASSOC)){ 



$shoutID[]              = $row['id'];
$shoutUsername[]        = $row["username"];
$shoutName[]            = $row["name"];
$shoutText[]            = $row["text"]; 
$shoutTimestamp[]       = $row["timestamp"];
$likesUsername[]        = $row['likesUsername'];
$commentsUsername[]     = $row["commentsUsername"];
$commentsName[]         = $row["commentsName"];
$commentsText[]         = $row["commentsText"]; 
$commentsTimestamp[]    = $row["commentsTimestamp"];
} 


$shoutCount = count($shoutUsername);


for($indexShout=0; $indexShout < $shoutCount; $indexShout++) {

//show post data

}
}

There are only 2 posts in my feed database and in feedLikes there is two likes for the first post. However, the first post is displayed twice.

If i print $shoutUsername I'd expect only 2 results (one for each post), however it shows 3, as the 2 likes for the first post are creating 2 of the same post.

Thank you for any help.

Answer 1

You can concat all the data while selecting and parse it at later stage using PHP. Not the best way to achieve the task but worth a try:

SELECT f.id
     , f.username
     , f.name
     , f.text
     , f.timestamp
     , Group_concat(l.likesUsername order by l.likesUsername separator ',') as likesUsername
     , Group_concat(c.commentsUsername order by c.commentsUsername separator ',') as commentsUsername
     , Group_concat(c.commentsName order by c.commentsUsername separator ',') as commentsName
     , Group_concat(c.commentsText order by c.commentsUsername separator ',') as commentsText
     , Group_oncat(c.commentsTimestamp order by c.commentsUsername separator ',') as commentsTimestamp
  FROM feed f
  LEFT 
  JOIN feedLikes l
    ON l.likesFeedID = f.id 
  LEFT 
  JOIN feedComments c
    ON c.commentsFeedID = f.id 
 WHERE f.name IN (SELECT scoutingUsername 
                    FROM scout 
                   WHERE scoutedUsername =? 
                      OR scoutingUsername =?) 
Group by f.id                             // added group by 
 ORDER 
    BY f.timestamp DESC