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

Drizzle Team•2y ago

terlan8892

how to make order work with selectDistinctOn in pg?

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

how to make order work with selectDistinctOn in pg?

how to make order work with selectDistinctOn in pg?

Similar Threads

Similar Threads

Similar Threads