How to correctly import comments (especially the comments "thread" field)?

just out of curiosity, why are you not using comment_save() (drupal api) from within a drupal node ?

Ali Hammad Raza
WordsValley

If you're going to do it the raw SQL way (as I did) then you'll need to use the 'int2vancode' and possibly 'vancode2int'. A 'Vancode' must be a word to describe what is supposed to be in the 'thread' column.

http://api.drupal.org/api/function/int2vancode/5
http://api.drupal.org/api/function/vancode2int/5

Since these functions don't use any other drupal code you could just copy and paste them into any scripts of your own. Worked for me.

I know it's been a while but I ran into a similar situation recently after merging a few sites and importing comments from disqus. At the end of the day I cobbled together this script to both rebuild the node_comments_statistics table and the comment.thread. As there's no clear cut solution posted here, I figured I'd post it incase someone else comes a looking. (Cross posted on my site http://seanreiser.com/projects/2009/06/06/rebuilding-drupal-comment-stat... )

db_query("UPDATE {comments} set thread = ''");

db_query("DELETE FROM {node_comment_statistics}");

$nodes = db_query("SELECT nid,uid,changed FROM {node} WHERE status=1");
while ($m = db_fetch_object($nodes)) {
    $nid = $m->nid;

    // Calculate node_comment_statistics

    $comments=db_query("SELECT nid,max(cid) FROM {comments} WHERE status=0 AND nid=$nid GROUP by nid");
    if($l = db_fetch_object($comments)) {
        $cid = $l->{"max(cid)"};

        $lastcomments = db_query("SELECT uid,name,timestamp FROM {comments} WHERE cid=$cid");
        $n = db_fetch_object($lastcomments);
        $uid = $n->uid;
        $name = $n->name;
        $timestamp = $n->timestamp;
 
        $counts = db_query("SELECT count(*) FROM {comments} WHERE status=0 AND nid=$nid");
        $n = db_fetch_object($counts);
        $count = $n->{"count(*)"};
    } else {
        $timestamp = $m->changed;
        $name = NULL;
        $uid = $m->uid;
        $count = 0;
    }
    db_query("INSERT INTO {node_comment_statistics} (nid,last_comment_timestamp,last_comment_name,last_comment_uid,comment_count) VALUES ('$nid','$timestamp','$name','$uid','$count')");



    // Build Comment Threads for Node


    $comments=db_query("SELECT cid, pid FROM {comments} WHERE nid=$nid ORDER BY timestamp");
    
    $nodethread = 0;
    while ($comment = db_fetch_object($comments)){
                
        if ($comment->pid == '0'){
            $thread = int2vancode(++$nodethread) . "/";
        }
        else {
            
            $parent = _comment_load($comment->pid);
            // This is comment with a parent comment: we increase
            // the part of the thread value at the proper depth.

            // Get the parent comment

            // Strip the "/" from the end of the parent thread.
            $parent->thread = (string) rtrim((string) $parent->thread, '/');

            // Get the max value in _this_ thread.
            $max = db_result(db_query("SELECT MAX(thread) FROM {comments} WHERE thread LIKE '%s.%%' AND nid = %d", $parent->thread, $edit['nid']));

            if ($max == '') {
                // First child of this parent.
                $thread = $parent->thread .'.'. int2vancode(0) .'/';
              }
            else {
                // Strip the "/" at the end of the thread.
                $max = rtrim($max, '/');

                // We need to get the value at the correct depth.
                $parts = explode('.', $max);
                $parent_depth = count(explode('.', $parent->thread));
                $last = $parts[$parent_depth];

                // Finally, build the thread field for this new comment.
                $thread = $parent->thread .'.'. int2vancode(vancode2int($last) + 1) .'/';
            
            }
        }
        db_query("UPDATE {comments} set thread = '$thread' where cid = $comment->cid");
    }

}

Been working on migrating comments from Postnuke->Drupal 7 and had to modify Sean's script above - use at your own risk!

// Simple script to rebuild comment threads in Drupal D7 by l33tdawg@hackinthebox.org
// Run this using the 'Execute PHP Code' function inside the Devel Module 

	// Flush the comment_statistics table and update the comment thread to '' for all comments 

	db_query("UPDATE {comment} set thread = ''");
	db_query("DELETE FROM {node_comment_statistics}");

	echo ("SUCCESS! Table node_comment_statistics truncated and thread comments have been reset\n"); 
	
	// Grab the node ID, the user ID and the UNIX timestamp 
 
	$nodes = db_query("SELECT nid, uid, changed FROM {node} WHERE status=1");
	foreach ($nodes as $record) { 
	    $nid = $record->nid;
	 // Calculate node_comment_statistics

    	$comments=db_query("SELECT nid, max(cid) FROM {comment} WHERE status=1 AND nid=$nid GROUP by nid");
    	$l = $comments->fetchObject();  
		if ($l != NULL) {
 			    	$cid = $l->{"max(cid)"};
	        		$lastcomments = db_query("SELECT uid, name, created FROM {comment} WHERE cid=$cid");
		    	    $n = $lastcomments->fetchObject(); 
					$uid = $n->uid;
				    $name = $n->name;
			    	$timestamp = $n->created;
					$count_comment = db_query("SELECT status FROM {comment} WHERE status=1 AND nid=$nid");
					$count = $count_comment->rowCount(); 
        			}
	    else { 
					$cid = 0; 
	   		     	$timestamp = $record->changed;
			      	$name = NULL;
        			$uid = $record->uid;
        			$count = 0;
     	      } 		

	 // Start updating the node_comment_statistics table 
		
		$update_node_comment_statistics = db_insert('node_comment_statistics')->fields(array(
	    	'nid' => $nid,
    		'last_comment_timestamp' => $record->changed, 
    		'last_comment_name' => $name,
	    	'last_comment_uid' => $uid, 
    		'comment_count' => $count, 
    		))->execute();    			
	}
 	
	// Start building the comment threads for the nodes 
    
	$nodes = db_query("SELECT nid, uid, changed FROM {node} WHERE status=1");
	foreach ($nodes as $record) {
			$nid = $record->nid; 
		    $comments=db_query("SELECT cid, pid FROM {comment} WHERE nid=$nid ORDER BY created");
		    $nodethread = 0;
		    foreach ($comments as $comment) { 

    	    	   if ($comment->pid == '0'){
		       $thread = int2vancode(++$nodethread) . "/";
        		} else {

            		// This is comment with a parent comment so we need to increate the thread value at the proper depth 
		         $parent = comment_load($comment->pid); 

	             	// Get the parent comment for the thread start 
    	        	// Strip the "/" from the end of the parent thread.

			 $parent->thread = (string) rtrim((string) $parent->thread, '/');
    	    	    	$edit['nid'] = $record->nid; 

        	    	// Get the MAX VALUE in the current thread 
            
 $max = db_query("SELECT MAX(thread) FROM {comment} WHERE thread LIKE :string AND nid = :nid", array(':string' => $parent->thread,':nid' => $nid))->fetchField(); 
			
	                // Find the first child of this parent.
                         
			if ($max == '') {
              			$thread = $parent->thread .'.'. int2vancode(0) .'/';
              			}
            	   else {
                
	                // Strip the "/" at the end of the thread.
                
    		            $max = rtrim($max, '/');

            	    // We need to get the value at the correct depth.
                
                	 $parts = explode('.', $max);
		          $parent_depth = count(explode('.', $parent->thread));
		          $last = $parts[$parent_depth];

        	        // Finally, build the thread field for this new comment
                
            		  $thread = $parent->thread .'.'. int2vancode(vancode2int($last) + 1) .'/';
            
           		 }

        	     }	
		$update_thread = db_update('comment') 
		->fields(array('thread' => $thread)) 
		->condition('cid', $comment->cid, '=')
		->execute(); 
		
   	    	}
  } 
  	echo "SUCCESS! node_comment_statistics table has been rebuilt!\n";     	
	echo "SUCCESS! Comment threads have been rebuilt!\n\n"; 
	echo "ALL DONE!\n"; 	

// END-OF-FILE

Regards,
L33tdawg@hackinthebox.org

The above update scripts did a partial job for me, so I had to run these two commands to finish the job for the counts and user names:

update node_comment_statistics, users set last_comment_name=users.name where node_comment_statistics.last_comment_uid=users.uid;
update node_comment_statistics set comment_count=(SELECT COUNT(comment.nid) FROM comment WHERE node_comment_statistics.nid=comment.nid GROUP BY comment.nid);

I didn't update the cid field though yet in the comment table, but everything is appearing ok in Advanced Forum/Forum lists and counts.