Left join

[slj90]

I am using the following sql code to display a stream of statuses, I have the username/profile picture/status/likes working but I am now stuck on comments.

SELECT s.*, u.*, c.*, COUNT(l.likes_location_id) AS likeCount
FROM stream AS s
LEFT JOIN users as u ON (u.users_username = s.stream_username)
LEFT JOIN comments AS c ON ( c.comments_location_id = s.stream_id )
LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
ORDER BY s.stream_id DESC LIMIT 50

If a status has multiple comments the results only shows the first comment that was made. How can I get it to include all of them?

 

Also, how would I include this in the PHP loop, without having another loop?

Thanks,

Edited January 7, 2015 by slj90

[requinix]

Include how? How do you expect it to combine multiple rows of data into one single string?

[slj90]

Include how? How do you expect it to combine multiple rows of data into one single string?

 

I don't know. I guess it's not possible? How is this usually done? Thanks

[slj90]

Include how? How do you expect it to combine multiple rows of data into one single string?

I was going to use an if statement to display them.

Edited January 7, 2015 by slj90

[kicken]

For a strictly mysql answer, you could use GROUP_CONCAT to join all the comment texts together into a single string. You probably don't want to display them all as a single string and likely want other details such as who made the comment so that's not a great solution.

 

A better solution is to use a separate query to fetch the comment details and then reference them as you display the statuses. You'd have one query that pulls the status information and like count, then a second query that pulls the comments for each status. As you read the status information you'd store it into an array indexed by the status ID. Then as you read the comments you can locate the status by the ID and append the comments to that array.

 

After you've put together the comments and status information you'd loop the statuses and display them.

 

$sql = '
SELECT 
	s.stream_id
	, u.users_username
	, COUNT(l.likes_location_id) AS likeCount
FROM stream AS s
LEFT JOIN users as u ON (u.users_username = s.stream_username)
LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
GROUP BY s.stream_id
ORDER BY s.stream_id DESC 
LIMIT 50
';

$result = $db->query($sql);
$statuses = array();
foreach ($result as $row){
	$statuses[$row['stream_id']] = $row + array('comments' => array());
}

$idList = implode(',', array_keys($statuses));
$sql = '
SELECT
	c.comments_location_id
	, c.comment
FROM comments c
ORDER BY c.comments_location_id DESC 
WHERE c.comments_location_id IN ('.$idList.')
';

$result = $db->query($sql);
foreach ($result as $row){
	$id = $row['comments_location_id'];
	if (isset($statuses[$id])){
		$statuses[$id]['comments'][] = $row;
	}
}

foreach ($statuses as $status){
	//display status.
}

Side note: Don't use * in your select. Specify which fields you need (even if it's all of them).