Dan,

I installed the newest version of the Fivestar Recommender (6.x-3 Alpha 2) -- and I'm getting the same issue that came up with the duplicate ratings.

What's the logic you're using to check for dupes? The dupes that I'm seeing (and I still don't know WHY) seem to be from someone changing their ratings REALLY quickly, so you get two different ratings in the system for the same uid/content_id combination. (in some cases, there's even 3).

I will be writing a script to clean these up, but I'd rather not have to run it as frequently as I run the recommender (which is pretty constant).

Cheers,
-Connor

Comments

angusmccloud’s picture

I verified it is still breaking on dupes. I wrote a quick script that deletes duplicates, and now it's running. Script is pasted below in case anyone else wants to grab it (this could almost definitely be done with less lines of code, I was just trying to get something together).


include_once 'includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

//////
////
// Delete duplicate ratings
////
//////
$q_dupe_people = "
	SELECT
		content_id
		, uid
		, count(*)
	FROM
		{votingapi_vote}
	WHERE
		content_type = 'node'
	GROUP BY
		content_id
		, uid
	HAVING
		count(*) > 1
";

$result = db_query($q_dupe_people);

$criteria = "";
$i = 0;
while($row = db_fetch_array($result)){
	$nid = $row['content_id'];
	$uid = $row['uid'];
	if($i > 0){
		$criteria .= " OR ";
	}
	$criteria .= "(content_id = $nid AND uid = $uid AND content_type = 'node')";
	
	$i += 1;
}

if($i > 0){
	$q_dupe_ratings = "
		SELECT
			vote_id
			, content_id
			, uid
		FROM
			{votingapi_vote}
		WHERE
			$criteria
		ORDER BY
			uid
			, content_id
			, vote_id
	";

	$result = db_query($q_dupe_ratings);
	
	$delete_criteria = "";
	$last_nid = "";
	$last_uid = "";
	$last_voteid = "";
	$i = 0;
	while($row = db_fetch_array($result)){
		$voteid = $row['vote_id'];
		$nid = $row['content_id'];
		$uid = $row['uid'];
		
		$flagged = 0;
		if($nid == $last_nid AND $uid == $last_uid){ // This is a dupe, but keep this row
			if($i > 0){
				$delete_criteria .= ", ";
			}
			$delete_criteria .= $last_voteid;
			
			$i += 1;
			$flagged = 1;
		}
//		print "<br/><br/>NID: $nid";
//		print "<br/>Last NID: $last_nid";
//		print "<br/>UID: $uid";
//		print "<br/>Last UID: $last_uid";
//		print "<br/>Flagged: $flagged"; 
				
		$last_voteid = $voteid;
		$last_nid = $nid;
		$last_uid = $uid;
	}
			
	$q_delete_dupes = "
		DELETE FROM
			{votingapi_vote}
		WHERE
			vote_id IN($delete_criteria)
	";
	
//	print $q_delete_dupes;
	db_query($q_delete_dupes);
	print "$i Dupe Ratings Deleted";
}else{
	print "No Duplicate Ratings";
}

//////
////
// Duplicate Ratings Deleted
// Time to check the cache for dupes
////
//////
print "<br/><br/>"; // For ourput reasons, just get some space in there

$q_dupe_nids = "
	SELECT
		content_id
		, function
		, count(*)
	FROM
		{votingapi_cache}
	WHERE
		content_type = 'node'
	GROUP BY
		content_id
		, function
	HAVING
		count(*) > 1
";

$result = db_query($q_dupe_nids);

$criteria = "";
$i = 0;
while($row = db_fetch_array($result)){
	$nid = $row['content_id'];
	$function = $row['function'];
	if($i > 0){
		$criteria .= " OR ";
	}
	$criteria .= "(content_id = $nid AND function = '$function' AND content_type = 'node')";
	
	$i += 1;
}

if($i > 0){
	$q_dupe_values = "
		SELECT
			vote_cache_id
			, content_id
			, function
		FROM
			{votingapi_cache}
		WHERE
			$criteria
		ORDER BY
			content_id
			, function
			, vote_cache_id
	";
			
	$result = db_query($q_dupe_values);
	
	$delete_criteria = "";
	$last_nid = "";
	$last_function = "";
	$last_voteid = "";
	$i = 0;
	while($row = db_fetch_array($result)){
		$voteid = $row['vote_cache_id'];
		$nid = $row['content_id'];
		$function = $row['function'];
		
		$flagged = 0;
		if($nid == $last_nid AND $function == $last_function){ // This is a dupe, but keep this row
			if($i > 0){
				$delete_criteria .= ", ";
			}
			$delete_criteria .= $last_voteid;
			
			$i += 1;
			$flagged = 1;
		}
//		print "<br/><br/>NID: $nid";
//		print "<br/>Last NID: $last_nid";
//		print "<br/>UID: $uid";
//		print "<br/>Last UID: $last_uid";
//		print "<br/>Flagged: $flagged"; 
				
		$last_voteid = $voteid;
		$last_nid = $nid;
		$last_function = $function;
	}
			
	$q_delete_dupes = "
		DELETE FROM
			{votingapi_cache}
		WHERE
			vote_cache_id IN($delete_criteria)
	";
	
//	print $q_delete_dupes;
	db_query($q_delete_dupes);
	print "$i Dupe Cache Values Deleted";
}else{
	print "No Duplicate Cache Values";
}

danithaca’s picture

cool. will take a look soon.