zerokelvin
zerokelvin
Explore posts from servers
CDCloudflare DevelopersPPrismaDTDrizzle Team
PostsComments
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))
10 replies
PPrisma
Created by zerokelvin on 9/2/2024 in #help-and-questions
cacheStrategy in local dev
https://www.prisma.io/docs/accelerate/local-development#using-prisma-accelerate-client-extension-in-development-and-production specifies how to use prisma accelerate client extension in local development, but it's incomplete. Most of my prisma queries have the cacheStrategy property specified. This works fine in production with the accelerate client, but fails with the non-accelerate client. Is the best practice here to just go through and comment out 50+ instances of cacheStrategy before developing your app locally and then uncomment it later when pushing?
4 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
PPrisma
Created by zerokelvin on 3/29/2024 in #help-and-questions
[ACCELERATE] Timed out fetching a new connection from the connection pool
I'm using Prisma Accelerate with the @prisma/client/edge client and I'm getting the following error: 
10:48:02 AM [vite] Internal server error: 
Invalid `prisma.facility.count()` invocation:


Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 10)
10:48:02 AM [vite] Internal server error: 
Invalid `prisma.facility.count()` invocation:


Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 10)
In following the "more info" link, it specifies how to resolve this issue for non-Accelerate connection strings, but following this doesn't work for my Accelerate connection string. I tried adding just about every combination of -pooler, connection_limit=100, pgbouncer=true but none of them work (and I'm still getting the same connection limit number in the error). How do I resolve this? Unfortunately I'll probably need to remove Prisma accelerate if I'm unable to resolve this soon
2 replies
CDCloudflare Developers
Created by zerokelvin on 12/4/2023 in #general-help
Cloudflare batch image upload does not work
Has anyone succeeded in getting the batch image upload endpoint to work? I'm getting the following error: 
{
  result: null,
  success: false,
  errors: [
    {
      code: "103",
      message: "forbidden endpoint"
    }
  ],
  messages: []
}
{
  result: null,
  success: false,
  errors: [
    {
      code: "103",
      message: "forbidden endpoint"
    }
  ],
  messages: []
}
and it looks like some other people have the same issue: https://community.cloudflare.com/t/images-batch-api-gets-error-code-103-forbidden-endpoint/586747
1 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