Is it possible to use the Query API to select many-to-many relations?

In prisma I can do this to select the entities from the "other side" of a many-to-many join:
export const select = {
id: true,
email: true,
name: true,
teams: {
select: {
teamId: true,
// 👇 here
team: {
select: {
orgId: true,
name: true,
},
},
},
},
};
export const select = {
id: true,
email: true,
name: true,
teams: {
select: {
teamId: true,
// 👇 here
team: {
select: {
orgId: true,
name: true,
},
},
},
},
};
I tried the same with Drizzle:
json{
columns: {
id: true,
email: true,
name: true,
},
with: {
teamConnections: {
columns: {
teamId: true,
// 💥 oops
},
},
}
}
json{
columns: {
id: true,
email: true,
name: true,
},
with: {
teamConnections: {
columns: {
teamId: true,
// 💥 oops
},
},
}
}
and it seems that Drizzle doesn't know about these? Is there a way to perform deep nesting or do I have to resort to using the Select API?
1 Reply
addamsson
addamsson3d ago
I tried to do the same with a select and it doesn't seem to group the results:
const result = await db
.select({
id: User.id,
email: User.email,
name: User.name,
roles: User.roles,
teams: {
teamId: Team.id,
orgId: Team.orgId,
name: Team.name,
},
})
.from(User)
.leftJoin(UserToTeam, eq(UserToTeam.userId, User.id))
.leftJoin(Team, eq(UserToTeam.teamId, Team.id))
.where(eq(User.id, userId));
const result = await db
.select({
id: User.id,
email: User.email,
name: User.name,
roles: User.roles,
teams: {
teamId: Team.id,
orgId: Team.orgId,
name: Team.name,
},
})
.from(User)
.leftJoin(UserToTeam, eq(UserToTeam.userId, User.id))
.leftJoin(Team, eq(UserToTeam.teamId, Team.id))
.where(eq(User.id, userId));
and value of result was:
[
{
id: '97426d60-3864-49f5-95a8-426829a42cc6',
email: 'hey@ho.com',
name: 'hey',
roles: [ 'User' ],
teams: {
teamId: 'b651be48-50b2-4fb2-946e-a961029679c2',
orgId: '02a114d0-326d-48f8-9293-1ca930bebfc1',
name: 'team 0'
}
},
{
id: '97426d60-3864-49f5-95a8-426829a42cc6',
email: 'hey@ho.com',
name: 'hey',
roles: [ 'User' ],
teams: {
teamId: 'ec0b8126-cabc-43d9-a63d-2ace43554aba',
orgId: '02a114d0-326d-48f8-9293-1ca930bebfc1',
name: 'team 01'
}
}
]
[
{
id: '97426d60-3864-49f5-95a8-426829a42cc6',
email: 'hey@ho.com',
name: 'hey',
roles: [ 'User' ],
teams: {
teamId: 'b651be48-50b2-4fb2-946e-a961029679c2',
orgId: '02a114d0-326d-48f8-9293-1ca930bebfc1',
name: 'team 0'
}
},
{
id: '97426d60-3864-49f5-95a8-426829a42cc6',
email: 'hey@ho.com',
name: 'hey',
roles: [ 'User' ],
teams: {
teamId: 'ec0b8126-cabc-43d9-a63d-2ace43554aba',
orgId: '02a114d0-326d-48f8-9293-1ca930bebfc1',
name: 'team 01'
}
}
]