Posted by frdesign on August 20, 2009 at 2:02pm
So I figured out how to use hook_comment to do things to each comment. For instance I'm using the Vote up/down module. Using hook_comment I check for content type so I can activate Vote Up Down only on the module types of my choosing, something that the module doesn't offer out of the box. Now I want the comments with the highest Vote Up Down rating to float to the top. Is there a hook or something I can use to change how comments are ordered when viewed?
Thanks
Comments
_
If the comments are displayed using Views, just edit the view and change the sort criteria...
Pete.
Pete.
http://adaptive.co.uk
Possible solution
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.
_
Looks OK to me; I've done similar things when Views 1 couldn't quite do what I wanted it to...
Pete.
Pete.
http://adaptive.co.uk
So close yet so far!
So I was able to capture the query before rendering by using hook_db_rewrite_sql(). This is the query being executed on my node to show comments.
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.picture, u.data, c.thread, c.status FROM {comments} c INNER JOIN {users} u ON c.uid = u.uid INNER JOIN {node_access} na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 39 AND na.realm = 'user_relationship_node_access_author'))) AND ( c.nid = %d AND c.status = %d )ORDER BY c.cid DESCUsing hook_db_rewrite_sql() I was able to add a join to the {votingapi_cache} table which has the voting data I want to order by. Excuse the messy code, just starting to get the hang of php.
<?php
/**
* 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))) {
if ($primary_table == 'c'
&& $primary_field == 'cid'
&& strpos($query, 'ORDER BY c.cid DESC'))
{
$nid = arg(1);
$node = node_load($nid);
if ($node->type == 'ft_question') {
$return['join'] = "LEFT JOIN {votingapi_cache} va on $primary_table.cid = va.content_id";
$return['where'] = "va.function = 'count' OR va.function IS NULL";
return $return;
}
}
}
}
?>
Now that I have the join I want to change the order by statement from the original query
ORDER BY c.cid DESCTO
ORDER BY va.value DESCIs this possible? (fingers crossed)
Thanks in advance to anyone who can help!!!
Alternate solution
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;
}
}
?>