Dynamic order by

[mortifia]

hello, I am currently using a list protected by an enum to retrieve the order wanted by the request

I wonder if there was a way to use this list or if I need to create each case

example of list for order by

const orderList = ['is_business desc', 'owner_business desc', 'owner_user desc']

example of use case

await this.db`
        select t.*, max(m.created_on) as created_on
          from chat.tickets t
          join chat.messages m using (ticket_uuid)
          join chat.ticket_user tu using (ticket_uuid)
          where tu.user_uuid = ${this.context.user_uuid}
          group by t.ticket_uuid
          ${this.db.orderBy(orderList)}`)

[porsager]

For now you have to make do with multiple query versions or sql.unsafe().

Once we get dynamic keyword / fragments support you will be able to do it like this:

// Not working yet
sql`
  select * from wat
  orer by ${ sql(column) } ${ desc ? sql`desc` : sql`asc` }
`

[mortifia]

thanks for your fast reply :)

[joshxyzhimself]

this one is supported already right?

i was looking for a way to use variables in columns. dynamic column names.

the following surprisingly works, lol.

await sql`
  CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY,
    "name" text NOT NULL,
    "telegram_id" text,
    "whatsapp_id" text,
    "wechat_id" text,
    UNIQUE("name")
  );
`;

const platform = 'telegram';
const platform_user_id = '123123123';
const users = await sql`
  SELECT * FROM "users"
  WHERE ${sql(platform.concat('_id'))} = ${platform_user_id}
  LIMIT 1;
`;

[porsager]

Oh yeah, that should be fine :)