How do I do nested WHERE statements inside relations of relations?

[dBianchii]

I had a prisma call query that looked like this:

return await ctx.prisma.appPermission.findMany({
    where: {
      appId: input.appId,
      TeamAppRole: {
        some: {
          teamId: ctx.session.user.activeTeamId,
        },
      },
    },
    select: {
      id: true,
      name: true,
      TeamAppRole: {
        select: {
          id: true,
        },
        where: {
          teamId: ctx.session.user.activeTeamId,
        },
      },
    },
  });

I am trying to convert it to drizzle, but I cannot find a way to add nested wheres inside relations of relations:

I want to understand, how can I filter for only TeamAppRoles that have a teamId of ctx.session.user.activeTeamId?

[dBianchii]

I tried doing this:

const teamAppRolesForActiveTeamId = await ctx.db
    .select({ id: schema.teamAppRoles.id })
    .from(schema.teamAppRoles)
    .where(eq(schema.teamAppRoles.teamId, ctx.session.user.activeTeamId))
    .then((res) => res.map((r) => r.id));

  const permissions = await ctx.db.query.appPermissions.findMany({
    where: (appPermission, { eq, and }) =>
      and(eq(appPermission.appId, input.appId)),
    with: {
      AppPermissionsToTeamAppRoles: {
        where: (appPermissionToTeamAppRole, { eq, and, inArray }) =>
          and(
            eq(
              appPermissionToTeamAppRole.teamAppRoleId,
              ctx.session.user.activeTeamId,
            ),
            inArray(appPermissionToTeamAppRole.teamAppRoleId, teamAppRolesForActiveTeamId),
          ),
        with: {
          TeamAppRole: {
            columns: {
              id: true,
            },
          },
        },
      },
    },
  });
  

Not only did it not work, but it has to do 2 database calls, which is not good.

[JohnAllenTech]

Also facing this issue. For now it looks like two calls is the only way to go which isnt ideal but Drizzle is young and improving rapidly

[dBianchii]

Ok, so I managed to do something like this:

const users = await ctx.db.query.users.findMany({
    where: (users, { eq, inArray }) =>
      inArray(
        users.id,
        ctx.db
          .select({ id: schema.usersToTeams.userId })
          .from(schema.usersToTeams)
          .where(eq(schema.usersToTeams.teamId, ctx.session.user.activeTeamId)),
      ),
    with: {
      TeamAppRolesToUsers: {
        where: (teamAppRolesToUsers, { inArray }) =>
          inArray(
            teamAppRolesToUsers.teamAppRoleId,
            ctx.db
              .select({ id: schema.teamAppRoles.id })
              .from(schema.teamAppRoles)
              .where(
                eq(schema.teamAppRoles.teamId, ctx.session.user.activeTeamId),
              ),
          ),
        with: {
          TeamAppRole: {
            columns: {
              appId: true,
              teamId: true,
              id: true,
              name: true,
            },
          },
        },
        columns: {
          teamAppRoleId: true,
          userId: true,
        },
      },
    },
    columns: {
      id: true,
      name: true,
      email: true,
      image: true,
    },
  });