DT
Drizzle Teamā€¢2y ago
rphlmr āš”

[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
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 āš”
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
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 āš”
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");

Did you find this page helpful?