Drizzle Team•22h 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))

2 Replies

zerokelvin•22h 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•20h ago

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

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

` did u tr something like that?

Gaming

Programming

How do I order by average rating?