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?
1 Answer
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;
-
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.– JandCommented Nov 9, 2015 at 13:51
-
1I think count(vote value) = count(votes given) for comment, at least for vote type number Commented Nov 9, 2015 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.– JandCommented Nov 9, 2015 at 14:43
storyor just particular one?