0 
SELECT p.*, u.user_id, u.user_name, 
       COUNT(c.comment_id) AS count, 
       COUNT(v.vote_post_id) / 
         (TIMESTAMPDIFF(MINUTE, v.vote_timestamp, SYSDATE()) + 1) AS rate
FROM posts AS p 
LEFT JOIN comments AS c ON (p.post_id = c.comment_post_id)
LEFT JOIN post_votes AS v ON (p.post_id = v.vote_post_id)
LEFT JOIN users AS u ON (p.postedby_id = u.user_id) 
GROUP BY p.post_id 
ORDER BY COUNT(v.vote_post_id) / 
     (TIMESTAMPDIFF(MINUTE, v.vote_timestamp, SYSDATE()) + 1) DESC

This is the script I'm working on. I don't have my db filled up very well for testing, but the first two results gets double up with comments. Can you see any obvious mistakes here? I have another version of the script that works fine here:

SELECT p.*, u.user_id, u.user_name, 
       COUNT(c.comment_id) AS count
    FROM posts AS p 
    LEFT JOIN comments AS c ON (p.post_id = c.comment_post_id) 
    LEFT JOIN users AS u ON (p.postedby_id = u.user_id) 
    GROUP BY p.post_id 
    ORDER BY COUNT(c.comment_post_id) / 
       (TIMESTAMPDIFF(MINUTE, p.post_timestamp, SYSDATE()) + 1) DESC
Improve this question
6
  • 5
    Which dbms? (Non ANSI SQL, like TIMESTAMPDIFF involved...) Commented Apr 16, 2015 at 12:30
  • 2
    what is exact output you want and what you getting specify that Commented Apr 16, 2015 at 12:30
  • 1
    It probably have something to do with that extra join in the first query Commented Apr 16, 2015 at 12:32
  • You have a one to many result in one of your joins. Commented Apr 16, 2015 at 12:33
  • I'm using a mysql db. My goal is to get the correct count of comments listed under the "AS count" Commented Apr 16, 2015 at 12:40

2 Answers 2

Reset to default
1

Multiple votes will cause your comments to duplicate. You want to do a sub-select on the post_votes table to get the total votes per post as a single value if you GROUP BY the vote_post_id.

Since COUNT is a reserved word, I don't recommend using it as a column name in your result set.

If you're just getting the comment count and not the comments themselves, then you'll want that in a sub-select, too, or you'll be doubling up on posts.

SELECT p.*, u.user_id, u.user_name, c.comment_count, 
    v.vote_count AS total_votes, 
    v.vote_count / (TIMESTAMPDIFF(MINUTE, p.post_timestamp, SYSDATE()) + 1) as votes_per_minute
FROM posts AS p 
LEFT JOIN (SELECT comment_post_id, COUNT(comment_post_id) AS comment_count FROM comments GROUP BY comment_post_id) AS c ON (p.post_id = c.comment_post_id)
LEFT JOIN (SELECT vote_post_id, COUNT(vote_post_id) AS vote_count FROM post_votes GROUP BY vote_post_id) AS v ON (p.post_id = v.vote_post_id)
LEFT JOIN users AS u ON (p.postedby_id = u.user_id) GROUP BY p.post_id 
ORDER BY v.vote_count / (TIMESTAMPDIFF(MINUTE, p.post_timestamp, SYSDATE()) + 1) DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

When I try to run this query I get "#1054 - Unknown column 'v.vote_timestamp' in 'order clause' ". Can you help with that too?
Oh, right - you want to order by that timestamp, and I was not including that as part of the sub-select. I'm not entirely sure what you're trying to accomplish with that calculation - is it votes per day? If so, then that calculation will need to be part of the sub-select, and then you can return it in the outer SELECT and also order by it.
Its supposed to be a list of all the posts, ordered by whichever has the most votes per minute since it was created. But i need to get the count of all comments related to the post too for php purposes. I'm not really good at this stuff, and it's getting very complicated.
In your non-working version that I changed, the ORDER BY on the outer query didn't have access to that field. If you just divide the total votes by the number of minutes since the post itself, I think you have your answer. I updated my answer to hopefully reflect this, although I don't have a way to run it.
Your last update did exactly what I needed, plus a little more. Didn't even think of mentioning I needed total_votes too! Perfect man, thanks.
|
0

Clearly you are using MYSQL becasue it is the only database that allows this type of group by. It is a bad choice 100% of the time to use it however. You should group by all the fields in the select that are not part of the aggregate functions the way all other databases require. THis is how a group by needs to work. This may clear your problem. It may not depending on the data. If you have multiple records in some of the joined tables and they happen to have differnt data in the some of the fields, you still may not get one record when you properly group. In that case, you need to write a derived table for the join or use an aggregate on the field that has more than one value to tell the database which value to use.

Improve this answer

1 Comment

Yeah... I don't really know much about mysql and I'm in way over my head now. You're probably right, and I'll have to read more about joins and group by. Thanks for the help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.