Drizzle Team•11mo ago

Dynamic orderBy

I would like to dynamically add ordering to my select().from()... (I'm getting ordering criteria from frontend as a pair of column name and direction) Is there something similar as getTableColumns to get typed colums so I could find desired column by its name to use in orderBy() method?

1 Reply

copypaper•9mo ago

Hey I know this is late, but here's my solution that's working. I'm grabbing the column name off the search params

import { desc, asc } from "drizzle-orm";
import { type PgColumn, type PgSelect } from "drizzle-orm/pg-core";

type ColumnMap = Record<string, PgColumn>;

export function withParam<T extends PgSelect>(
  qb: T,
  p: string,
  colMap: ColumnMap,
) {
  const direction = p.startsWith("-") ? "desc" : "asc";
  // Remove the "-" from the column name.
  p = p.replace("-", "");
  const col = colMap[p];
  // If the column is not found, return the query builder as is.
  if (!col) {
    return qb;
  }
  if (direction === "desc") {
    return qb.orderBy(desc(col));
  }
  return qb.orderBy(asc(col));
}

import { desc, asc } from "drizzle-orm";
import { type PgColumn, type PgSelect } from "drizzle-orm/pg-core";

type ColumnMap = Record<string, PgColumn>;

export function withParam<T extends PgSelect>(
  qb: T,
  p: string,
  colMap: ColumnMap,
) {
  const direction = p.startsWith("-") ? "desc" : "asc";
  // Remove the "-" from the column name.
  p = p.replace("-", "");
  const col = colMap[p];
  // If the column is not found, return the query builder as is.
  if (!col) {
    return qb;
  }
  if (direction === "desc") {
    return qb.orderBy(desc(col));
  }
  return qb.orderBy(asc(col));
}

/**
 *
 * @param qb Query builder. Must have $dynamic method.
 * @param p a string that represents the column name. If it starts with a "-" it will sort in descending order. Otherwise, it will sort in ascending order.
 * @param colMap A map of column names to column objects. Should be a record of string to PgColumn from your schema.
 * @returns
 *
 * Example usage:
 * 
 * const query = db.select().from(users).$dynamic;
 * const sort = "-lastFirst"
 * const columnMap: ColumnMap = {
 *  lastFirst: users.lastFirst, // users is a table from your schema.
 *  firstLast: users.firstLast,
 * }
 * function query = withParam(query, sort, columnMap);
 * // query will order by users.lastFirst in descending order.
 * 
 */

/**
 *
 * @param qb Query builder. Must have $dynamic method.
 * @param p a string that represents the column name. If it starts with a "-" it will sort in descending order. Otherwise, it will sort in ascending order.
 * @param colMap A map of column names to column objects. Should be a record of string to PgColumn from your schema.
 * @returns
 *
 * Example usage:
 * 
 * const query = db.select().from(users).$dynamic;
 * const sort = "-lastFirst"
 * const columnMap: ColumnMap = {
 *  lastFirst: users.lastFirst, // users is a table from your schema.
 *  firstLast: users.firstLast,
 * }
 * function query = withParam(query, sort, columnMap);
 * // query will order by users.lastFirst in descending order.
 * 
 */

jsdoc was formatting wierd with discord, but that sshould explain it hopefully

Gaming

Programming

Dynamic orderBy