Drizzle Team•8mo 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•8mo 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•8mo 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•8mo ago

shikishikichangchangOP•8mo ago

is there anything wrong doing it this way?

Sillvva•8mo ago

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

shikishikichangchangOP•8mo ago

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

Sillvva•8mo ago

Not without doing a query to get the count

shikishikichangchangOP•8mo 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•8mo 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•8mo ago

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

Gaming

Programming

order by dynamic colum