const data = (await db
.selectDistinctOn([tasks.id], {
task: tasks,
taskLocation: {
projectId: taskLocations.projectId,
subCatId: taskLocations.projectSubCatId,
},
assignees: sql`
COALESCE((
SELECT array_agg(${assignees_x_tasks.assigneeId})
FROM ${assignees_x_tasks}
WHERE ${assignees_x_tasks.taskId} = ${tasks.id}
), '{}') AS assignees
`,
})
.from(tasks)
.leftJoin(assignees_x_tasks, eq(assignees_x_tasks.taskId, tasks.id))
.leftJoin(taskLocations, and(eq(taskLocations.taskId, tasks.id), eq(taskLocations.userId, clerkUser.userId)))
.where(
and(
or(eq(tasks.ownerId, clerkUser.userId), eq(assignees_x_tasks.assigneeId, clerkUser.userId)),
eq(taskLocations.projectSubCatId, subCatId)
)
)) as TaskType[];
// TODO ::: make order work with distinct .orderBy(tasks.id, asc(tasks.date), asc(tasks.time), asc(tasks.createdAt)))
const data = (await db
.selectDistinctOn([tasks.id], {
task: tasks,
taskLocation: {
projectId: taskLocations.projectId,
subCatId: taskLocations.projectSubCatId,
},
assignees: sql`
COALESCE((
SELECT array_agg(${assignees_x_tasks.assigneeId})
FROM ${assignees_x_tasks}
WHERE ${assignees_x_tasks.taskId} = ${tasks.id}
), '{}') AS assignees
`,
})
.from(tasks)
.leftJoin(assignees_x_tasks, eq(assignees_x_tasks.taskId, tasks.id))
.leftJoin(taskLocations, and(eq(taskLocations.taskId, tasks.id), eq(taskLocations.userId, clerkUser.userId)))
.where(
and(
or(eq(tasks.ownerId, clerkUser.userId), eq(assignees_x_tasks.assigneeId, clerkUser.userId)),
eq(taskLocations.projectSubCatId, subCatId)
)
)) as TaskType[];
// TODO ::: make order work with distinct .orderBy(tasks.id, asc(tasks.date), asc(tasks.time), asc(tasks.createdAt)))