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
Glorrin
GlorrinOP14mo 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 }),
}
);
Want results from more Discord servers?
Add your server