zerokelvin Posts - Answer Overflow

zerokelvin

Explore posts from servers

CDCloudflare Developers PPrisma DTDrizzle Team

•Created by zerokelvin on 9/18/2024 in #help

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

3 replies

DTDrizzle Team

•Created by zerokelvin on 4/29/2024 in #help

Drizzle incorrectly building query with DEFAULT instead of $11 in insertion

Drizzle ORM built this query:

INSERT INTO "domains" (
    "name", 
    "formatted_name", 
    "root_name", 
    "tld", 
    "godaddy_auction_id", 
    "price", 
    "bid_count", 
    "auction_end_time", 
    "majestic_trust_flow", 
    "majestic_citation_flow", 
    "majestic_backlink_count", 
    "majestic_referring_domain_count"
) VALUES 
    ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, DEFAULT, DEFAULT), 
    ($11, $12, $13, $14, $15, $16, $17, $18, $19, $20, DEFAULT, DEFAULT), 
    ($21, $22, $23, $24, $25, $26, $27, $28, $29, $30, DEFAULT, DEFAULT), 
    ($31, $32, $33, $34, $35, $36, $37, $38, $39, $40, DEFAULT, DEFAULT), 
    ($41, $42, $43, $44, $45, $46, $47, $48, $49, $50, DEFAULT, DEFAULT)
ON CONFLICT ("name") DO UPDATE 
    SET "majestic_backlink_count" = excluded.majestic_backlink_count;

INSERT INTO "domains" (
    "name", 
    "formatted_name", 
    "root_name", 
    "tld", 
    "godaddy_auction_id", 
    "price", 
    "bid_count", 
    "auction_end_time", 
    "majestic_trust_flow", 
    "majestic_citation_flow", 
    "majestic_backlink_count", 
    "majestic_referring_domain_count"
) VALUES 
    ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, DEFAULT, DEFAULT), 
    ($11, $12, $13, $14, $15, $16, $17, $18, $19, $20, DEFAULT, DEFAULT), 
    ($21, $22, $23, $24, $25, $26, $27, $28, $29, $30, DEFAULT, DEFAULT), 
    ($31, $32, $33, $34, $35, $36, $37, $38, $39, $40, DEFAULT, DEFAULT), 
    ($41, $42, $43, $44, $45, $46, $47, $48, $49, $50, DEFAULT, DEFAULT)
ON CONFLICT ("name") DO UPDATE 
    SET "majestic_backlink_count" = excluded.majestic_backlink_count;

Here's the query:

    const rez = await db
      .insert(domains)
      .values(values)
      .onConflictDoUpdate({
        target: domains.name,
        set: buildConflictUpdateColumns(domains, [
          "auctionPrice",
          "bidCount",
          "auctionEndTime",
          "majesticTrustFlow",
          "majesticCitationFlow",
          "majesticBacklinkCount",
          "majesticReferringDomainCount",
        ]),
      })

    const rez = await db
      .insert(domains)
      .values(values)
      .onConflictDoUpdate({
        target: domains.name,
        set: buildConflictUpdateColumns(domains, [
          "auctionPrice",
          "bidCount",
          "auctionEndTime",
          "majesticTrustFlow",
          "majesticCitationFlow",
          "majesticBacklinkCount",
          "majesticReferringDomainCount",
        ]),
      })

Why do majestic_backlink_count and majestic_referring_domain_count not have values like $11? This is resulting in data being set to NULL instead of the number value I've specified. How do I fix this?

5 replies

DTDrizzle Team

•Created by zerokelvin on 9/18/2023 in #help

Why is my query so much slower when filter by all four latitude/longitude bounds than just three?

When I uncomment out any of the latitude / longitude filters, the response latency spikes from ~50ms to 2000ms. (a similar query on prisma runs in just ~60ms, even with all filters.). Does anyone have an idea why? Or how I could fix this? Unfortunately, migrating to PostGIS is not an option at the moment

  const facilities = await db.query.facility.findMany({
    orderBy: desc(facility.internalRating),
    limit: 1,
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });

  const facilities = await db.query.facility.findMany({
    orderBy: desc(facility.internalRating),
    limit: 1,
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });

11 replies

DTDrizzle Team

•Created by zerokelvin on 7/11/2023 in #help

Internal server error: Error connecting to database: fetch failed

I'm trying to send the following query to my db but it eventually times out, throwing an error:

  const currentCity = await db.query.city.findFirst({
    where: and(
      eq(city.slug, citySlug),
      eq(city.stateName, stateSlugToStateName(stateSlug))
    ),
  });

  const facilities = await db.query.facility.findMany({
    where: and(
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS),
      whereLevelOfCare(levelOfCare)
    ),
    limit: 20,
    orderBy: desc(facility.internalRating),
    with: {
      city: true,
      apartments: true,
      images: true,
      amenityToFacility: {
        with: {
            amenity: true
        }
      }
    },
  });

  const currentCity = await db.query.city.findFirst({
    where: and(
      eq(city.slug, citySlug),
      eq(city.stateName, stateSlugToStateName(stateSlug))
    ),
  });

  const facilities = await db.query.facility.findMany({
    where: and(
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS),
      whereLevelOfCare(levelOfCare)
    ),
    limit: 20,
    orderBy: desc(facility.internalRating),
    with: {
      city: true,
      apartments: true,
      images: true,
      amenityToFacility: {
        with: {
            amenity: true
        }
      }
    },
  });

If I comment out the facilities query, the city query returns just fine, so I don't think there's anything wrong with my env setup or connector code. This is also the same query that I previously sent via prisma (or at least, my best attempt at recreating it), and it worked there, so I don't think the query is failing due to its intrinsic size. Any ideas as to how to resolve this issue?

6 replies

DTDrizzle Team

•Created by zerokelvin on 7/7/2023 in #help

How do I use Drizzle both locally in node and deployed on Vercel Edge?

I'm having some trouble with Drizzle + Postgres, deployed to Vercel. I can either get it working when deployed to vercel edge with my db connector like:

  const connectionString = this.env.get("DATABASE_URL") as string;
  const pool = new Pool({ connectionString });
  const db = drizzle(pool);

  const connectionString = this.env.get("DATABASE_URL") as string;
  const pool = new Pool({ connectionString });
  const db = drizzle(pool);

But then it doesn't work locally in dev, with the following error:

Uncaught (in promise) Error: All attempts to open a WebSocket to connect to the database failed. Please refer to https://github.com/neondatabase/serverless#run-on-node

Uncaught (in promise) Error: All attempts to open a WebSocket to connect to the database failed. Please refer to https://github.com/neondatabase/serverless#run-on-node

(I tried following the instructions there doesn't seem to resolve the issue, and just introduces node dependencies into my app, which then can't run on Vercel Edge). If I instead just set it up with

const client = postgres(connectionString);
const db = drizzle(client);

const client = postgres(connectionString);
const db = drizzle(client);

Then it works locally in dev, but fails to build (with the same issue of node dependencies can't be deployed to Vercel Edge). This is all in the context of a Qwik app (but I don't think this detail impacts the issue). How can I resolve this issue so that it works both locally in node dev and also deployed to Vercel Edge?

1 replies

Gaming

Programming