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:
const facilities = await db
.select({
id: facilityTable.id,
name: facilityTable.name,
averageRating: avg(reviewTable.rating).mapWith(Number),
totalReviews: count(reviewTable.id),
city: {
slug: cityTable.slug,
regionSlug: cityTable.regionSlug,
},
})
.from(facilityTable)
.innerJoin(cityTable, eq(facilityTable.cityId, cityTable.id))
.leftJoin(reviewTable, eq(facilityTable.id, reviewTable.facilityId))
.groupBy(facilityTable.id, cityTable.slug, cityTable.regionSlug)
.orderBy(
sortBy === SortBy.PRICE_LOW_TO_HIGH
? asc(facilityTable.minMonthlyRent)
: sortBy === SortBy.PRICE_HIGH_TO_LOW
? desc(facilityTable.maxMonthlyRent)
: sortBy === SortBy.RATING
? desc(avg(reviewTable.rating))
: // else SortBy.RECOMMENDED or undefined
desc(facilityTable.internalRating),
)
.limit(PAGE_SIZE)
.offset(PAGE_SIZE * (pageIndex - 1)) const facilities = await db
.select({
id: facilityTable.id,
name: facilityTable.name,
averageRating: avg(reviewTable.rating).mapWith(Number),
totalReviews: count(reviewTable.id),
city: {
slug: cityTable.slug,
regionSlug: cityTable.regionSlug,
},
})
.from(facilityTable)
.innerJoin(cityTable, eq(facilityTable.cityId, cityTable.id))
.leftJoin(reviewTable, eq(facilityTable.id, reviewTable.facilityId))
.groupBy(facilityTable.id, cityTable.slug, cityTable.regionSlug)
.orderBy(
sortBy === SortBy.PRICE_LOW_TO_HIGH
? asc(facilityTable.minMonthlyRent)
: sortBy === SortBy.PRICE_HIGH_TO_LOW
? desc(facilityTable.maxMonthlyRent)
: sortBy === SortBy.RATING
? desc(avg(reviewTable.rating))
: // else SortBy.RECOMMENDED or undefined
desc(facilityTable.internalRating),
)
.limit(PAGE_SIZE)
.offset(PAGE_SIZE * (pageIndex - 1))