Drizzle Team•3mo ago

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))

7 Replies

zerokelvinOP•3mo ago

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

DoggeSlapper•3mo ago

orderBy(sql`COALESCE(AVG(${reviewTable.rating}), 0) DESC`

orderBy(sql`COALESCE(AVG(${reviewTable.rating}), 0) DESC`

did u tr something like that?

zerokelvinOP•3mo ago

I think something like did work for me, yes, but I ended up just making a materialized view

DYELbrah•2mo ago

The ternary operators back to back is painful

zerokelvinOP•2mo ago

How would you do it instead?

Kuba•2mo ago

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" attribute

DYELbrah•2mo ago

Probably a switch statement? or just a separate function that returns asc or desc Would also be nicely testable

Gaming

Programming

How do I order by average rating?