Glorrin Posts - Answer Overflow

Glorrin

Explore posts from servers

•Created by Glorrin on 11/23/2023 in #help

need help to optimise a query

[schema in first comment] I want to get an object like this:

const event = {
   id: event.id,
   name: event.name,
   eventCharacters: [{
     id: eventCharacter.id,
     character: { id: character.id, name: character.name },
     countSoftReserves: count(eventCharacterSoftReserved.*)
   }]
}

const event = {
   id: event.id,
   name: event.name,
   eventCharacters: [{
     id: eventCharacter.id,
     character: { id: character.id, name: character.name },
     countSoftReserves: count(eventCharacterSoftReserved.*)
   }]
}

I have an eventId, and a userId event should only be the one associated with eventId (that's easy) eventCharacters should be filtered with eventCharacters.character.userId = userId This I have no clue on how to do it with findFirst This is not a proper way to make a query but I hope it reprensents what I need I could go the select way but then I will have an array with event.name repeated many times and event characters repeated for each softReserves. I am not sure if I should do 2 or 3 queries and recombined or if only 1 would be fine Is there some kind of good practice guide around ? I tried this:

const raid = await db.query.event.findFirst({
    where: eq(event.id, eventId),
    with: {
      eventCharacters: {
        with: {
          eventCharacterSoftReserves: { columns: { id: true } }, // I only need the count of that but this is good enough for now
          character: { columns: { id: true, name: true } },
        },
        where: eq(character.userId, userId), // doesn't work, can't figure out how to filter on character
        columns: { id: true },
      },
    },
    columns: { id: true, name: true },
  });

const raid = await db.query.event.findFirst({
    where: eq(event.id, eventId),
    with: {
      eventCharacters: {
        with: {
          eventCharacterSoftReserves: { columns: { id: true } }, // I only need the count of that but this is good enough for now
          character: { columns: { id: true, name: true } },
        },
        where: eq(character.userId, userId), // doesn't work, can't figure out how to filter on character
        columns: { id: true },
      },
    },
    columns: { id: true, name: true },
  });

2 replies

DTDrizzle Team

•Created by Glorrin on 11/10/2023 in #help

Another SSL error on import

I have an existing database and I wanted to try drizzle on it. Database is from a lesser known provider and is impossible to find reliable help to access it, but unfortunately I cannot change it. I want to use drizzle-kit introspect:pg here is my drizzle.config.ts

import { type Config } from "drizzle-kit"

import { env } from "@/env.mjs"

export default {
  schema: "./src/server/db/schema.ts",
  driver: "pg",
  dbCredentials: {
    connectionString: `${env.DATABASE_URL}?sslmode=prefer&ssl={"rejectUnauthorized":true, "ca": "${env.DATABASE_CA}"}`,
  },
} satisfies Config

import { type Config } from "drizzle-kit"

import { env } from "@/env.mjs"

export default {
  schema: "./src/server/db/schema.ts",
  driver: "pg",
  dbCredentials: {
    connectionString: `${env.DATABASE_URL}?sslmode=prefer&ssl={"rejectUnauthorized":true, "ca": "${env.DATABASE_CA}"}`,
  },
} satisfies Config

I also tried connectionString: ${env.DATABASE_URL}?ssl{"rejectUnauthorized":true}, and prety much every permutation between those 2 I could find but I always have the same error :

node:internal/process/promises:289
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error: self-signed certificate in certificate chain
    at TLSSocket.onConnectSecure (node:_tls_wrap:1674:34)
    at TLSSocket.emit (node:events:515:28)
    at TLSSocket._finishInit (node:_tls_wrap:1085:8)
    at ssl.onhandshakedone (node:_tls_wrap:871:12) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'
}

Node.js v21.1.0

node:internal/process/promises:289
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error: self-signed certificate in certificate chain
    at TLSSocket.onConnectSecure (node:_tls_wrap:1674:34)
    at TLSSocket.emit (node:events:515:28)
    at TLSSocket._finishInit (node:_tls_wrap:1085:8)
    at ssl.onhandshakedone (node:_tls_wrap:871:12) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'
}

Node.js v21.1.0

I tried the answers from the other SSL posts in this discord but unfortunately nothing I tried worked.

1 replies

Gaming

Programming