Drizzle Team•6mo ago

how to make order work with selectDistinctOn in pg?

When I add order by to my query it gives error that "initial order by prop should be the same with distinct on prop", but i dont want first orderby parameter to be tasks.id. i want it to be as on the comment. Could you please let me know how i can achive it? ( And when i try to make the query with distincton a subquery, then use it in new one with orderby, i am getting this error: "error": "You tried to reference "assignees" field from a subquery, which is a raw SQL field, but it doesn't have an alias declared. Please add an alias to the field using ".as('alias')" method.")

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)))

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

how to make order work with selectDistinctOn in pg?