I'm not quite sure if this is a bug or a support request. I want (A) to count the number of nodes of a certain content type ("song") that have votes on them and (B) to count the total number of votes on nodes of a certain content type (also "song"). In the votingapi_vote table there is a column called content_type but it only holds the value "node" and not the actual content type.

I'm using MySQL 5.0.45.

1) Is the value in content_type a bug, or is that to distinguish between votes on nodes and votes on... well, something else?
2) How can I count the number of songs that have been voted on?
3) How can I count the number of votes on songs?

I'm using this query to count the total number of votes (on all nodes):

  $result = db_result(db_query("SELECT vote_id FROM {votingapi_vote} WHERE 1 ORDER BY vote_id DESC LIMIT 0,1"));

I'm aware that I could iterate over each of the values in the content_id column and check if those nodes are a certain content type, but that seems like an unnecessary waste of resources, especially with a lot of votes in the database. Similarly, I could "SELECT DISTINCT content_id" and iterate over that.

Comments

eaton’s picture

eaton commented

1) Is the value in content_type a bug, or is that to distinguish between votes on nodes and votes on... well, something else?

Yep. VotingAPI is being used to cast votes on users, nodes, comments, and even other votes themselves (in an Amazon-style 'how useful was this review' mechanism. The use of 'content_type' as the field name is, admittedly, confusing.

2) How can I count the number of songs that have been voted on?

You'd have to build your own SQL query in that case. While it's a bit less than ideal, it could certainly be done.

3) How can I count the number of votes on songs?

Something like the following should work:

SELECT SUM(DISTINCT(n.nid)) FROM votingapi_cache vc
LEFT JOIN node n ON vc.content_id = n.nid AND vc.content_type = 'node'
WHERE node.type = 'song'

icecreamyou’s picture

icecreamyou commented

That doesn't quite do it, but it gets me on my way. I only learned today about having multiple tables in one query--that simplifies things a lot. :)

eaton’s picture

eaton commented
Status: Active » Closed (won't fix)

Support for the 5.x branch of VotingAPI has ended with the release of Drupal 7 and the upcoming release of VotingAPI 7.x-2.4.