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 },
  });

1 Reply

GlorrinOP•13mo ago

simplified schema :

export const event = mysqlTable(
  "event",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    name: varchar("name", { length: 256 }).notNull(),
    start: timestamp("date").notNull(),
  }
);

export const character = mysqlTable(
  "character",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    name: varchar("name", { length: 256 }).notNull(),
    userId: varchar("userId", { length: 191 }).notNull(),
  }
);

export const eventCharacter = mysqlTable(
  "eventCharacter",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    eventId: varchar("eventId", { length: 191 }).notNull(),
    characterId: varchar("characterId", { length: 191 }).notNull(),
  }
);

export const eventCharacterSoftReserved = mysqlTable(
  "eventCharacterSoftReserved",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    name: varchar("name", { length: 256 }).notNull(),
    eventCharacterId: varchar("eventCharacterId", { length: 191 }),
  }
);

export const event = mysqlTable(
  "event",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    name: varchar("name", { length: 256 }).notNull(),
    start: timestamp("date").notNull(),
  }
);

export const character = mysqlTable(
  "character",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    name: varchar("name", { length: 256 }).notNull(),
    userId: varchar("userId", { length: 191 }).notNull(),
  }
);

export const eventCharacter = mysqlTable(
  "eventCharacter",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    eventId: varchar("eventId", { length: 191 }).notNull(),
    characterId: varchar("characterId", { length: 191 }).notNull(),
  }
);

export const eventCharacterSoftReserved = mysqlTable(
  "eventCharacterSoftReserved",
  {
    id: varchar("id", { length: 191 })
      .default(sql`uuid()`)
      .notNull(),
    name: varchar("name", { length: 256 }).notNull(),
    eventCharacterId: varchar("eventCharacterId", { length: 191 }),
  }
);

Gaming

Programming

need help to optimise a query