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.

Displaying dates in calculated columns in date format

When we have to use a DateTime field in a text-type calculated column (for example, appending the creation date after the „Created on” text), and would like to create the formula like

="Created on" & Created

then we might be a bit surprised on the result, as there will be a long decimal number appended to the text instead of the creation date. What’s that?

To make computing easier SharePoint stores dates as numbers, where 1 means 31.12.1899 and 39996 means 2.6.2009. The earliest date one can set on the SharePoint user interface is 1.1.1900.

Storing time values is even more interesting. That is the decimal fraction of the decimal number, where 0 means midnight, 0.5 means noun, and 22:54 is stored as 0,9544791667. So the decimal number mentioned above is the count of the days after 30.12.1899.

So far so good, but we don’t have the answer for the original question yet, how can we use this number that represents a date append to the text as date.

Fortunately, the TEXT function in calculated columns provides a solution for that. This function expect the first parameter to be a numeric value, just the one we have for the date, and a text pattern to format the numeric value. We can use the patterns similar to the ones for the .NET DateTime type, for example:

="Created on: " & TEXT([Created], "dd.MM.yyyy")

Well, that is much better so. But what happens when we have to work with a mandatory field, like the creation date, but a DateTime value that might be empty?

The 0 value is reserved just for this case, and 30.12.1899 will be displayed. That is not what we need, but what we should do exactly instead of appending this date, depends on the business needs. You can left the column empty or display another text, like „Not specified”.

For example:

=IF([YourDateField]=0, "Not specified", "Field value: " & TEXT([YourDateField], "dd.MM.yyyy"))