TT
0
SS
TTTT
MMMMM
T
existsgetfullJoinleftJoinconst dms = await q.dungeonMasters.findMany({
with: {
logs: {
with: {
character: {
columns: {
id: true,
name: true,
userId: true
}
}
}
}
},
where: (dms, { or, eq, and, exists }) =>
and(
id
? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
: or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
//Include the DM only if they have logs with associated characters
exists(
db
.select({ id: logs.id })
.from(logs)
.innerJoin(characters, eq(logs.characterId, characters.id))
.where(and(eq(logs.dungeonMasterId, dms.id)))
)
)
});const results = await db
.select({
name: BatchModel.name,
customer: CustomerModel
})
.from(BatchModel)
.fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
.fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
.groupBy(BatchModel.id)
.all();
const aggregated = aggregate({rows: results, id: "name", fields: {customers: "customer"}}) /**
* database response is something like:
* [
* { user: { id: 1, name: 'user1' }, group: { id: 1, name: 'group1' } },
* { user: { id: 1, name: 'user1' }, group: { id: 2, name: 'group2' } },
* { user: { id: 2, name: 'user2' }, group: { id: 1, name: 'group1' } }
* ]
*
* references:
* - https://orm.drizzle.team/docs/joins#left-join
* - https://orm.drizzle.team/docs/rqb#many-to-many
*/
const rows = await db
.select({
user: getTableColumns(users),
group: getTableColumns(groups),
})
.from(users)
.leftJoin(usersToGroups, eq(usersToGroups.userId, users.id))
.leftJoin(groups, eq(usersToGroups.groupId, groups.id));
/**
* reduce result is something like:
* {
* '1': { user: { id: 1, name: 'user1' }, groups: [ [Object], [Object] ] },
* '2': { user: { id: 2, name: 'user2' }, groups: [ [Object] ] }
* }
*
* references:
* - https://orm.drizzle.team/docs/joins#aggregating-results
*/
const result = rows.reduce<Record<number, { user: User; groups: Group[] }>>(
(acc, row) => {
const user = row.user;
const group = row.group;
if (!acc[user!.id]) {
acc[user!.id] = { user: user!, groups: [] };
}
if (group) {
acc[user!.id].groups.push(group);
}
return acc;
},
{}
);
/**
* transformed response is something like:
* [
* { id: 1, name: 'user1', groups: [ [Object], [Object] ] },
* { id: 2, name: 'user2', groups: [ [Object] ] }
* ]
*/
const transformedResponse = Object.values(result).map((ele) => ({
...ele.user,
groups: ele.groups,
}));