Improving the Content Rater
By Scott Mitchell
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:
|
This query is specific to my blog's database structure, which uses the .Text blog
engine. Each blog entry is stored in the
The SQL query retrieves the
On my blog I display the five highest rated articles using an unordered list (
Displaying a Ratings Report
To accomplish this I used a technique discussed in more detail in the FAQ Displaying
Data Grouped By Category at DataWebControls.com. The SQL query to get the needed
data is shown below:
blog_Content table. Each rating is stored in the
blog_Ratings table. The blog_Ratings table has a foreign key field called 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.
<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.
<%@ Control Language="c#" %>
<%@ OutputCache Duration="1800" VaryByParam="None" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
SqlConnection myConnection = new SqlConnection(Connection String);
SqlCommand myCommand = new SqlCommand("blog_TopRated", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
TopRatedDG.DataSource = myCommand.ExecuteReader();
TopRatedDG.DataBind();
myConnection.Close();
}
}
</script>
<h3>Top Rated Entries</h3>
<asp:Repeater runat="server" id="TopRatedDG">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li>
<a href='posts/<%# DataBinder.Eval(Container.DataItem, "ContentID") %>.aspx'>
<%# DataBinder.Eval(Container.DataItem, "Title") %>
</a>
(<%# DataBinder.Eval(Container.DataItem, "AvgRating", "{0:0.00}")%> |
<%# DataBinder.Eval(Container.DataItem, "TotalCount")%>)</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
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.
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