Is it currently possible to create generic orderBy functions?

[sebpowell]

Hello,

I'm trying to work out if at present, it is possible to do something like this:

async findMany({
 where,
 orderBy,
}: {
 where?: SQL<unknown>;
 orderBy?: { name: "asc" | "desc" };
}) {
 const _orderBy = [];

 for (const key in orderBy) {
  if (orderBy[key] === "asc") {
   _orderBy.push(asc(key));
  } else {
   _orderBy.push(desc(key));
  }
 }

 return await db.query.items.findMany({
  orderBy: _orderBy,
 })
}

I haven't seen anything in the docs that suggest it is possible, and haven't had any luck working out a solution, so wondering if I've missed something, and if not, whether anyone might be able to recommend an approach for doing the above.

Thank you in advance, any help would be greatly appreciated.

[Angelelz]

I don't see anything wrong with this. I guess the correct type for orderBy should be Record<string, "asc" | "desc">. Other than that, this seems fine to me.

[sebpowell]

@Angelelz thank you for replying! Yes, you are correct on the type – that being said, this approach doesn't seem to work, because Drizzle seems to expect you to provide a column by referencing the schema (not just the name) – see here:

Doing this doesn't just throw a TS error, but will throw a Drizzle error if you try using it.

So far, the only solution I've found to work is doing something like this – it's a little tedious, but it seems to work. Not sure if there's a better way, or if you can see what I'm doing wrong above?

[Angelelz]

Ah, I see.
This is what I would do in that case:

async findMany({
 where,
 orderBy,
}: {
 where?: SQL<unknown>;
 orderBy?: Record<keyof brands & string, "asc" | "desc">;
}) {
 const _orderBy = [];

 for (const key in orderBy) {
  if (orderBy[key] === "asc") {
   _orderBy.push(asc(brands[key]));
  } else {
   _orderBy.push(desc(brands[key]));
  }
 }

 return await db.query.items.findMany({
  orderBy: _orderBy,
 })
}

[sebpowell]

@Angelelz thank you for the suggestion! I did try that before, but it doesn't work, you get the following error:

error: syntax error at or near "desc"
    at  ... {
  length: 94,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '107',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1188',
  routine: 'scanner_yyerror'
}

The only way I was able to get it to work was by selecting the columns on the schema like so: brands.name – dynamic values – e.g. brands[key] don't seem to work.