By Jesse Grosjean on

I'm trying to modify the using the voting.module so that it can display a sortable table listing each node's title along with the number of votes and average vote for that node. But I'm stuck and need some help to continue. Here's what I have so far (it's missing average vote and number of votes):

  $header = array(
    array('data' => t('title'), 'field' => 'n.title'),
    array('data' => t('changed'), 'field' => 'n.changed', 'sort' => 'desc'),
    array('data' => t('vote'), 'field' => 'v.vote'),
  );

  $sql = "SELECT n.nid, n.title,"
       . "   n.created, n.changed, v.vote"
       . " FROM {node} n"
       . " LEFT JOIN {votes} v ON n.nid = v.content_id  AND v.uid = $user->uid" 
       . " WHERE n.type = 'contribution'";
  $sql .= tablesort_sql($header);
  $result = pager_query(db_rewrite_sql($sql), 50);
    
  while ($row = db_fetch_object($result)) {
    $rows[] = array(l($row->title, "node/$row->nid"), date("Y-m-d", $row->changed), $row->vote);
  }
  
  if ($pager = theme('pager', NULL, 50, 0, tablesort_pager())) {
    $rows[] = array(array('data' => $pager, 'colspan' => 3));
  }

Here's the table that votes are stored in:

CREATE TABLE votes (
  voting_id int(10) unsigned NOT NULL auto_increment,
  content_type varchar(20) default NULL,
  content_id int(10) unsigned default NULL,
  vote tinyint(1) unsigned default NULL,
  uid int(10) unsigned default '0',
  timestamp int(11) default NULL,
  hostname varchar(128) default '0',
  PRIMARY KEY  (voting_id)
) TYPE=MyISAM;

Comments

benshell’s picture

benshell commented

You could just make your query get all the node IDs and then call this function with each value: voting_get_vote('node', $nid)

It returns an object with the current user's vote, the average vote, and the number of votes.

The advantage of this method is you won't have to change anything if the voting.module database structure changes (it may change a little in the next version: http://drupal.org/node/33629).

The disadvantage is that you won't be able to sort by the average vote or the number of votes.

rjl’s picture

rjl commented

Not that familiar with this module, but the following should provide some use

as you don't want to view a single vote, remove this line...

array('data' => t('vote'), 'field' => 'v.vote'),

as you do want to view a count of votes and an average of votes, add these two lines instead...

array('data' => t('count'), 'field' => 'v.vcount'),
array('data' => t('average'), 'field' => 'v.vavg'),

change your query to pull the vote count and vote average by node (this is assuming the 'vote' field is the field where the vote is stored)

this...

$sql = "SELECT n.nid, n.title,"
. " n.created, n.changed, v.vote"
. " FROM {node} n"
. " LEFT JOIN {votes} v ON n.nid = v.content_id AND v.uid = $user->uid"
. " WHERE n.type = 'contribution'";

becomes...

$sql = "SELECT n.nid, n.title,"
. " n.created, n.changed, count(v.vote) vcount, avg(v.vote) vavg"
. " FROM {node} n"
. " LEFT JOIN {votes} v ON n.nid = v.content_id"
. " WHERE n.type = 'contribution'"
. " GROUP BY n.nid, n.title, n.created, n.changed"
;

this line...

$rows[] = array(l($row->title, "node/$row->nid"), date("Y-m-d", $row->changed), $row->vote);

change to this line...

$rows[] = array(l($row->title, "node/$row->nid"), date("Y-m-d", $row->changed), $row->vcount, $row->vavg);

Hope that works for you

Jesse Grosjean’s picture

Jesse Grosjean commented

Thanks that's exactly what I needed.

jw867’s picture

jw867 commented

How is this code being used? Dropped into a generic Drupal page?

Thanks!

Jesse Grosjean’s picture

Jesse Grosjean commented

I'm using it in a module custom module to track user requests:

http://www.hogbaysoftware.com/beta/requests

But it could probably be dropped into a page and work fine too.

----------------------------------------------------------------
Jesse Grosjean
Hog Bay Software
www.hogbaysoftware.com