Jump to content

Left join


slj90

Recommended Posts

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 by slj90
Link to comment
Share on other sites

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).
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.