Drizzle TeamDT
Drizzle Team•3y ago
Glorrin

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.*)
   }]
}

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 },
  });
Was this page helpful?