Introduction
In an earlier article of mine, Creating a Content Rater, I showed how to
create a content rater User Control like the one shown on the right. I implemented this content rate on
my blog, ScottOnWriting.NET, about two weeks ago and have since
had a number of readers rate various blog entries and leave comments.
As the number of ratings increased, I wanted to add some additional features for both myself and my visitors.
First, I wanted to list the highest rated entries on each page. This way, visitors would be just one click away
from any of the top rated entries. Second, I wanted some sort of Web-based interface that I could use myself
to quickly see the ratings and comments for each of the entries. This article discusses these two enhancements, and
builds upon the original article. (If you have not yet read the original article,
be sure to do so before continuing!)
Displaying the Highest Rated Items
After enough people have rated content, it would be nice to show everyone the top rated content. (For some reason,
MSDN doesn't do this, even though they have hundreds of people rating each article.) To accomplish this I first
created a stored procedure, blog_TopRated, that returned the five highest rated items that had at least 3 votes. (I didn't want
a blog entry with just one high vote to make it on the list...) The stored procedure's syntax follows:
SELECT TOP 5
ContentID,
Title,
AVG(CONVERT(decimal(4,2), Rating)) as AvgRating,
COUNT(*) as TotalCount
FROM blog_Ratings br
INNER JOIN blog_Content bc ON
br.ContentID = bc.ID
GROUP BY ContentID, Title
HAVING COUNT(*) > 2
ORDER BY AvgRating DESC
This query is specific to my blog's database structure, which uses the .Text blog
engine. Each blog entry is stored in the blog_Content table. Each rating is stored in the
blog_Ratings table. The blog_Ratings table has a foreign key field called ContentID
that associated a rating with a particular blog entry.
The SQL query retrieves the ContentID, Title, average Rating, and total number
of ratings for each entry that has three or more ratings. The HAVING clause is what ensures only
those entries with more than two ratings will be returned. For more information on the GROUP BY and
HAVING clauses, be sure to read: Using the
GROUP BY Clause and SQL: HAVING Clause, or
reference the SQL Books Online.
On my blog I display the five highest rated articles using an unordered list (<ul>). The title
of each entry is listed as a hyperlink. Also, the average rating and total number of ratings are displayed.
The following shows the complete source code and markup for the User Control. Note that the Repeater control was
used to render the unordered list.
A couple things to note here. First, the Repeater creates an unordered list by creating a <ul>
in the <HeaderTemplate> and a </ul> in the <FooterTemplate>.
In the a <ItemTemplate> an <li> element is created for each of the five highest
rated articles. A hyperlink is created, directing the user to posts/ID.aspx, where ID is
the unique identifier of the blog entry. (.Text uses URL
rewriting to map requests to posts/ID.aspx to a page that displays the specified blog entry...)
Following the hyperlink, the average rating and total number of ratings are displayed. The screenshot to the
right shows this "Top Rated Entries" section on ScottOnWriting.NET.
Displaying a Ratings Report
In addition to showing the visitors of my blog the five highest rated entries, I also wanted a page I could visit
that would display all of the ratings. I wanted this report grouped by blog entry, showing all of the ratings
for that particular entry beneath it. The screenshot below, which shows a screenshot of the actual screenshot of the
page I created, illustrates the look I was after:
SELECT bc.ID,
bc.Title,
bc.DateAdded as DateEntryAdded,
br.DateAdded,
br.Rating,
br.Comments
FROM blog_Ratings br
INNER JOIN blog_Content bc ON
bc.ID = br.ContentID
ORDER BY ID DESC, br.DateAdded DESC
In an earlier article of mine, 
A couple things to note here. First, the Repeater creates an unordered list by creating a 