Комментарии, похожие на Stackoverflow, голосование за SQL-запрос

Я разрабатываю систему комментирования, такую ​​как Stackoverflow или Disqus, где люди могут комментировать и голосовать за комментарии. Для этого у меня есть три таблицы: USERS, COMMENTS и VOTES.

Мне сложно понять, как написать запрос для подсчета голосов и определения того, проголосовал ли данный пользователь. Каждый пользователь может проголосовать только один раз за комментарий и не может проголосовать за свой комментарий. Кроме того, комментарии и голоса касаются не только одной темы. У каждой страницы есть своя собственная тема, поэтому при выполнении комментариев и голосований от GET до SELECT WHERE topic_id='X' также необходимо отразить в запросе.

вот данные моих таблиц, как выглядит мой запрос и что я надеюсь вернуть:

USERS table
user_id user_name   
 1         tim
 2         sue
 3         bill 
 4         karen
 5         ed

COMMENTS table
comment_id topic_id    comment   commenter_id
 1            1       good job!         1
 2            2       nice work         2
 3            1       bad job :)        3

VOTES table
 vote_id    vote  comment_id  voter_id
  1          -1       1          5
  2           1       1          4
  3           1       3          1
  4          -1       2          5
  5           1       2          4

SELECT users.*, comments.*, count(vote as totals), sum(vote=1 as yes), sum(vote=-1 as no),
my_votes.vote as did_i_vote, users.* as IM_THE_USER
from comments
join votes 
on comments.comment_id=votes.comment_id
join users
on comments.commenter_id=users.user_id
join votes as MY_votes
on MY_votes.voter_id=IM_THE_USER.user_id
where topic_id=1 and IM_THE_USER.user_id=1
group by comment_id

user_id user_name comment_id topic_id  comment   commenter_id  totals yes no did_i_vote
   1      tim          1        1      good job!     1           2     1   1    NULL  
   3      bill         3        1      bad job:)     3           1     1   0      1

mysql sql voting comments
person tim peterson    schedule 15.11.2012    source источник

Ответы (1)


arrow_upward
4
arrow_downward

SQL Fiddle для игры

select u.user_id, u.user_name,
       c.comment_id, c.topic_id,
       sum(v.vote) as totals, sum(v.vote > 0) as yes, sum(v.vote < 0) as no,
       my_votes.vote as did_i_vote
from comments c
join users u on u.user_id = c.commenter_id
left join votes v on v.comment_id = c.comment_id
left join votes my_votes on my_votes.comment_id = c.comment_id
                            and my_votes.voter_id = 1
where c.topic_id = 1
group by c.comment_id, u.user_name, c.comment_id, c.topic_id, did_i_vote;

Обновление: фиксированная группа по предложению

обновление от OP (Tim Peterson): исправлено общее количество и добавлен сам комментарий, возвращаемый запросом последний рабочий SQLFiddle

person Olaf Dietsche    schedule 15.11.2012
comment
привет @Olaf спасибо! Моя единственная проблема - как также вернуть комментарии, за которые не проголосовали. Я добавил comment_id = 4 в ваш SQLFiddle, но запрос не возвращает его. sqlfiddle.com/#!2/b562a/1 - person tim peterson; 15.11.2012
comment
@timpeterson Вы должны изменить внутреннее соединение на левое соединение. См. Измененный ответ и sqlfiddle. - person Olaf Dietsche; 15.11.2012