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.
Here's the executed sql
Notice the third parameter being just
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();
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();
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]
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]
{}.0 Replies