DT
Drizzle Team•14mo ago
cal

`sql.join` with `ODER BY` leads to syntax error while the sql query seems correct.

I am conditionally sorting the results from a database query 
const sortSqlChunks: SQL[] = [];
if (sorting.length > 1) {
  const customSort = sorting.shift();
  if (customSort) {
    const [property, order] = Object.entries(customSort)[0];
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${order} nulls last`,
    );
  }
}
sortSqlChunks.push(sql`${ad.publisherCreatedDateTime} desc nulls last`);

const ads = await this.dataAccessService
      .getDb("AD")
      .select()
      .from(ad)
      .where(inArray(ad.publicId, elasticSearchAds.publicIds))
      .orderBy(sql.join(sortSqlChunks, ", "));
const sortSqlChunks: SQL[] = [];
if (sorting.length > 1) {
  const customSort = sorting.shift();
  if (customSort) {
    const [property, order] = Object.entries(customSort)[0];
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${order} nulls last`,
    );
  }
}
sortSqlChunks.push(sql`${ad.publisherCreatedDateTime} desc nulls last`);

const ads = await this.dataAccessService
      .getDb("AD")
      .select()
      .from(ad)
      .where(inArray(ad.publicId, elasticSearchAds.publicIds))
      .orderBy(sql.join(sortSqlChunks, ", "));
The query builder returns the following query 
{
  sql: 'select ... from "ads"."Ad" where "Ad"."publicId" in (...) order by "Ad"."price" $26 nulls last$27"Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
    'desc',
    ', '
  ]
}
{
  sql: 'select ... from "ads"."Ad" where "Ad"."publicId" in (...) order by "Ad"."price" $26 nulls last$27"Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
    'desc',
    ', '
  ]
}
Which seems to be correct. When executing the code I get the following PostgresError: 
syntax error at or near \"$26\"
syntax error at or near \"$26\"
Something seems to be off with 'desc' but I don't have a clue what is causing this. Any help is appreciated
2 Replies
Angelelz
Angelelz•14mo ago
I don't think you can put the order in the query parameters Try this: 
...
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${sql.raw(order)} nulls last`,
    );
...
...
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${sql.raw(order)} nulls last`,
    );
...
cal
calOP•13mo ago
this results in 
{
  sql: 'select ... from "ads"."Ad" where "Ad"."publicId" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25) order by "Ad"."price" desc nulls last$26"Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
    ', '
  ]
}
{
  sql: 'select ... from "ads"."Ad" where "Ad"."publicId" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25) order by "Ad"."price" desc nulls last$26"Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
    ', '
  ]
}
with the following error: syntax error at or near \"nulls\" 🤔 sql.join seems to be the cause for the problem here: if I use 
sortSql = sql`${ad[property as unknown as keyof AdSelect]} ${sql.raw(order)} nulls last, ${ad.publisherCreatedDateTime} desc nulls last`;
sortSql = sql`${ad[property as unknown as keyof AdSelect]} ${sql.raw(order)} nulls last, ${ad.publisherCreatedDateTime} desc nulls last`;
this results in a query of 
{
  sql: ... from "ads"."Ad" where "Ad"."publicId" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25) order by "Ad"."price" asc nulls last, "Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
  ]
}
{
  sql: ... from "ads"."Ad" where "Ad"."publicId" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25) order by "Ad"."price" asc nulls last, "Ad"."publisherCreatedDateTime" desc nulls last',
  params: [
    ...
  ]
}
and a successful db query The problem was not using sql.raw on the join separator: this is the correct code to achieve what I want to do: 
const sortSqlChunks: SQL[] = [];
if (sorting.length > 1) {
  const customSort = sorting.shift();
  if (customSort) {
    const [property, order] = Object.entries(customSort)[0];
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${order} nulls last`,
    );
  }
}
sortSqlChunks.push(sql`${ad.publisherCreatedDateTime} desc nulls last`);

const ads = await this.dataAccessService
      .getDb("AD")
      .select()
      .from(ad)
      .where(inArray(ad.publicId, elasticSearchAds.publicIds))
      .orderBy(sql.join(sortSqlChunks, sql.raw(", ")));
const sortSqlChunks: SQL[] = [];
if (sorting.length > 1) {
  const customSort = sorting.shift();
  if (customSort) {
    const [property, order] = Object.entries(customSort)[0];
    sortSqlChunks.push(
      sql`${ad[property as unknown as keyof AdSelect]} ${order} nulls last`,
    );
  }
}
sortSqlChunks.push(sql`${ad.publisherCreatedDateTime} desc nulls last`);

const ads = await this.dataAccessService
      .getDb("AD")
      .select()
      .from(ad)
      .where(inArray(ad.publicId, elasticSearchAds.publicIds))
      .orderBy(sql.join(sortSqlChunks, sql.raw(", ")));
Want results from more Discord servers?
Add your server