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•2mo 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'
    }
  }
]

Gaming

Programming

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