How to sort comments by a new field in the comment table?

Question

I'm writing a module which allows users to put comment on each paragraph.

I have added a new field to the comment table, called paragraph_id, and in the comment form I have added a field that asks users which paragraph do they want to put comment on.

So now all I have to do is to alter the comment query. So something like what sort comment module does, it alter the comment query so that they got sorted by date, now I have to sort them by paragraph_id field.

This is the sort_comment.module :

  <?php
  /* [email protected] - blackice78 */
 define('SORT_COMMENTS_OLDER_FIRST', 1); // default
 define('SORT_COMMENTS_NEWER_FIRST', 2);
 /* Alter node type form: allow user to decide comments sorting */
  function sort_comments_form_node_type_form_alter(&$form, $form_state) {
  $form['comment']['comment_default_sorting'] = array(
 '#title' => t('Sort'),
 '#type' => 'select',
 '#options' => array(1 => t('Older first'), 
                     2 => t('Newer first')),
 '#default_value' => variable_get('comment_default_sorting_' . $form['#node_type']-           >type, SORT_COMMENTS_OLDER_FIRST), 
    );
  }
 /* Alter comments query */
 function sort_comments_query_comment_filter_alter(QueryAlterableInterface $query){
 if ( ($node = $query->getMetaData('node')) && (get_class($query) == 'PagerDefault') ) {
  $sort = variable_get('comment_default_sorting_' . $node->type,           SORT_COMMENTS_OLDER_FIRST);
  if ($sort == SORT_COMMENTS_NEWER_FIRST) {
  $orderby =& $query->getOrderBy();  
  $expressions =& $query->getExpressions();
  if (isset($orderby['torder'])) { 
      unset($expressions['torder']);
      unset($orderby['torder']);
        $orderby['c.thread'] = 'DESC';
      } else {
       $orderby['c.cid'] = 'DESC';
     }
  }
}

So, does anybody know how can I sort the comments by paragraph_id field?

Answer 1

The sort_comments.module checks if a special value is set to use its' ordering:

if (isset($orderby['torder'])) { /* ... */ }

You likely want to set an sql expression like they do for your own module.

Then in sort_comments.module they just set an SQL order by or leave the default ordering by comment_id (cid):

// ...
$orderby['c.thread'] = 'DESC';
} else {
   $orderby['c.cid'] = 'DESC';
}

You likely want to mimic this functionality in your own vesrion of hook_QUERY_TAG_alter, a specific version of hook_query_alter.