Drizzle Team•9mo ago

order by dynamic colum

i have a table and i want the user to be able to order by different columns. how do i write the drizzle query to take in a string and do the ordering? Example here: https://orm.drizzle.team/learn/guides/limit-offset-pagination does ordereing as such: .orderBy(users.id). But for my use case, I won't know until runtime.

Drizzle ORM - SQL Limit/Offset pagination

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

10 Replies

Sillvva•9mo ago

Create an object that maps string keys to table columns.

import { asc, type AnyColumn } from "drizzle-orm";

const orderByOptions: Record<string, AnyColumn> = {
    id: users.id,
    name: users.name
};

const selectedOption: string = "id";

if (orderByOptions[selectedOption]) {
  db
  .select()
  .from(users)
  .orderBy(asc(orderByOptions[selectedOption]));
}

import { asc, type AnyColumn } from "drizzle-orm";

const orderByOptions: Record<string, AnyColumn> = {
    id: users.id,
    name: users.name
};

const selectedOption: string = "id";

if (orderByOptions[selectedOption]) {
  db
  .select()
  .from(users)
  .orderBy(asc(orderByOptions[selectedOption]));
}

shikishikichangchangOP•9mo ago

damn i was hoping there was an easier solution i have thirteen columns do you know if there's a way in drizzle to return the count before limit and offset? i want to avoid having two very similar queries

shikishikichangchangOP•9mo ago

shikishikichangchangOP•9mo ago

is there anything wrong doing it this way?

Sillvva•9mo ago

I think that looks fine. I always forget about the sql function

shikishikichangchangOP•9mo ago

thanks do you know if there's a way in drizzle to return the count before limit and offset?

Sillvva•9mo ago

Not without doing a query to get the count

shikishikichangchangOP•9mo ago

so i have a table i want to get the count before limit and before offset does that mean i need to send two similar queries to the db? only that one doesnt have the limit and offset

Sillvva•9mo ago

You'd probably have to do two queries. Also there's this nice helper method:

import { getTableColumns, type AnyColumn } from "drizzle-orm";
const cols: Record<string, AnyColumn> = getTableColumns(users);

import { getTableColumns, type AnyColumn } from "drizzle-orm";
const cols: Record<string, AnyColumn> = getTableColumns(users);

shikishikichangchangOP•9mo ago

thanks this seems inefficient wonder if there's a better way hmm

Gaming

Programming

order by dynamic colum