Second Life of a Hungarian SharePoint Geek

February 3, 2010

Query items by rating values using CAML

It’s a common requirement to look up items having a minimum number of ratings and / or a minimum value of average rating. In this post I will show you how to create a method that does exactly this query.

When you enable rating for a list, two new fields are added to the list, the Rating (0-5) field, that is the average of the ratings, and the Number of Ratings field, that is just what its name suggests, the count of the ratings. Both of this fields are numeric, so you can create a simple CAML expression to query their value. It’ important to use the internal names of the fields in the expression. The internal names are not so simple in this case. Let’s see how a query that looks for items having at least a single rating and average rating above 4 looks like:

  1. <Where>
  2.   <And>
  3.     <Gt>
  4.       <FieldRef Name='Number_x0020_of_x0020_Ratings' />
  5.       <Value Type=Number'>{0}</Value>
  6.     </Gt>
  7.     <Gt>
  8.       <FieldRef Name='Rating_x0020__x0028_0_x002d_5_x0' />
  9.       <Value Type=Number'>{1}</Value>
  10.     </Gt>
  11.   </And>
  12. </Where>

Note the double underscore characters in the case of the internal name of the Rating (0-5) field.

Note also, that in this case we compare using the Gt comparison operator. If you wish the items having the specified vvalues to be included in the result, replace the operator with Geq.

The following sample method illustrates the query in action. The method has an SPList parameter to the list to search in, and two optional (nullable) integer parameters: one for the required number of ratings and the other one for the average rating. The last parameter is to indicate if the items having the minimum required values should be included in the result or not.

  1. private SPListItemCollection GetItemsByRating(SPList list, int? numberOfRatings, int? averageRating, bool includeMinimum)
  2. {
  3.     // at least one of the parameters specified
  4.     if ((numberOfRatings.HasValue) || (averageRating.HasValue))
  5.     {
  6.         String numberOfRatingsQuery = String.Empty;
  7.         String averageRatingQuery = String.Empty;
  8.         String camlOperator = includeMinimum ? "Geq" : "Gt";
  9.  
  10.         String fieldQueryFormat = String.Format(
  11.                 @"<{0}><FieldRef Name='{{0}}' /><Value Type=Number'>{{1}}</Value></{0}>",
  12.                 camlOperator);
  13.  
  14.         if (numberOfRatings.HasValue)
  15.         {
  16.             numberOfRatingsQuery =
  17.                 String.Format(fieldQueryFormat, "Number_x0020_of_x0020_Ratings", numberOfRatings.Value);
  18.         }
  19.  
  20.         if (averageRating.HasValue)
  21.         {
  22.             averageRatingQuery =
  23.                 String.Format(fieldQueryFormat, "Rating_x0020__x0028_0_x002d_5_x0", averageRating.Value);
  24.         }
  25.  
  26.         String fullQuery = String.Empty;
  27.  
  28.         // both parameters specified, we need an And clause
  29.         if ((numberOfRatings.HasValue) && (averageRating.HasValue))
  30.         {
  31.             fullQuery = String.Format(@"<Where><And>{0}{1}</And></Where>", numberOfRatingsQuery, averageRatingQuery);
  32.         }
  33.         else
  34.         {
  35.             // one, and only one of the query strings is empty
  36.             fullQuery = String.Format(@"<Where>{0}{1}</Where>", numberOfRatingsQuery, averageRatingQuery);
  37.         }
  38.  
  39.         SPQuery query = new SPQuery();
  40.         query.Query = fullQuery;
  41.         SPListItemCollection items = list.GetItems(query);
  42.         return items;
  43.     }
  44.     else
  45.     {
  46.         // no query parameter, return all items
  47.         return list.Items;
  48.     }
  49. }

You can use this query to get the topmost rated items of your lists. If you wish you can extend it even by an OrderBy operator to order the items by the ratings. You can order the items by either of these fields, or you can add a calculated column to your list with your own formula of  weighting by the values of the Rating (0-5) and the Number of Ratings fields. For example, create a calculated column called Weighted rating and set the formula as [Number of Ratings]*[Rating (0-5)] and type to Number.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: