Gossamer Forum
Quote Reply
Review's star average?
I've looked all over the boards and in the resource center and can't seem to find anything on this.

I am not using the ratings system on my links system, I am just using the reviews. What I was looking to do was show a star graphic for the average review rating. Since people can rate the site 1-5 stars when they do a review I figured this would be better than just a rating. I can get the rating to show on the link description, but I can not get an average for the review ratings.

Can anyone point me in the right direction. I've looked on the boards and tried modifying some of the subs I found but don't seem to have any luck.

Thanks for your time.
Quote Reply
Re: [webpixie] Review's star average? In reply to
You'll need a global for this:


Code:
average_rating => sub {
my $id = shift;
my ($avg) = $DB->table('Reviews')->select( 'AVG(Review_Rating)', { Review_LinkID => $id })->fetchrow_array;
$avg ||= 0;
return sprintf("%.2f", $avg);
}

and then on the link.html template put:

<%average_rating($ID)%>

That will give you a two decimal number.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Review's star average? In reply to
Great! This is just what I was looking for! :-)

However, this creates an average based on ALL review ratings, even ones that haven't been validated yet. To only do validated ones...

sub {
my $id = shift;
my ($avg) = $DB->table('Reviews')->select( 'AVG(Review_Rating)', { Review_LinkID => $id }, {Review_Validated => 'Yes'})->fetchrow_array;
$avg ||= 0;
return sprintf("%.2f", $avg);
}


Also, if you switch sprintf to %.0f, you can use

<img src="<%build_images_url%>/default/stars-<%average_rating($ID)%>.gif">

To show the average as stars, just like for each individual review.

Enjoy!

Stephen
Quote Reply
Re: [biketrials] Review's star average? In reply to
Anyone know how I could show the top 10 Links in a category based on the average of each link's reviews? I suppose if there's no easy way to do this, I might as well just hope that people actually take the time to rate links and show top 10 rated!

Thanks!

Stephen
Quote Reply
Re: [biketrials] Review's star average? In reply to
Something like:

Code:
sub {
my $cat_id = shift;
my $rel = $DB->table('Reviews', 'Links', 'CatLinks');
$rel->select_options('LIMIT 10', 'ORDER BY avg DESC');
my $links = $rel->select( ['Links.*', 'AVG(Review_Rating) AS avg'], { CategoryID => $cat_id })->fetchall_hashref;
my $output;
foreach my $link (@$links) {
$output .= "....";
}
return $output;
}

Might work (untested). =)

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Review's star average? In reply to
Thanks, Alex!

Trying to use it, I get:

Error: Can't call method "fetchall_hashref" on an undefined value at (eval 18) line 5. Environment:

...

GT::SQL::error = Failed to execute query: '
SELECT lsql_Links.*, AVG(Review_Rating) AS avg
FROM lsql_CatLinks, lsql_Links, lsql_Reviews
WHERE lsql_CatLinks.LinkID = lsql_Links.ID AND lsql_Reviews.Review_LinkID = lsql_Links.ID AND (lsql_CatLinks.CategoryID = 'HASH(0x857d2f4)')
LIMIT 10 ORDER BY avg DESC
' Reason: You have an error in your SQL syntax near 'ORDER BY avg DESC
' at line 5




Does this mean anything to you?

Thanks for your help!

Stephen
Quote Reply
Re: [biketrials] Review's star average? In reply to
Ok, upon more fiddling, it seems that what was needed was a GROUP BY clause.

So I did:

sub {
my $cat_id = shift;
my $rel = $DB->table('Reviews', 'Links', 'CatLinks');
$rel->select_options('GROUP BY lsql_Links.ID', 'ORDER BY avg DESC', 'LIMIT 10');
my $links = $rel->select( ['Links.*', 'AVG(Review_Rating) AS avg'], { CategoryID => $cat_id })->fetchall_hashref;

my $output;
foreach my $link (@$links) {
$output .= Links::SiteHTML::display('link_summary', $link);
}
return $output;
}



This seems to be working fine, but I don't really understand *WHAT* it does... lsql_Links.ID is always unique, so grouping by that column should have no effect on the selected columns. However, the errors it was giving was asking for a GROUP BY clause, so...

Next thing to do is change the global so I don't have to tell it which category it's in.

Cheers!
Quote Reply
Re: [biketrials] Review's star average? In reply to
Yes, you need the group by. Also:

(lsql_CatLinks.CategoryID = 'HASH(0x857d2f4)'

doesn't look right. It looks like you didn't pass a category id to the function.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Review's star average? In reply to
Hi Alex,

Thanks, yeah, the error code I posted was for when I wasn't passing a category id to the function... I just changed the global so it gets the category from the link's ID #, and only averages validated reviews... It's working great, thanks for your help!

sub {
my $tags = shift;
my ($cat_id, $cat_name) = each %{$DB->table('Links')->get_categories($tags->{ID})};
my $rel = $DB->table('Reviews', 'Links', 'CatLinks');
$rel->select_options('GROUP BY lsql_Links.ID', 'ORDER BY avg DESC', 'LIMIT 10');
my $links = $rel->select( ['Links.*', 'AVG(Review_Rating) AS avg'], { CategoryID => $cat_id, isValidated => "Yes" })->fetchall_hashref;

my $output;
foreach my $link (@$links) {
$output .= Links::SiteHTML::display('link_summary', $link);
}
return $output;
}
Quote Reply
Re: [Alex] Review's star average? In reply to
Great global but is it possible to mark it out of 10 instead of 5?

Thanks
Quote Reply
Re: [MJB] Review's star average? In reply to
Hi,

No, this would require a plugin as the 1 to 5 stars is set in the code.

Cheers,

Alex
--
Gossamer Threads Inc.