Sort CGBlog by CGFeedback Rating
Posted: Tue Jun 15, 2010 2:12 am
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
B. Name this one sortby_rating
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.
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;
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;
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.
, your post also worked great with sorting custom news item fields.
