G$Row
G$Row
Explore posts from servers
DTDrizzle Team
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
No description
5 replies