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:
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.
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.