Sort CGBlog by CGFeedback Rating

[kendo451]

Due to client requests, I have had several times needed the ability to sort CGBlog posts by a custom field, like most viewed post, or highest rated post.

CGBlog doesn't have the sortby custom field functionality yet, so I wrote a couple of UDT's and here is a way to do it.  This example will use CGFeedback to take ratings for each article, and then make a summary template in CGBlog so you can sort by rating.

1. Create the following two User Defined Tags:

A. Name this one update_stats

Code: Select all

// Simple blog entry view's counter
global $gCms;
$db =& $gCms->GetDb();

// Check if CGBlog installed
if(!$gCms->modules['CGBlog']['object']) {

	return "CGBlog module not installed!";
}

// Check if parameter id given
$id = '';
if(isset($params['id'])) {
	
	$id = $params['id'];
} else {

	return "Error: no id given";
}
// Check if parameter count given
$count = 0;
if(isset($params['count']) && $params['count'] > 0) {	
	$count = $params['count'];
}

// Check if parameter avg given
$avg = 0;
if(isset($params['avg']) && $params['avg'] > 0) {
	$avg = $params['avg'];
} 

// Get fielddef_id from db if it exists, if failure, exit.
$query = "SELECT id FROM ".cms_db_prefix()."module_cgblog_fielddefs WHERE name=?";
$count_fielddef_id = $db->GetOne($query, array('count'));


if(!$count_fielddef_id) {

	return "Error: invalid field $count";
}


// Get fielddef_id from db if it exists, if failure, exit.
$query = "SELECT id FROM ".cms_db_prefix()."module_cgblog_fielddefs WHERE name=?";
$avg_fielddef_id = $db->GetOne($query, array('avg'));
if(!$avg_fielddef_id) {

	return "Error: invalid field $avg";
}

// Update count field
$record_exists = false;
$query = "SELECT value FROM ".cms_db_prefix()."module_cgblog_fieldvals WHERE cgblog_id =? AND fielddef_id=?";
$record_exists = $db->GetOne($query, array($id,$count_fielddef_id));
if($record_exists !== false) {

	$query ="UPDATE ".cms_db_prefix()."module_cgblog_fieldvals SET value=? WHERE cgblog_id=? AND fielddef_id=?";
	$db->Execute($query,array($count, $id, $count_fielddef_id));
	
} else {

	// If we didn't have anything in db yet, make new row.
	$now = trim($db->DBTimeStamp(time()), "'");
	$query = "INSERT INTO ".cms_db_prefix()."module_cgblog_fieldvals (cgblog_id,fielddef_id,value,create_date,modified_date) VALUES (?,?,?,?,?)";
	$dbr = $db->Execute($query, array($id, $count_fielddef_id, $count, $now, $now));
	
	// If this failed, its bad :(
	if(!$dbr) {
		die('FATAL SQL ERROR: '.$db->ErrorMsg().'<br/>QUERY: '.$db->sql);
	}	

}

// Update avg field
$record_exists = false;
$query = "SELECT value FROM ".cms_db_prefix()."module_cgblog_fieldvals WHERE cgblog_id =? AND fielddef_id=?";
$record_exists = $db->GetOne($query, array($id,$avg_fielddef_id));

if($record_exists !== false) {

	$query ="UPDATE ".cms_db_prefix()."module_cgblog_fieldvals SET value=? WHERE cgblog_id=? AND fielddef_id=?";
	$db->Execute($query,array($avg, $id, $avg_fielddef_id));

} else {

	// If we didn't have anything in db yet, make new row.
	$now = trim($db->DBTimeStamp(time()), "'");
	$query = "INSERT INTO ".cms_db_prefix()."module_cgblog_fieldvals (cgblog_id,fielddef_id,value,create_date,modified_date) VALUES (?,?,?,?,?)";
	$dbr = $db->Execute($query, array($id, $avg_fielddef_id, $avg, $now, $now));
	
	// If this failed, its bad :(
	if(!$dbr) {
		die('FATAL SQL ERROR: '.$db->ErrorMsg().'<br/>QUERY: '.$db->sql);
	}	

}
return;

B. Name this one sortby_rating

Code: Select all

global $gCms;
$smarty =& $gCms->GetSmarty();

if(isset($params['items'])) {
	
	$items = $params['items'];
} else {

	return "Error: no items given";
}


foreach ( $items as $pos => $entry ) 
	$tmp_array[$pos] = $entry->fieldsbyname['avg']->value;

arsort($tmp_array);

foreach ($tmp_array as $key => $value) 
	$return_array[] = $items[$key];

$smarty->assign('items' , $return_array );

return;

2. In CGBlog create two custom fields: "count" and "avg" both should be text fields that are public.

3. Create your CGBlog detail template so that it calls CGFeeback with two actions: summary, and default (submit form).

4. Create your CGBlog summary template so that it calls a CGFeeback rating template to show the rating and/or # of comments for each post.  At the end of the ratings template, put this tag:
{update_stats avg=$stats.avg count=$stats.count id=$entry->id}

5. In your CGBlog summary template that is for sorting by rating, put this tag at the very beginning:
{sortby_rating items=$items}

#Note: This will only work correctly if your CGBblog pagelimit is unlimited, because it only sorts the entries in the $items array.  If you want to sort by rating AND have pagination, you'll just have to wait until Calguy adds sort by custom field to the CGBlog module.

[flmm]

Thanks , your post also worked great with sorting custom news item fields.

[peterbisset]

This is exactly what I am looking for, but how would you apply this to Company Directory please? I tried following the steps here and applying to Company Directory but unfortunately it didn't work

[kdrummer]

I need to sort customfields in news module...
can apply this code? which lines need to change?

I'm trying, but with poor results...