1

In a D7 site, comments are voted using rate module.

I'm wondering how to get the rating of all comments of 'story' content using raw sql query?

Improve this question
2
  • of all nodes of contenty type story or just particular one? – Paul Bönisch Nov 9 '15 at 8:18
  • Ideally all of them. But even if I know how to make a query for one node, it should be easy to generalize. – Jand Nov 9 '15 at 12:49
1

Here you are SQL to get the total vote count for widget type number for each comment od node type "story"

SELECT c.cid,count(v.vote_id) FROM votingapi_vote v JOIN comment c ON v.entity_id=c.cid JOIN node n ON n.nid=c.nid WHERE n.type='story' GROUP BY v.entity_id;

If you want to get the total numer of votes for all "story" nodes then:

SELECT c.cid,count(v.vote_id) FROM votingapi_vote v JOIN comment c ON v.entity_id=c.cid JOIN node n ON n.nid=c.nid WHERE n.type='story'

Or conditional for voting type like you did:

SELECT c.cid, count(v.value) FROM votingapi_vote v JOIN comment c ON v.entity_id=c.cid JOIN node n ON n.nid=c.nid WHERE n.type='story' and v.value_type='points' GROUP BY v.entity_id;
Improve this answer
3
  • This is good hint, but what I want is the total value (not total number) of votes given to each comment, exactly as what users see on pages. When I run the above query it returns only one row while there are many comments. – Jand Nov 9 '15 at 13:51
  • 1
    I think count(vote value) = count(votes given) for comment, at least for vote type number – Paul Bönisch Nov 9 '15 at 14:31
  • Well, this one worked for me: SELECT c.cid, count(v.value) FROM votingapi_vote v JOIN comment c ON v.entity_id=c.cid JOIN node n ON n.nid=c.nid WHERE n.type='story' and v.value_type='points' GROUP BY v.entity_id; Please modify answer and I'll accept it. Thanks a lot. – Jand Nov 9 '15 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.