Drizzle TeamDT
Drizzle Team•2y ago
G$Row

How to use subquery in inArray?

I'm trying to use a subquery to populate an IN statement. There is no typescript error but it doesn't work.
Here's my code. 
const clinicRolesSQ = ctx.db
        .select({ id: ClinicRole.id })
        .from(ClinicRole)
        .innerJoin(ClinicUserRole, eq(ClinicRole.id, ClinicUserRole.roleId))
        .where(eq(ClinicUserRole.userId, ctx.session.user.id))
        .as("rolesSQ");

      const tasks = await ctx.db
        .select({
          count: sql<number>`count(DISTINCT Task.id) over()`.as("count"),
          task: Task,
        })
        .from(Task)
        .leftJoin(AssignedTask, eq(Task.id, AssignedTask.taskId))
        .where(
          and(
            eq(Task.clinicId, clinicId),
            input.completed
              ? isNotNull(Task.completedBy)
              : isNull(Task.completedBy),
            input.search
              ? or(
                  ilike(Task.name, `%${input.search}%`),
                  ilike(Task.description, `%${input.search}%`),
                )
              : undefined,
            or(
              eq(AssignedTask.userId, ctx.session.user.id),
              inArray(AssignedTask.roleId, clinicRolesSQ),
            ),
          ),
        )
        .orderBy(input.sortOrder === "desc" ? desc(sortBy) : sortBy)
        .limit(limit)
        .offset(offset)
        .execute();

Here's the executed sql 
select count(DISTINCT Task.id) over() as "count", "task"."id", "task"."name", "task"."description", "task"."due_date", "task"."clinic_id", "task"."appointment_id", "task"."created_by", "task"."completed_at", "task"."completed_by", "task"."created_at", "task"."updated_at" from "task" left join "assigned_task" on "task"."id" = "assigned_task"."task_id" where ("task"."clinic_id" = $1 and "task"."completed_by" is null and ("assigned_task"."user_id" = $2 or "assigned_task"."role_id" in $3)) order by "task"."created_at" limit $4 -- params: ["id1", "id2", {}, 150]

Notice the third parameter being just {}.
Was this page helpful?