API Query Order By

[berlin2]

I have an API that is retrieving data. Does Drizzle offer a better way to create the orderBy statement?

export async function POST(request: NextRequest) {
    const body = await request.json();
    const query = db.select().from(athletes)
    if (body.sortModel) {
        body.sortModel.forEach((sort: any) => {
            if (sort.colId === 'year' && sort.sort === 'asc') query.orderBy(asc(athletes['year']))
            if (sort.colId === 'year' && sort.sort === 'desc') query.orderBy(desc(athletes['year']))  
            if (sort.colId === 'country' && sort.sort === 'asc') query.orderBy(asc(athletes['country']))
            if (sort.colId === 'country' && sort.sort === 'desc') query.orderBy(desc(athletes['country']))  
        })
    }
    const result = await query
    return new Response(JSON.stringify({rows: result}))
}

[Senbonzakura1234]

This problem can be solve by using object literal mapping, all you need is 2 object, 1 to map sord field, and the other to map sort direction query.

map the sort.colId to the correct column name, you should use something like zod to make sure sort.colId value is what you expected before making any queries. Doing the same with sort.sort.

[berlin2]

Can you elaborate? With Prisma I would use solution:

    let queryPrisma = {
        where: body.where || undefined,
        select: body.select || undefined,
        orderBy: <any>[],
    };
    if (body.sortModel) {
        body.sortModel.forEach((sort: any) => {
            queryPrisma.orderBy.push({[sort.colId]: sort.sort})
        })
    }

[Senbonzakura1234]

no that's not what i mean, i mean use object literal to map condition.
like this

type DirectionEnum  = 'asc' | 'desc';

const DirectionMap = {
   'asc': asc, // import direction query from drizzle
   'desc': desc, // import direction query from drizzle
} as const;

export const getDirection = (direction: DirectionEnum | null) => (direction ? DirectionMap[direction] : asc);

and in query just do this

   query.orderBy(getDirection(sort.sort)(athletes[sort.colId]))

Also i suggest to use something like zod to validate request input like sort.colId and sort.sort

[berlin2]

This worked for the sort direction. For the column I implemented the following:

type ColumnEnum  = 'id' | 'year' | 'country';

const ColumnMap = {
    'id': athletes.id,
    'year': athletes.year,
    'country': athletes.country,
 } as const;
 
 const getColumn = (column: ColumnEnum | null) => (column ? ColumnMap[column] : athletes.id);

query:

query.orderBy(getDirection(sort.sort)(getColumn(sort.colId)))