37 Adding Dynamic Sorting to Queries: A Practical Guide for Software Engineers

One of the most frequently requested features in modern applications is the ability to sort data dynamically. Not just ascending or descending, but also by different columns depending on the user’s needs. For example, customer data might need to be sorted by name, registration date, or total transactions, either ascending or descending.

Adding dynamic sorting to a query might look simple on the surface—you just manipulate the ORDER BY clause in the SQL query. However, to make it secure, scalable, and still readable, we often need a more systematic approach. In this article, we’ll walk through the steps for adding dynamic sorting one by one, discuss anti-patterns to avoid, share examples with code snippets (using Node.js & TypeORM), and provide a simulation of the sorting results. If you spend a lot of time working in the backend domain, read this article all the way through.

Why Is Dynamic Sorting Important?

Dynamic sorting improves an application’s usability: users can decide for themselves how data is displayed, making it easier to gain insights. Imagine a dashboard like an e-commerce admin panel without sorting—incredibly inconvenient, right? Features like this boost customer satisfaction and also give our application a professional feel.

When Should You Use Dynamic Sorting?

• When the displayed data is fairly large and varied (e.g., product, order, or user tables)
• When users need the flexibility to change the order in which data is displayed
• For APIs used by multiple clients (e.g., mobile and web) with different sorting requirements
• For admin panels with sortable column features

Practical Problems: SQL Injection and Hardcoding

Adding dynamic sorting naively can invite security problems, particularly SQL Injection, if we directly inject the column name and sort order into the query. Here is an anti-pattern that absolutely must be avoided:

1// Anti-pattern: Highly vulnerable to SQL Injection!
2const query = `
3    SELECT * FROM users 
4    ORDER BY ${req.query.sortBy} ${req.query.order}
5`;

If sortBy is set to ’nama;DROP TABLE users;–’, your application could become a victim.

The Recommended Pattern: Whitelisting Columns and Order

The common, secure solution is to restrict user input (whitelisting) and escape the sort input.

Here is a pattern you can use with Node.js & TypeORM:

 1// 1. Define a whitelist of columns that are allowed to be sorted
 2const ALLOWED_SORT_FIELDS = ['name', 'created_at', 'total_orders'];
 3const ALLOWED_ORDERS = ['ASC', 'DESC'];
 4
 5// 2. Read the parameters from the request
 6const { sortBy = 'created_at', order = 'ASC' } = req.query;
 7
 8// 3. Validate the input
 9const safeSortBy = ALLOWED_SORT_FIELDS.includes(sortBy) ? sortBy : 'created_at';
10const safeOrder = ALLOWED_ORDERS.includes(order.toUpperCase()) ? order.toUpperCase() : 'ASC';
11
12// 4. TypeORM query
13const users = await getRepository(User)
14  .createQueryBuilder('user')
15  .orderBy(`user.${safeSortBy}`, safeOrder)
16  .getMany();

In the code above, the user input is validated first, so the resulting query is always safe.

Simulating the Query and Sorting Results

Suppose our database contains a users table like the following:

If the user calls /users?sortBy=total_orders&order=desc, the system will run the following query:

1SELECT * FROM users ORDER BY total_orders DESC;

The result:

Flowchart of the Dynamic Sorting Process

To make it clearer, here is the flow of the validation and query execution process (using mermaid):

flowchart TD
    A[Input sortBy dan order dari user] --> B{Validasi nama kolom}
    B -- Valid --> C[Validasi order]
    B -- Invalid --> D[Gunakan default (created_at)]
    C -- Valid --> E[Bangun query ORDER BY]
    C -- Invalid --> F[Gunakan default (ASC)]
    E --> G[Eksekusi query di DB]
    F --> G
    D --> C

flowchart TD
    A[Input sortBy dan order dari user] --> B{Validasi nama kolom}
    B -- Valid --> C[Validasi order]
    B -- Invalid --> D[Gunakan default (created_at)]
    C -- Valid --> E[Bangun query ORDER BY]
    C -- Invalid --> F[Gunakan default (ASC)]
    E --> G[Eksekusi query di DB]
    F --> G
    D --> C

A More Complex Case: REST API with Pagination & Sorting

Dynamic sorting is usually combined with pagination as well. Here is a commonly used endpoint pattern:

1GET /users?sortBy=created_at&order=desc&page=2&limit=10

An example implementation:

 1const { sortBy = 'created_at', order = 'ASC', page = 1, limit = 10 } = req.query;
 2const safeSortBy = ALLOWED_SORT_FIELDS.includes(sortBy) ? sortBy : 'created_at';
 3const safeOrder = ALLOWED_ORDERS.includes(order.toUpperCase()) ? order.toUpperCase() : 'ASC';
 4const take = Math.max(Number(limit), 1);
 5const skip = (Math.max(Number(page), 1) - 1) * take;
 6
 7const users = await getRepository(User)
 8  .createQueryBuilder('user')
 9  .orderBy(`user.${safeSortBy}`, safeOrder)
10  .skip(skip)
11  .take(take)
12  .getMany();

Dynamic Sorting at the ORM Level: Prisma & Eloquent Case Studies

Prisma

Prisma provides a fairly convenient API for sorting.

1const ALLOWED_SORT_FIELDS = ['name', 'createdAt', 'totalOrders'];
2// You need to convert to camel case to match Prisma's fields
3
4const safeSortBy = ALLOWED_SORT_FIELDS.includes(sortBy) ? sortBy : 'createdAt';
5const safeOrder = ALLOWED_ORDERS.includes(order.toLowerCase()) ? order.toLowerCase() : 'asc';
6
7const users = await prisma.user.findMany({
8  orderBy: { [safeSortBy]: safeOrder }
9});

Eloquent (Laravel)

1$allowedSortFields = ['name', 'created_at', 'total_orders'];
2$sortBy = in_array($request->get('sortBy'), $allowedSortFields) ? $request->get('sortBy') : 'created_at';
3$order = in_array(strtolower($request->get('order')), ['asc', 'desc']) ? $request->get('order') : 'asc';
4
5$users = User::orderBy($sortBy, $order)->get();

Advanced: Sorting Based on a Join Table

Sometimes users want to sort based on a column in a related/joined table. For example, the number of orders from the orders table for users. In TypeORM, this can be done with leftJoin and aggregation. However, make sure the whitelist is still applied!

Simulation Table: Sorting Results by Different Columns

Conclusion

Adding dynamic sorting to a query is not merely a matter of manipulating SQL strings. There are considerations around security, maintainability, and usability. Always use a whitelist to prevent SQL Injection, and provide a default value. As the query grows more complex, consider using an abstraction/middleware to keep the code clean. With this approach, the application you build will be more flexible, scalable, and—most importantly—safe from potential exploits.

Happy coding, and don’t forget to refactor if needed!

Psst! Need another snippet for sorting in a specific framework? Leave it in the comments.