Find_options

Chapter 13: Find Options & Querying

Advanced Querying with Find Options

---

13.1 Find Options Overview

Find options provide a powerful way to customize your queries without writing raw SQL.

                    Find Options Structure
    ================================================================================

    FindOptionsBase
    |
    +-- where          Filter conditions
    +-- relations      Related entities to load
    +-- select         Columns to select
    +-- order          Sorting
    +-- skip           Offset (pagination)
    +-- take           Limit (pagination)
    +-- cache          Query caching
    +-- lock           Row locking
    +-- join           Custom joins
    +-- withDeleted    Include soft deleted
    +-- relationLoadStrategy   Loading strategy

    ================================================================================

---

13.2 Where Conditions

Simple Conditions

import { Repository } from 'typeorm';
import { User } from './user.entity';

// Single condition
const users = await userRepository.find({
  where: { isActive: true },
});

// Multiple conditions (AND)
const users = await userRepository.find({
  where: {
    isActive: true,
    role: 'admin',
  },
});

OR Conditions

// Multiple where objects = OR
const users = await userRepository.find({
  where: [
    { role: 'admin' },
    { role: 'moderator' },
  ],
});
// SQL: WHERE role = 'admin' OR role = 'moderator'

// Complex OR with AND
const users = await userRepository.find({
  where: [
    { isActive: true, role: 'admin' },
    { isActive: false, role: 'moderator' },
  ],
});
// SQL: WHERE (isActive = true AND role = 'admin')
//      OR (isActive = false AND role = 'moderator')

Nested Conditions

// Conditions on relations
const users = await userRepository.find({
  where: {
    posts: {
      title: Like('%TypeScript%'),
    },
  },
  relations: ['posts'],
});

---

13.3 Find Operators

TypeORM provides special operators for complex conditions.

                    Find Operators
    +------------------------------------------------------------------+
    |                                                                   |
    |    Operator           | Description                              |
    |    -------------------|------------------------------------------|
    |    In([])             | Value in array                           |
    |    Not(value)         | Not equal to value                       |
    |    Between(a, b)      | Between two values                       |
    |    Like(pattern)      | Pattern matching (case-sensitive)       |
    |    ILike(pattern)     | Pattern matching (case-insensitive)     |
    |    IsNull()           | Value is NULL                            |
    |    NotIsNull()        | Value is NOT NULL                        |
    |    LessThan(n)        | Less than                                |
    |    LessThanOrEqual(n) | Less than or equal                       |
    |    MoreThan(n)        | Greater than                             |
    |    MoreThanOrEqual(n) | Greater than or equal                    |
    |    Equal(value)       | Equal to (explicit)                      |
    |    Raw(sql)           | Raw SQL condition                        |
    |    Any([])            | Any value in array (PostgreSQL)          |
    |                                                                   |
    +------------------------------------------------------------------+

In Operator

import { In } from 'typeorm';

// Values in array
const users = await userRepository.find({
  where: {
    id: In([1, 2, 3, 4, 5]),
  },
});
// SQL: WHERE id IN (1, 2, 3, 4, 5)

// With roles
const admins = await userRepository.find({
  where: {
    role: In(['admin', 'superadmin']),
  },
});

Not Operator

import { Not } from 'typeorm';

// Not equal
const nonAdmins = await userRepository.find({
  where: {
    role: Not('admin'),
  },
});
// SQL: WHERE role != 'admin'

// Not in array
const users = await userRepository.find({
  where: {
    id: Not(In([1, 2, 3])),
  },
});
// SQL: WHERE id NOT IN (1, 2, 3)

Between Operator

import { Between } from 'typeorm';

// Date range
const users = await userRepository.find({
  where: {
    createdAt: Between(
      new Date('2024-01-01'),
      new Date('2024-12-31')
    ),
  },
});
// SQL: WHERE createdAt BETWEEN '2024-01-01' AND '2024-12-31'

// Number range
const products = await productRepository.find({
  where: {
    price: Between(100, 500),
  },
});

Like Operators

import { Like, ILike } from 'typeorm';

// Case-sensitive pattern
const users = await userRepository.find({
  where: {
    name: Like('%John%'),
  },
});
// SQL: WHERE name LIKE '%John%'

// Case-insensitive pattern
const users = await userRepository.find({
  where: {
    name: ILike('%john%'),
  },
});
// SQL: WHERE LOWER(name) LIKE LOWER('%john%')

// Starts with
const users = await userRepository.find({
  where: {
    email: Like('john%'),
  },
});
// SQL: WHERE email LIKE 'john%'

// Ends with
const users = await userRepository.find({
  where: {
    email: Like('%@gmail.com'),
  },
});
// SQL: WHERE email LIKE '%@gmail.com'

Null Operators

import { IsNull, NotIsNull } from 'typeorm';

// Is null
const usersWithoutProfile = await userRepository.find({
  where: {
    profileId: IsNull(),
  },
});
// SQL: WHERE profileId IS NULL

// Is not null
const usersWithProfile = await userRepository.find({
  where: {
    profileId: NotIsNull(),
  },
});
// SQL: WHERE profileId IS NOT NULL

Comparison Operators

import { LessThan, LessThanOrEqual, MoreThan, MoreThanOrEqual } from 'typeorm';

// Less than
const oldUsers = await userRepository.find({
  where: {
    age: LessThan(18),
  },
});
// SQL: WHERE age < 18

// Less than or equal
const youngUsers = await userRepository.find({
  where: {
    age: LessThanOrEqual(25),
  },
});
// SQL: WHERE age <= 25

// Greater than
const seniorUsers = await userRepository.find({
  where: {
    age: MoreThan(60),
  },
});
// SQL: WHERE age > 60

// Greater than or equal
const adultUsers = await userRepository.find({
  where: {
    age: MoreThanOrEqual(18),
  },
});
// SQL: WHERE age >= 18

Raw Operator

import { Raw } from 'typeorm';

// Raw SQL condition
const users = await userRepository.find({
  where: {
    name: Raw(name => `LOWER(${name}) = 'john'`),
  },
});

// With parameters
const users = await userRepository.find({
  where: {
    createdAt: Raw(date => `${date} > NOW() - INTERVAL '30 days'`),
  },
});

---

13.4 Relations

Loading Relations

// Single relation
const user = await userRepository.find({
  relations: ['posts'],
});

// Multiple relations
const user = await userRepository.find({
  relations: ['posts', 'profile', 'comments'],
});

// Nested relations
const user = await userRepository.find({
  relations: ['posts', 'posts.comments', 'posts.comments.author'],
});

Relation Load Strategy

// JOIN (default) - Single query with JOINs
const users = await userRepository.find({
  relations: ['posts'],
  relationLoadStrategy: 'join',
});
// SQL: SELECT user.*, post.* FROM users user LEFT JOIN posts post ...

// QUERY - Multiple queries (better for large datasets)
const users = await userRepository.find({
  relations: ['posts'],
  relationLoadStrategy: 'query',
});
// SQL: SELECT * FROM users WHERE ...
// SQL: SELECT * FROM posts WHERE userId IN (...)

---

13.5 Select

Basic Select

// Select specific columns
const users = await userRepository.find({
  select: ['id', 'name', 'email'],
});
// SQL: SELECT id, name, email FROM users

Select with Relations

// Select columns from relations
const users = await userRepository.find({
  select: {
    id: true,
    name: true,
    posts: {
      id: true,
      title: true,
    },
  },
  relations: ['posts'],
});

Exclude Columns

// Select all except some (using negative selection)
const users = await userRepository.find({
  select: {
    id: true,
    name: true,
    email: true,
    // password is excluded
  },
});

---

13.6 Order

Single Order

const users = await userRepository.find({
  order: {
    createdAt: 'DESC',
  },
});
// SQL: ORDER BY createdAt DESC

Multiple Order

const users = await userRepository.find({
  order: {
    role: 'ASC',
    createdAt: 'DESC',
  },
});
// SQL: ORDER BY role ASC, createdAt DESC

Order by Relation

const posts = await postRepository.find({
  relations: ['author'],
  order: {
    author: {
      name: 'ASC',
    },
  },
});

---

13.7 Pagination

Skip and Take

// Page 1
const page1 = await userRepository.find({
  skip: 0,
  take: 10,
});

// Page 2
const page2 = await userRepository.find({
  skip: 10,
  take: 10,
});

// Page 3
const page3 = await userRepository.find({
  skip: 20,
  take: 10,
});

Pagination Helper

async function paginate<T>(
  repository: Repository<T>,
  page: number,
  limit: number,
  options?: FindManyOptions<T>,
) {
  const [data, total] = await repository.findAndCount({
    ...options,
    skip: (page - 1) * limit,
    take: limit,
  });

  return {
    data,
    meta: {
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    },
  };
}

// Usage
const result = await paginate(userRepository, 1, 10, {
  where: { isActive: true },
  order: { createdAt: 'DESC' },
});

---

13.8 Cache

// Enable caching for query
const users = await userRepository.find({
  cache: true,
});

// Cache with duration (ms)
const users = await userRepository.find({
  cache: 60000,  // 1 minute
});

// Cache with custom ID
const users = await userRepository.find({
  cache: {
    id: 'all_active_users',
    milliseconds: 60000,
  },
});

---

13.9 Lock

Pessimistic Lock

// Pessimistic read lock
const user = await userRepository.findOne({
  where: { id: 1 },
  lock: { mode: 'pessimistic_read' },
});
// SQL: SELECT ... FOR SHARE

// Pessimistic write lock
const user = await userRepository.findOne({
  where: { id: 1 },
  lock: { mode: 'pessimistic_write' },
});
// SQL: SELECT ... FOR UPDATE

// With transaction
await userRepository.manager.transaction(async (manager) => {
  const user = await manager.findOne(User, {
    where: { id: 1 },
    lock: { mode: 'pessimistic_write' },
  });

  user.balance -= 100;
  await manager.save(user);
});

---

13.10 withDeleted

// Include soft deleted records
const allUsers = await userRepository.find({
  withDeleted: true,
});

// Only soft deleted
const deletedUsers = await userRepository.find({
  withDeleted: true,
  where: {
    deletedAt: Not(IsNull()),
  },
});

---

13.11 Complete Find Options Example

const result = await userRepository.find({
  // Conditions
  where: [
    {
      isActive: true,
      role: In(['admin', 'moderator']),
      createdAt: Between(
        new Date('2024-01-01'),
        new Date('2024-12-31')
      ),
    },
    {
      isActive: false,
      lastLoginAt: MoreThan(new Date('2024-06-01')),
    },
  ],

  // Relations
  relations: ['posts', 'profile'],

  // Select specific columns
  select: {
    id: true,
    name: true,
    email: true,
    posts: {
      id: true,
      title: true,
    },
  },

  // Ordering
  order: {
    createdAt: 'DESC',
    name: 'ASC',
  },

  // Pagination
  skip: 0,
  take: 20,

  // Caching
  cache: 60000,

  // Include soft deleted
  withDeleted: false,
});

---

13.12 Summary

                    Find Options Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Option              | Description                              |
    |    -------------------|------------------------------------------|
    |    where              | Filter conditions                        |
    |    relations          | Related entities to load                 |
    |    select             | Columns to select                        |
    |    order              | Sorting                                  |
    |    skip               | Offset for pagination                    |
    |    take               | Limit for pagination                     |
    |    cache              | Query result caching                     |
    |    lock               | Row locking                              |
    |    withDeleted        | Include soft deleted                     |
    |    relationLoadStrategy| JOIN or QUERY loading                   |
    |                                                                   |
    +------------------------------------------------------------------+

---

Next Chapter

Chapter 14: Custom Repositories

---

Last Updated: February 2026