Lesson 13 / 35
Filtering, Sorting and Pagination
Express AND/OR conditions, comparison and text operators, ordering and limit/offset pagination in all three ORMs.
By Rahul Agarwal · 15 years in web development
Where, order, limit
Every list screen needs the same three clauses: a filter (WHERE), an order (ORDER BY) and a page size (LIMIT with OFFSET or a cursor). In a filter object, separate keys are combined with AND. For OR you use an explicit construct: Prisma OR: [...], Sequelize [Op.or]: [...], and in TypeORM you pass an array of where objects to find. Comparison operators look different in each ORM: Prisma nests them as { gte: 18 }, Sequelize uses symbol keys such as { [Op.gte]: 18 }, and TypeORM uses helper functions like MoreThanOrEqual(18), Like('%x%') and In([1, 2]). Text matching with LIKE is case-sensitive on PostgreSQL by default; Prisma offers mode: 'insensitive' on PostgreSQL, TypeORM has ILike and Sequelize has Op.iLike. Always add an explicit order when paginating. Without ORDER BY, SQL does not promise any row order, so pages can repeat or skip rows.
Page 2 of adult students from one college
The same query in three dialects: AND filter, newest first, 20 per page, skipping the first 20.
// Prisma
prisma.student.findMany({
where: { age: { gte: 18 }, email: { endsWith: '@nitk.edu.in' } },
orderBy: { createdAt: 'desc' },
skip: 20,
take: 20,
});
// TypeORM
studentRepo.find({
where: { age: MoreThanOrEqual(18), email: Like('%@nitk.edu.in') },
order: { createdAt: 'DESC' },
skip: 20,
take: 20,
});
// Sequelize
Student.findAll({
where: { age: { [Op.gte]: 18 }, email: { [Op.endsWith]: '@nitk.edu.in' } },
order: [['createdAt', 'DESC']],
offset: 20,
limit: 20,
});Merit list on the notice board
A college puts its merit list on the board 20 names per sheet, sorted by marks. If the clerk printed each sheet without sorting first, the same student could appear on two sheets and another on none. ORDER BY is the sorting; LIMIT and OFFSET decide which sheet you are looking at.
Sort on a unique tie-breaker
Sorting only by createdAt is not enough if two rows share a timestamp: their order can change between queries. Add the primary key as a second sort key, for example orderBy: [{ createdAt: 'desc' }, { id: 'desc' }] in Prisma.
Quick check: In TypeORM's find(), how do you express 'city is Pune OR city is Indore'?
- where: { city: 'Pune', city: 'Indore' }
- where: { OR: ['Pune', 'Indore'] }
- where: { city: { [Op.or]: ['Pune', 'Indore'] } }
- where: [{ city: 'Pune' }, { city: 'Indore' }] or where: { city: In(['Pune', 'Indore']) }
Answer
where: [{ city: 'Pune' }, { city: 'Indore' }] or where: { city: In(['Pune', 'Indore']) } — TypeORM treats an array of where objects as OR, and In() generates an IN list. Op.or is Sequelize syntax, and OR is Prisma syntax.