Drizzle Team•15mo ago

orderBy with variable column

Hey all! I'd like to explore the possibility to order a query by one of it's columns, but the column itself is inferred from a variable. What i've tried:

// "categories" has 2 column, id and name

const sortField = 'name';

await db.query.categories.findMany({ orderBy: asc(sql`${sortField}`) });

// "categories" has 2 column, id and name

const sortField = 'name';

await db.query.categories.findMany({ orderBy: asc(sql`${sortField}`) });

but no sort is applied. Even using

const sortField = 'name' as keyof typeof categories;

const sortField = 'name' as keyof typeof categories;

does not change the final result. Any idea? Thanks in advance!

Solution:

const sortField = 'name';

await db.query.categories.findMany({ orderBy: asc(sql.identifier(sortField)) });

const sortField = 'name';

await db.query.categories.findMany({ orderBy: asc(sql.identifier(sortField)) });

...

Jump to solution

5 Replies

Angelelz•15mo ago

Can you pass { logger: true } to drizzle config and share what is the query that's generated?

ValerioOP•15mo ago

Here the result, base on a real world query Query: select "id", "name", "order" from "categories" order by ? asc limit ? -- params: ["name", 25]

Solution

Angelelz•15mo ago

const sortField = 'name';

await db.query.categories.findMany({ orderBy: asc(sql.identifier(sortField)) });

const sortField = 'name';

await db.query.categories.findMany({ orderBy: asc(sql.identifier(sortField)) });

Angelelz•15mo ago

Try that

ValerioOP•15mo ago

Your solution works! Pog

a lot!

Gaming

Programming

orderBy with variable column