Drizzle Team•2y ago

Find IDs from a list which do not exist in table

As the title says, i'm trying to filter a list of given IDs (could be hundreds) into a list of IDs that do not exist in a table (could have multiple thousands of rows). My research suggests that I should be doing something like this:

select *
from ( 
   values (1),(2),(3)
) as temp(id)
where not exists (select *
                  from mytable
                  where mytable.id = temp.id);

select *
from ( 
   values (1),(2),(3)
) as temp(id)
where not exists (select *
                  from mytable
                  where mytable.id = temp.id);

Would you agree and more importantly, can I do this using ORM syntax? Thanks for any tips or insights you can provide.

1 Reply

ccabdOP•2y ago

i guess not but this is what i did

const ids = [ 3445, 3446, 999 ];
const values = sql.join( ids.map( v => sql`(${v})` ), sql`,` );

const query = sql.empty();
query.append( sql`SELECT * FROM ( VALUES ` );
query.append( values );
query.append( sql` ) AS temp(id)` );
query.append( sql` WHERE NOT EXISTS (` );
query.append( sql` SELECT * FROM ${mytable} WHERE ${mytable.id} = CAST( temp.id AS integer )` );
query.append( sql` )` );

const result = await database.execute( query );

const ids = [ 3445, 3446, 999 ];
const values = sql.join( ids.map( v => sql`(${v})` ), sql`,` );

const query = sql.empty();
query.append( sql`SELECT * FROM ( VALUES ` );
query.append( values );
query.append( sql` ) AS temp(id)` );
query.append( sql` WHERE NOT EXISTS (` );
query.append( sql` SELECT * FROM ${mytable} WHERE ${mytable.id} = CAST( temp.id AS integer )` );
query.append( sql` )` );

const result = await database.execute( query );

which results in this query

{
  query: 'SELECT * FROM ( VALUES ($1),($2),($3) ) AS temp(id) WHERE NOT EXISTS ( SELECT * FROM "mytable" WHERE "mytable"."id" = CAST( temp.id AS integer ) )',
  params: [ 3445, 3446, 999 ]
}

{
  query: 'SELECT * FROM ( VALUES ($1),($2),($3) ) AS temp(id) WHERE NOT EXISTS ( SELECT * FROM "mytable" WHERE "mytable"."id" = CAST( temp.id AS integer ) )',
  params: [ 3445, 3446, 999 ]
}

Gaming

Programming

Find IDs from a list which do not exist in table