Postgres + WITH sql

I'm trying to do the following sql:

WITH v (id) AS (
    VALUES('448e2cc0-aa49-4339-babc-db8aec706ea6'),
        ('b7709b0a-5c38-436a-bad9-a7330890d7db'),
        ('41ca49eb-3550-48ca-8810-f0c03d2bf1c3'),
        ('87d2793d-95dc-40e4-9b9b-288c6a897422'),
        ('d6a5c825-c3b7-49c1-afdb-36edbc881927')
)
SELECT v.id
FROM v
LEFT JOIN "items" ON "items"."id" = v.id::uuid
WHERE "items"."id" IS NULL

WITH v (id) AS (
    VALUES('448e2cc0-aa49-4339-babc-db8aec706ea6'),
        ('b7709b0a-5c38-436a-bad9-a7330890d7db'),
        ('41ca49eb-3550-48ca-8810-f0c03d2bf1c3'),
        ('87d2793d-95dc-40e4-9b9b-288c6a897422'),
        ('d6a5c825-c3b7-49c1-afdb-36edbc881927')
)
SELECT v.id
FROM v
LEFT JOIN "items" ON "items"."id" = v.id::uuid
WHERE "items"."id" IS NULL

It returns a list of missing ids in the database. I have it done like this:

const values = Array.from(wmids).map((id) => `('${id}')`).join(',');
const query = sql.raw(`WITH v (id) AS (VALUES ${values}) SELECT v.id FROM v `);
query.append(sql`LEFT JOIN ${Items} ON ${Items.id} = v.id::uuid `);
query.append(sql`WHERE ${isNull(Items.id)}`);

const res = await ctx.db.execute(query);

const values = Array.from(wmids).map((id) => `('${id}')`).join(',');
const query = sql.raw(`WITH v (id) AS (VALUES ${values}) SELECT v.id FROM v `);
query.append(sql`LEFT JOIN ${Items} ON ${Items.id} = v.id::uuid `);
query.append(sql`WHERE ${isNull(Items.id)}`);

const res = await ctx.db.execute(query);

Is there a way to do it more object friendly by using db.$with and db.with(name).... ? The presence of VALUES seems there might not be any helpers for this.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Postgres + WITH sql