Display current node rank from votes (voting api) in view

Question

As in Title. I am struggling by programming but with no luck. Mechanism of voting api is too complicated for me. How can I display current node rank (order number) by votes?

My view now:

Sort critera: (Vote results) Vote results: Function desc

Relationships Node: Vote results

When displaying all results it's have order number thanks to view php field module.

Display position in rank of current, single node is hell for me. Custom sql query doesn't work at all.

I am using voting api and rate module.

Please help.

EDIT

Since I am using +1/-1 I try to:

1. Sum +1 Votes
2. Sum -1 Votes
3. Subtract sum of -1 votes from sum of +1

Code:

$plus_rates = db_query("SELECT count(value) AS sum, content_id AS nid FROM votingapi_vote WHERE tag = 'vote' AND value_type = 'points' AND value = 1 GROUP BY content_id ORDER BY sum DESC, nid ASC");
                                         $minus_rates = db_query("SELECT count(value) AS sum, content_id AS nid FROM votingapi_vote WHERE tag = 'vote' AND value_type = 'points' AND value = -1 GROUP BY content_id ORDER BY sum DESC, nid ASC");


                     $pluses = array();
                                         $minuses = array();

                     while ( $row = db_fetch_object($plus_rates) ) {

                                            $pluses[$row->nid] = $row->sum;         

                     }

                                         $plus_rates = NULL;

                                         while ( $row = db_fetch_object( $minus_rates ) ) {

                                            $minuses[$row->nid] = $row->sum;

                                         }

                                         $minus_rates = NULL;

                                         $keys = array_keys($minuses);

                                         foreach($keys as $k) {

                                                if (array_key_exists($k, $pluses)) {

                                                        $pluses[$k] = $pluses[$k] - $minuses[$k];

                                                }

                                         }

                                         arsort($pluses, SORT_NUMERIC);

                                         firep($pluses, 'pluses');

$pluses is sorted DESC but order is totally different from my view display:

• Sort cirteria: (Vote results) Vote results: Function desc)
• Relationship Node: Vote results, Aggregation function: total score

EDIT 2

I try to query votingapi_cache directly but still order is totally different than in views:

SELECT value, content_id FROM votingapi_cache WHERE value_type = 'points' AND tag = 'vote' AND FUNCTION = 'sum' ORDER BY value DESC LIMIT 0 , 30

Answer 1

Check the comments on this question: Problem visualizing the rating value inside a view

It sounds like you probably haven't set an aggregate function on the Node: Vote Results relationship. You cab definitely display the total votes in a View without writing any of your own code.

Answer 2

votingapi_vote is not a table which you can access directly in views. If you use the data module, you should be able to make it accessable to views and create a view on it directly. It will be at a lower level than what you do through nodes but may be simpler for you.

Answer 3

Ok I made it,

First View settings (View for main rating listing):

Sort criteria:

(Vote results) Vote results: Value desc, Node: Nid asc

Code:

$result = db_query("SELECT c.content_id AS nid, c.value AS sum
                                                                FROM votingapi_cache AS c
                                                                INNER JOIN node AS n ON c.content_id = n.nid
                                                                WHERE c.value_type = 'points'
                                                                AND c.tag = 'vote'
                                                                AND c.function = 'sum'
                                                                AND n.type = 'profile'
                                                                AND n.status = 1
                                                                ORDER BY sum DESC , nid ASC
                                                        ");
                           $is_rated = FALSE; 

                                     while ($row = db_fetch_object($result)) {

                                        $counter++;

                                         if ($profile->nid == $row->nid) {

                                            $is_rated = TRUE;
                                          break;

                                         }

                                     }

In real it should be turn to temp table and cache too.