Counting and ordering

[The Little Guy]

I have the following query:

 

SELECT *, c.uniq_id as cid, c.name as cname, c.date as cdate 
FROM comments c 
LEFT JOIN users u 
ON (c.owner = u.id) 
LEFT JOIN like_dislike_comment l
ON (l.commentID = c.uniq_id)
WHERE c.id = '$id' 
GROUP BY l.commentID
ORDER BY 
c.uniq_id DESC

 

The above query gets all comments for a particular page, each comment also has a like/dislike button 1 = like; 2 = dislike. for each comment (row) I want to have a count of the number of likes, and the number of dislikes of each comment. I then want to have the one with the MOST likes first, and after that in original order of post date. How would I write something like that?

 

Hope this makes sense.

[kickstart]

Hi

 

Think something like this would do it for you (not tested)

 

SELECT *, c.uniq_id as cid, c.name as cname, c.date as cdate, z.DislikeCount, y.LikeCount
FROM comments c
LEFT JOIN users u ON (c.owner = u.id)
LEFT JOIN like_dislike_comment l ON (l.commentID = c.uniq_id)
LEFT OUTER JOIN (SELECT commentID, COUNT(*) AS LikeCount
FROM like_dislike_comment 
WHERE LikeDislike = 1
GROUP BY c.uniq_id) y
LEFT OUTER JOIN (SELECT commentID, COUNT(*) AS DislikeCount
FROM like_dislike_comment 
WHERE LikeDislike = 2
GROUP BY c.uniq_id) z
WHERE c.id = '$id'
GROUP BY l.commentID
ORDER BY y.LikeCount DESC, c.Date

 

All the best

 

Keith

[The Little Guy]

Thanks! I'll give it a try when I have a chance!

[The Little Guy]

I am getting this error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE c.uniq_id = '$id'

GROUP BY l.commentID

ORDER BY y.LikeCount DESC, c.Date

L' at line 17

 

I am replacing $id with 29 when I run the code.

[kickstart]

Hi

 

Doh, missed off the ON clauses:-

 

SELECT *, c.uniq_id as cid, c.name as cname, c.date as cdate, z.DislikeCount, y.LikeCount
FROM comments c
LEFT JOIN users u ON (c.owner = u.id)
LEFT JOIN like_dislike_comment l ON (l.commentID = c.uniq_id)
LEFT OUTER JOIN (SELECT commentID, COUNT(*) AS LikeCount
FROM like_dislike_comment 
WHERE LikeDislike = 1
GROUP BY c.uniq_id) y ON c.uniq_id = y.commentID
LEFT OUTER JOIN (SELECT commentID, COUNT(*) AS DislikeCount
FROM like_dislike_comment 
WHERE LikeDislike = 2
GROUP BY c.uniq_id) z ON c.uniq_id = z.commentID
WHERE c.id = '3'
GROUP BY l.commentID
ORDER BY y.LikeCount DESC, c.Date 

 

All the best

 

Keith

[The Little Guy]

Do I have to Join in the subquery? It doesn't know what c.uniq_id is.

 

#1054 - Unknown column 'c.uniq_id' in 'group statement'

 

Thanks! Your fix fixed the first problem! I was trying for like 5 hours last night to fix that error. Thank You!

[kickstart]

Hi

 

Long day..........

 

 

SELECT *, c.uniq_id as cid, c.name as cname, c.date as cdate, z.DislikeCount, y.LikeCount
FROM comments c
LEFT JOIN users u ON (c.owner = u.id)
LEFT JOIN like_dislike_comment l ON (l.commentID = c.uniq_id)
LEFT OUTER JOIN (SELECT commentID, COUNT(*) AS LikeCount
FROM like_dislike_comment 
WHERE LikeDislike = 1
GROUP BY commentID) y ON c.uniq_id = y.commentID
LEFT OUTER JOIN (SELECT commentID, COUNT(*) AS DislikeCount
FROM like_dislike_comment 
WHERE LikeDislike = 2
GROUP BY commentID) z ON c.uniq_id = z.commentID
WHERE c.id = '3'
GROUP BY l.commentID
ORDER BY y.LikeCount DESC, c.Date 

 

When I first knocked the code up I did have a JOIN in the sub selects and then realised it wasn't needed so took it out. But forgot to change the column name in the group by.

 

All the best

 

Keith

[The Little Guy]

Sweet!

 

The only thing, is that it didn't grab all of the comments for that particular page, but I might beable to figure that out myself, but if you have a quick fix go ahead and post it!

 

Otherwise thank you!

[The Little Guy]

and I already figured it out!

 

Just had to remove this line:

GROUP BY l.commentID