DT
Drizzle Team•6mo ago
addamsson

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
addamssonOP•6mo 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',
    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',
    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',
    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',
    name: 'hey',
    roles: [ 'User' ],
    teams: {
      teamId: 'ec0b8126-cabc-43d9-a63d-2ace43554aba',
      orgId: '02a114d0-326d-48f8-9293-1ca930bebfc1',
      name: 'team 01'
    }
  }
]
Want results from more Discord servers?
Add your server