DT
Drizzle Team•16mo ago
francis

How to include array literals in raw sql?

I have an input array used for an order by using the array_position function - rather than passing the array as a single param, the call to: 
.orderBy(sql`array_position(${ingredientNames}, ${entity.name})`);
.orderBy(sql`array_position(${ingredientNames}, ${entity.name})`);
generates a param list for each item in the input array. This generates a record rather than an array literal in Postgres. Trying raw sql with: 
`{${array.map((item) => `'${item}'`).join(",")}}`
`{${array.map((item) => `'${item}'`).join(",")}}`
doesn't work either.
2 Replies
francis
francisOP•16mo ago
update: got it working with: 
export function arrayToSqlArray(array: unknown[]) {
  return `{${array.map((item) => `"${item}"`).join(",")}}`;
}
export function arrayToSqlArray(array: unknown[]) {
  return `{${array.map((item) => `"${item}"`).join(",")}}`;
}
and: 
    .orderBy(sql`array_position(${arrayToSqlArray(ingredientNames)}, ${ingredient.name})`)
    .orderBy(sql`array_position(${arrayToSqlArray(ingredientNames)}, ${ingredient.name})`)
dandadan
dandadan•10mo ago
ive got it working with the following 
const sanitizeArray = (arr: string[]) => {
  const sanitizedArray: SQL<unknown>[] = [sql`[`];
  const rawArray: SQL<unknown>[] = [];

  for (const element of arr) {
    rawArray.push(sql`${element}`);
  }
  sanitizedArray.push(sql.join(rawArray, sql`, `));
  sanitizedArray.push(sql`]`);

  const sanitizedQuery = sql.join(sanitizedArray);
  return sanitizedQuery;
};

const tokens = ["a", "b"];
const sanitizedTokens = sanitizeArray(tokens);
const x = sql`SELECT unnest(ARRAY${sanitizedTokens}) AS word`;
const sanitizeArray = (arr: string[]) => {
  const sanitizedArray: SQL<unknown>[] = [sql`[`];
  const rawArray: SQL<unknown>[] = [];

  for (const element of arr) {
    rawArray.push(sql`${element}`);
  }
  sanitizedArray.push(sql.join(rawArray, sql`, `));
  sanitizedArray.push(sql`]`);

  const sanitizedQuery = sql.join(sanitizedArray);
  return sanitizedQuery;
};

const tokens = ["a", "b"];
const sanitizedTokens = sanitizeArray(tokens);
const x = sql`SELECT unnest(ARRAY${sanitizedTokens}) AS word`;
im not even sure where i picked up the snippet of code from im leaving this here in case someone has a better solution

Did you find this page helpful?