G$Row Posts - Answer Overflow

G$Row

Explore posts from servers

•Created by G$Row on 7/23/2024 in #help

I'm getting a Postgres Error when running my drizzle query but not when running the generated sql

I have a drizzle query.

const staffDateExpression = sql<Date>`date_trunc(${timeFrame}, ${CalendarEvent.startTimeUTC})`;
  // 1. Calculate staffing costs
  const staffingCosts = await ctx.db
    .select({
      date: staffDateExpression.as("date"),
      value: sum(
        sql`(${CalendarEvent.endTimeUTC}::timestamp - ${CalendarEvent.startTimeUTC}::timestamp) * ${ClinicStaffProfile.costPerHour} / 3600`,
      ).as("value"),
    })
    .from(Appointment)
    .innerJoin(CalendarEvent, eq(Appointment.calendarEventId, CalendarEvent.id))
    .innerJoin(
      ClinicStaffProfile,
      eq(Appointment.providerId, ClinicStaffProfile.userId),
    )
    .where(
      and(
        eq(Appointment.clinicId, clinicId),
        between(CalendarEvent.startTimeUTC, startDate, endDate),
        isNotNull(ClinicStaffProfile.costPerHour),
      ),
    )
    .groupBy(staffDateExpression);

const staffDateExpression = sql<Date>`date_trunc(${timeFrame}, ${CalendarEvent.startTimeUTC})`;
  // 1. Calculate staffing costs
  const staffingCosts = await ctx.db
    .select({
      date: staffDateExpression.as("date"),
      value: sum(
        sql`(${CalendarEvent.endTimeUTC}::timestamp - ${CalendarEvent.startTimeUTC}::timestamp) * ${ClinicStaffProfile.costPerHour} / 3600`,
      ).as("value"),
    })
    .from(Appointment)
    .innerJoin(CalendarEvent, eq(Appointment.calendarEventId, CalendarEvent.id))
    .innerJoin(
      ClinicStaffProfile,
      eq(Appointment.providerId, ClinicStaffProfile.userId),
    )
    .where(
      and(
        eq(Appointment.clinicId, clinicId),
        between(CalendarEvent.startTimeUTC, startDate, endDate),
        isNotNull(ClinicStaffProfile.costPerHour),
      ),
    )
    .groupBy(staffDateExpression);

Which generates this query

select date_trunc($1, "calendar_event"."start_time_utc") as "date", sum(("calendar_event"."end_time_utc"::timestamp - "calendar_event"."start_time_utc"::timestamp) * "clinic_staff_profile"."cost_per_hour" / 3600) as "value" from "appointment" inner join "calendar_event" on "appointment"."calendar_event_id" = "calendar_event"."id" inner join "clinic_staff_profile" on "appointment"."provider_id" = "clinic_staff_profile"."user_id" where ("appointment"."clinic_id" = $2 and "calendar_event"."start_time_utc" between $3 and $4 and "clinic_staff_profile"."cost_per_hour" is not null) group by date_trunc($5, "calendar_event"."start_time_utc") -- params: ["day", "itt4i7h048d4xllh185k4txi", "2024-06-23T19:05:11.869Z", "2024-07-23T19:05:11.870Z", "day"]

select date_trunc($1, "calendar_event"."start_time_utc") as "date", sum(("calendar_event"."end_time_utc"::timestamp - "calendar_event"."start_time_utc"::timestamp) * "clinic_staff_profile"."cost_per_hour" / 3600) as "value" from "appointment" inner join "calendar_event" on "appointment"."calendar_event_id" = "calendar_event"."id" inner join "clinic_staff_profile" on "appointment"."provider_id" = "clinic_staff_profile"."user_id" where ("appointment"."clinic_id" = $2 and "calendar_event"."start_time_utc" between $3 and $4 and "clinic_staff_profile"."cost_per_hour" is not null) group by date_trunc($5, "calendar_event"."start_time_utc") -- params: ["day", "itt4i7h048d4xllh185k4txi", "2024-06-23T19:05:11.869Z", "2024-07-23T19:05:11.870Z", "day"]

When I run my js code I get the error

PostgresError: column "calendar_event.start_time_utc" must appear in the GROUP BY clause or be used in an aggregate function

but when I just run the query in Dbeaver it runs just fine. I'm very confused. I'm not sure if I made a mistake or if this is a bug. Seems like a bug though.

4 replies

TtRPC

•Created by G$Row on 5/3/2024 in #❓-help

How to get data type from onMutate function in useMutation

I'm doing optimistic updates using the technique which is documented here https://tanstack.com/query/latest/docs/framework/react/guides/optimistic-updates . I found that I'm using the same queries in multiple spots and want to extract the update code so that I don't have to copy and paste. I can't figure out the type of the data parameter in the onMutate function.

api.task.createTask.useMutation({
    onMutate: async (data) => {}})

api.task.createTask.useMutation({
    onMutate: async (data) => {}})

I've tried using type MutateData = Parameters<typeof api.task.createTask.useMutation>[0] . The problem is that I can't then access the ['onMutate'] because it doesn't exist on there. Does anyone know how to get this?

30 replies

DTDrizzle Team

•Created by G$Row on 4/18/2024 in #help

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

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]

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 {}.

1 replies

DTDrizzle Team

•Created by G$Row on 11/8/2023 in #help

Getting "never" type from querying a relation

5 replies

Gaming

Programming