# Filtering, Sorting and Pagination — Prisma / TypeORM / Sequelize

Source: https://www.skillbyai.com/en/orms/or-filter-paginate

> Express AND/OR conditions, comparison and text operators, ordering and limit/offset pagination in all three ORMs.

## 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.

```ts
// 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.

**Quiz:** 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'] } }
- [x] 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.
