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.

About these ads