order by is not working for me/ cant embed keywords

[marble dust]

hmm,

 const orderby=database.sql`ORDER BY ${sortBy.field} ${sortBy.direction}`;
    alerts = await database.sql`
            SELECT alerts.*,profiles.Full_name FROM alerts
            left join profiles on profiles.user_id = alerts.user_id
            WHERE alerts.hotel_id = ${user.hotel_id}
             ${true ? orderby : database.sql``};

returns syntax error at or near "ORDER" , what did i do wrong?

[marble dust]

oh okey mybad the ${} inside the sql for paramterized values, but then i would be able to nest order by keywords ??

i was able to fix it like this

let query = `
  SELECT alerts.*, profiles.Full_name 
  FROM alerts
  LEFT JOIN profiles ON profiles.user_id = alerts.user_id
  WHERE alerts.hotel_id = '${user.hotel_id}'
`;
alerts = await database.sql.unsafe(query+ " ORDER BY created_at " + sortBy.direction);

but this is not optimal,

[marble dust]

order by is not working for me/ cant embed keywords

[marble dust]

...

..

..

..

.

.

.

.

[fervent raven]

You should be properly tagging using sql framents here is a example:

const orderBy = {
  should_sort: true,
  field: "oid",
  asc: true,
};
console.log(
  await sql`SELECT typname, oid
FROM pg_type
WHERE oid > 65535
${orderBy.should_sort ? sql`ORDER BY ${sql(orderBy.field)} ${orderBy.asc ? sql`ASC` : sql`DESC`}` : sql``};`,
);

let me break it down and comment for you to make more clear:

function orderBy(orderByInfo) {
  // disable order by if enabled is false
  if (!orderByInfo.enabled) {
    // return an empty sql fragment
    return sql``;
  }
  // decide the direction based on the descending property
  // in this case we return a sql fragment with the direction
  const direction = orderByInfo.descending ? sql`DESC` : sql`ASC`;
  // use sql() to properly say its a identifier aka field name
  const field_name = sql(orderByInfo.field);
  // return the sql fragment
  return sql`ORDER BY ${field_name} ${direction}`;
}

const orderByInfo = {
  enabled: true,
  field: "oid",
  descending: true,
};

console.log(
   await sql`SELECT typname, oid FROM pg_type WHERE oid > 65535 ${orderBy(orderByInfo)}`
);

[honest beacon]

 const orderby=database.sql`ORDER BY ${sortBy.field} ${sortBy.direction}`;
    alerts = await database.sql`
            SELECT alerts.*,profiles.Full_name FROM alerts
            left join profiles on profiles.user_id = alerts.user_id
            WHERE alerts.hotel_id = ${user.hotel_id}
             ${sql(true ? orderby : database.sql)}`};

I have not tested above, but in other similar frameworks, you use ${sql()} (for sql fragments like column names) instead of ${} (for values)

[marble dust]

i love when discord doesnt send any msgs notification

[honest beacon]

heh. did it work?

[fervent raven]

@marble dust if you have any problem lmk and I will fixed ASAP, using this in a personal project and is working, but if you find any bugs or problems with your use case will be happy to help you

[marble dust]

Hey i will try the fix when i go home asap . Thanks in advance mate

[marble dust]

unforutunate that didnt work

there was a bug in adding columns using sql`` fixed in 1.2.3 but adding keyword like "asc desc" using fragements havent been fixed yet in 1.2.4 ,,

aright , bun 1.2.4 had this bug not fixed i updateed to 1.2.5v which is not final release , the bug is fixed

 const orderBy = (field_name: string, orderBy: string) => 
  sql`ORDER BY ${sql(field_name)} ${orderBy == "asc" ? sql`ASC` : sql`DESC`}`;
  console.log(await database.sql`
    SELECT alerts.*,profiles.Full_name FROM alerts
    left join profiles on profiles.user_id = alerts.user_id
    WHERE alerts.hotel_id = ${user.hotel_id}
    ${orderBy(sortBy.field,sortBy.direction)};
     `) ```

[marble dust]

@honest beacon

[honest beacon]

great to hear it is working

[marble dust]

finally ican now build my project again XD

i had to wait almost 3 month