2

I have a review type with a rating field that contains a number from 1 to 5. Now I'd like to calculate the average rating. Relevant schema:

    {
      name: 'rating',
      title: 'Rating',
      validation: Rule => Rule.required(),
      type: 'number'
    },

Sanity recently added arithmetic operations to GROQ, but I'm not sure how to use them and there's very little documentation.

  1. Is it possible to calculate the sum of all ratings using an arithmetic operation? If so, how?
  2. Can you also divide the sum by the number of all reviews in the same query?
jgillich
  • 71,459
  • 6
  • 57
  • 85

1 Answers1

2

I found a solution, quite simple really. You count each star individually and then sum them up and divide them by the total count.

"rating":
  (
    (count(*[_type=='review' && references(^._id) && rating == 1]) * 1) +
    (count(*[_type=='review' && references(^._id) && rating == 2]) * 2) +
    (count(*[_type=='review' && references(^._id) && rating == 3]) * 3) +
    (count(*[_type=='review' && references(^._id) && rating == 4]) * 4) +
    (count(*[_type=='review' && references(^._id) && rating == 5]) * 5)
  ) / count(*[_type=='review' && references(^._id)])

I'd imagine that this is somewhat expensive for Sanity to calculate, and it's a bit verbose, so I'm still interested in other solutions.

jgillich
  • 71,459
  • 6
  • 57
  • 85
  • Glad you figured it out! Your solution looks good, not sure how to improve upon it. As you suspect, with a huge amount of review documents, this may indeed become an expensive query. – thomax Apr 24 '19 at 08:09
  • Also, please consider checking off this as answered? Makes it easier for others to spot a solution. – thomax Apr 24 '19 at 08:10