Ordering comments by something other than date

Hey Pete,
I'm actually trying to alter the way comments are shown in a node. It doesn't look like it uses a view. But it does seem to use a query however, which when you think about it is really the same thing.

After some digging I found hook_db_rewrite_sql() which seems promising so far. I looked at the comments module and I think I found the query that dictates how the comments are ordered. Here's the snippet from the core comments module.

<?php

// Multiple comment view
      $query_count = 'SELECT COUNT(*) FROM {comments} c WHERE c.nid = %d';
      $query = 'SELECT c.cid as cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, c.homepage, u.uid, u.name AS registered_name, u.signature, u.signature_format, u.picture, u.data, c.thread, c.status FROM {comments} c INNER JOIN {users} u ON c.uid = u.uid WHERE c.nid = %d';

      $query_args = array($nid);
      if (!user_access('administer comments')) {
        $query .= ' AND c.status = %d';
        $query_count .= ' AND c.status = %d';
        $query_args[] = COMMENT_PUBLISHED;
      }

      if ($order == COMMENT_ORDER_NEWEST_FIRST) {
        if ($mode == COMMENT_MODE_FLAT_COLLAPSED || $mode == COMMENT_MODE_FLAT_EXPANDED) {
          $query .= ' ORDER BY c.cid DESC';
        }
        else {
          $query .= ' ORDER BY c.thread DESC';
        }
      }
      else if ($order == COMMENT_ORDER_OLDEST_FIRST) {
        if ($mode == COMMENT_MODE_FLAT_COLLAPSED || $mode == COMMENT_MODE_FLAT_EXPANDED) {
          $query .= ' ORDER BY c.cid';
        }
        else {
          // See comment above. Analysis reveals that this doesn't cost too
          // much. It scales much much better than having the whole comment
          // structure.
          $query .= ' ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))';
        }
      }

?>

I think if I can alter the query by adding a LEFT JOIN to the "votingapi_cache" table which has the total votes record and ORDER BY VOTE COUNT might do the trick. I would add a conditional statement so it only makes the change to my custom node type.

Does anyone see any potential issues with this approach, it I get it to work that is.

In case anyone ever runs into this, after more digging I found that hook_db_rewrite_sql() doesn't let me add ORDER BY statements which is rather surprising. I guess I've been spoiled by the amount of flexibility in other hooks. For anyone that might be interested I had to come up with a custom solution which is probably not ideal but it gets the job done. I wrote a small module which clones the query core uses to build the comment objects, I then changed it as desired, and forced the results of the new query to override the core results by using hook_comment().

Here's the code, I've been coding less than a year so I'm sure it could be better.

<?php
// $Id$

/**
 * @file
 * Makes comments that have most Vote Up Down votes float to the top.
 */


/**
 * Implementation of hook_db_rewrite_sql().
 */
function comments_float_up_db_rewrite_sql($query, $primary_table, $primary_field, $args){
  if (arg(0) == 'node' && is_numeric(arg(1))) {
    
    // Find the query that builds comment objects.
    if ($primary_table == 'c'
        && $primary_field == 'cid'
        && strpos($query, 'ORDER BY c.cid DESC')){
      
          // Load node to find out content type.
          $nid = arg(1);
          $node = node_load($nid);
          
          // Only replace query for content type that uses Vote Up Down module.
          if ($node->type == 'ft_question') {
            // Change query so it's joined to votingapi_cache table
            // and comments are ordered by up/down votes count so
            // those with more votes float to the top when page is loaded
            $old_query = array('c.pid, c.nid,', 'ON c.uid = u.uid', 'ORDER BY c.cid DESC');
            $updated_query = array('c.pid, c.nid, va.value,', "ON c.uid = u.uid LEFT JOIN {votingapi_cache} va ON c.cid = va.content_id AND (va.content_type = 'comment' OR va.content_type IS NULL) AND va.function = 'sum'", 'ORDER BY COALESCE(va.value,0) DESC');
            $query = str_replace($old_query, $updated_query, $query);
            
            // Since query is not passed by reference into this function
            // we have to run updated query and save resulting array of
            // comment objects into $_SESSION so we can use it when
            // we display them in the node.
            $captured_comments = array();
            $q_result = db_query($query, $nid);
            while ($value = db_fetch_object($q_result)) {
              $captured_comments[] = $value;
          }
          
          // Save comments into $_SESSION
          $_SESSION['ft_question']['comments'] = $captured_comments;
          $_SESSION['ft_question']['node'] = $nid;
          // Since we don't need original query don't return anything here.
          //return $return;
        }
    }

  }
}

/**
 * Implementation of hook_comment().
 */
function comments_float_up_comment(&$comment, $op) {
  switch ($op) {
    case 'view':
      
      // Check that we're still in the same node we started in
      // and that user has right permission
      if ($_SESSION['ft_question']['node'] == arg(1) && user_access('use vote up/down on comments')) {

        // Default comment order is wrong for this content type so we'll
        // replace with correct one previously saved onto $_SESSION
        $comment = $_SESSION['ft_question']['comments'][0];
        
        // Get rid of comment we just used so that the next comment we
        // get from $_SESSION is the next one in the order
        array_shift($_SESSION['ft_question']['comments']);
        
        // Once $_SESSION array is emptied unset it
        if (count($_SESSION['ft_question']['comments']) == 0) {
          unset($_SESSION['ft_question']['comments']);
        }
      }
      break;
  }
}
?>