equivalent code drizzle

how to make the equivalent query in drizzle?

const validThreadMessages = await db
    .selectDistinctOn([threadMessage.threadId], {
        id: threadMessage.id,
        threadId: threadMessage.threadId,
        createDate: threadMessage.createDate,
    })
    .from(threadMessage)
    .where(
        sql`${threadMessage.createDate} > current_date - interval '${interval} days`
    )
    .orderBy(desc(threadMessage.threadId), desc(threadMessage.createDate))

const validThreadMessages = await db
    .selectDistinctOn([threadMessage.threadId], {
        id: threadMessage.id,
        threadId: threadMessage.threadId,
        createDate: threadMessage.createDate,
    })
    .from(threadMessage)
    .where(
        sql`${threadMessage.createDate} > current_date - interval '${interval} days`
    )
    .orderBy(desc(threadMessage.threadId), desc(threadMessage.createDate))

equivalent sql code:

SELECT DISTINCT ON ("threadId")
    "id",
    "threadId",
    "message",
    "createDate"
FROM
    "threadMessage"
WHERE
    "createDate" > current_date - interval '20 days'
ORDER BY
    "threadId",
    "createDate" DESC;

SELECT DISTINCT ON ("threadId")
    "id",
    "threadId",
    "message",
    "createDate"
FROM
    "threadMessage"
WHERE
    "createDate" > current_date - interval '20 days'
ORDER BY
    "threadId",
    "createDate" DESC;

5 Replies

dream•4mo ago

i get the error when i try to inject the interval variable that is a number in the sql operator code so it doesnt work like this

const interval = 7 as number
sql`${threadMessage.createDate} > current_date - interval '${interval} days`

const interval = 7 as number
sql`${threadMessage.createDate} > current_date - interval '${interval} days`

but it works like this:

sql`${threadMessage.createDate} > current_date - interval '7 days`

sql`${threadMessage.createDate} > current_date - interval '7 days`

do you know what could be wrong @Mykhailo ?

Mykhailo•4mo ago

hey @dream. Let me find in discord, I think we already had this issue

dream•4mo ago

thanks man 🙂 i tried searching a bit here but couldnt find anything

Mykhailo•4mo ago

https://discord.com/channels/1043890932593987624/1213103155760468018 I think it is the same case. Could you try to explicitly cast ${interval}::interval? but I think your interval should be with 'days'

dream•4mo ago

it worked 🙂 thanks brother great help!!

Gaming

Programming

equivalent code drizzle