How do I order by average rating?
I have a table of facilities and reviews for facilities which I can join via the facilityTable.id = review.facilityId columns. I want to select the top ten according to average reviews. How can I do that? Here's what I have so far:
7 Replies
This gets a value without error, but doesn't seem to be returning everything. It's returning facilities that have no reviews, but not the facilities with the highest average reviews
did u tr something like that?
I think something like did work for me, yes, but I ended up just making a materialized view
The ternary operators back to back is painful
How would you do it instead?
Another option is to use an additional field ex.:
averageRating
that you update on every new review. It doesn't adhere to the normalization rules, but does it job well and saves some computation from the database side
I mean, you have to compute it for every new review, but it avoid you from having to run AVG
aggregation on every retrieval of facilities, since it's just another "computed" attributeProbably a switch statement? or just a separate function that returns asc or desc
Would also be nicely testable