Implementing Sorting
1. Reading Sort Parameters
Example: ?sort=name,-createdAt
const sortParam = (req.query.sort || "").split(",").filter(Boolean);
// → ["name", "-createdAt"]2. Implementing Single Field Sorting
Example: Single field
const orderBy = { [req.query.sortBy || "id"]: req.query.order === "desc" ? "desc" : "asc" };3. Implementing Multi-Field Sorting
Example: Multi-key parser
function parseSort(sortStr, allowed) {
return sortStr.split(",").filter(Boolean).map(token => {
const desc = token.startsWith("-");
const field = desc ? token.slice(1) : token;
if (!allowed.includes(field)) throw new HttpError(400, `Invalid sort: ${field}`);
return { [field]: desc ? "desc" : "asc" };
});
}
const orderBy = parseSort(req.query.sort || "", ["name","price","createdAt"]);4. Setting Sort Direction
| Convention | Notation |
|---|---|
Prefix - | ?sort=-createdAt |
Suffix :desc | ?sort=createdAt:desc |
| Separate param | ?sortBy=createdAt&order=desc |
5. Using Default Sorting
Example: Default to id
const orderBy = sortParam.length ? parseSort(sortParam, ALLOWED) : [{ id: "asc" }];Note: Always include a tie-breaker (e.g.,
id) to guarantee stable order for pagination.6. Validating Sort Fields
Example: Allowlist
const ALLOWED_SORT = new Set(["name","price","createdAt","rating"]);
if (!ALLOWED_SORT.has(field)) throw new HttpError(400, "Invalid sort field");Warning: Never pass user input directly as a column name — SQL injection risk. Always allowlist.
7. Implementing Case-Insensitive Sorting
Example: Postgres COLLATE
SELECT * FROM users ORDER BY name COLLATE "en_US.utf8";
-- or
SELECT * FROM users ORDER BY LOWER(name);8. Using Database Sorting
| Engine | Syntax |
|---|---|
| SQL | ORDER BY col [ASC|DESC] NULLS [FIRST|LAST] |
| MongoDB | .sort({ col: 1 }) |
| Prisma | orderBy: [{ a: "asc" }, { b: "desc" }] |
9. Sorting Nested Fields
Example: Sort by relation
const items = await prisma.post.findMany({
orderBy: { author: { name: "asc" } }
});10. Combining Sorting with Filtering
Example: Filter + sort + paginate
app.get("/products", async (req, res) => {
const filter = filterSchema.parse(req.query);
const { page, limit, offset } = parsePagination(req);
const orderBy = parseSort(req.query.sort || "", ["name","price","createdAt"]);
const [items, total] = await Promise.all([
prisma.product.findMany({ where: buildWhere(filter), orderBy, skip: offset, take: limit }),
prisma.product.count({ where: buildWhere(filter) })
]);
res.json({ data: items, meta: { page, limit, total } });
});