Trying to pass a variable in .having()
My having clause is like this:
.having(
({ endAt }) =>
sql
date_trunc('day', ${subEndAt}) = CURRENT_DATE + INTERVAL ${sql${intervalDays}},
);
I am fetching endAt in the query, but I have multiple intervals like '3 days', '5 days'.
When I try passing intervalDays as string i get an syntax error, but instead of intervalDays if I set it as 3 days the query works fine.
Is there a way where we can pass it using a variable or do we need to create a separate query for each of the intervals?4 Replies
Hello, @Kaz ^v^ ! Could you please share the query and am I right that
intervalDays is an array of strings?export async function getExpiringUserSubscriptionsAtInterval(
interval: number,
) {
const db = getDatabase(process.env.DATABASE_URL!);
return await db
.select({
userId: users.id,
subEnd: sql<Date>MAX(${userSubscriptionDetails.endingAt}),
})
.from(users)
.innerJoin(
userSubscriptionDetails,
and(
eq(users.id, userSubscriptionDetails.userId),
isNull(userSubscriptionDetails.type),
),
)
.groupBy(
users.id
)
.having(({ endAt }) =>
sqldate_trunc('day', ${subEndAt}) = CURRENT_DATE + INTERVAL '${sql${interval} days'},
);
}
@solo here is the function with my query, i tried passing interval as a 3 and 3 days but both of them results in an error.
intervalDays is not an array.
My use case is i just want to use the same query but with different interval, eg: 3 days, 7 days, 10 days
but it works fine if i hardcode the interval before hand like,
.having(({ endAt }) =>
sqldate_trunc('day', ${subEndAt}) = CURRENT_DATE + INTERVAL '14 days'},
);
@solo any idea how to do it?@Kaz ^v^ try to update your query with this
CURRENT_DATE + ${days}::interval
this works, thanks @solo