[Postgres] prepared with inArray & placeholder

Hello there, I can't see why It ends with an error :/

export const sumQuery = db
  .select({
    sum: sql<number>`cast(sum(${Table.amount}) as integer)`,
  })
  .from(Table)
  .where(
    inArray(Table.userId, placeholder("userIds"))
  .prepare("sum_query");

// later
const userIds = ['real', 'uuid', 's']
await sumQuery.execute({ userIds });

export const sumQuery = db
  .select({
    sum: sql<number>`cast(sum(${Table.amount}) as integer)`,
  })
  .from(Table)
  .where(
    inArray(Table.userId, placeholder("userIds"))
  .prepare("sum_query");

// later
const userIds = ['real', 'uuid', 's']
await sumQuery.execute({ userIds });

select cast(sum("amount") as integer) from "table" where "table"."user_id" in $1
[
  [
    '54a7291c-7ab5-4ff0-b4a6-612cc5397a57',
    '69312f6a-6eab-4b67-b4d2-be1800f40d73',
    'a80d62dd-b623-42eb-a853-b8caaddd0d1e'
  ]
]

select cast(sum("amount") as integer) from "table" where "table"."user_id" in $1
[
  [
    '54a7291c-7ab5-4ff0-b4a6-612cc5397a57',
    '69312f6a-6eab-4b67-b4d2-be1800f40d73',
    'a80d62dd-b623-42eb-a853-b8caaddd0d1e'
  ]
]

PostgresError: syntax error at or near "$1"

PostgresError: syntax error at or near "$1"

Any idea? It only fails with array kind placeholder 😢 Looks like () are missing?

4 Replies

Andrii Sherman•2y ago

I guess each param in array should be parameterized like where "table"."user_id" in ($1, $2, $3) I guess it's something we need to fix

rphlmr ⚡OP•2y ago

When I write it in raw sql like

sql`${Table.userId} in (${placeholder("userIds")})`

sql`${Table.userId} in (${placeholder("userIds")})`

It tries to join the array :

"54a7291c-7ab5-4ff0-b4a6-612cc5397a57,69312f6a-6eab-4b67-b4d2-be1800f40d73,a80d62dd-b623-42eb-a853-b8caaddd0d1e"

"54a7291c-7ab5-4ff0-b4a6-612cc5397a57,69312f6a-6eab-4b67-b4d2-be1800f40d73,a80d62dd-b623-42eb-a853-b8caaddd0d1e"

Andrii Sherman•2y ago

yeah, I guess this one won't work you can try this trick for now https://orm.drizzle.team/docs/sql#sqljoin so try to

const sqlChunks: SQL[] = [];

if (userIds.length > 0) {
    sqlChunks.push(sql`${usersTable.id} in`);

    sqlChunks.push(sql`(`);
    for (let i = 0; i < userIds.length; i++) {
      sqlChunks.push(sql`${userIds[0]}`);
      
      if (i !== userIds.length - 1) {
        sqlChunks.push(sql`,`);
      }
    }
    sqlChunks.push(sql`)`);
 }

const whereSql: SQL = sql.join(sqlChunks, sql.raw(' '));

...

.where(whereSql)

const sqlChunks: SQL[] = [];

if (userIds.length > 0) {
    sqlChunks.push(sql`${usersTable.id} in`);

    sqlChunks.push(sql`(`);
    for (let i = 0; i < userIds.length; i++) {
      sqlChunks.push(sql`${userIds[0]}`);
      
      if (i !== userIds.length - 1) {
        sqlChunks.push(sql`,`);
      }
    }
    sqlChunks.push(sql`)`);
 }

const whereSql: SQL = sql.join(sqlChunks, sql.raw(' '));

...

.where(whereSql)

I may have lost something typed it directly here without checking in IDE But the idea is to create the sql yourseld iterating through array of values also you can replace

sqlChunks.push(sql`${userIds[0]}`);

sqlChunks.push(sql`${userIds[0]}`);

with placeholder

sqlChunks.push(sql`${placeholder(`userIds_${i}`)}`);

sqlChunks.push(sql`${placeholder(`userIds_${i}`)}`);

this is s workaround for now, so you won't be blocked but should be fixed on our and for sure

rphlmr ⚡OP•2y ago

I have to test it more, but it seems to work too. 🌧️

export const sumQuery = db
  .select({
    sum: sql<number>`cast(sum(${Table.amount}) as integer)`,
  })
  .from(Table)
  .where(
    eq(Table.userId, sql`any(string_to_array(${placeholder("userIds")}, ',')::UUID[])`))
  .prepare("sum_query");

export const sumQuery = db
  .select({
    sum: sql<number>`cast(sum(${Table.amount}) as integer)`,
  })
  .from(Table)
  .where(
    eq(Table.userId, sql`any(string_to_array(${placeholder("userIds")}, ',')::UUID[])`))
  .prepare("sum_query");

Gaming

Programming

[Postgres] prepared with inArray & placeholder