Where_clauses

Chapter 18: Where Clauses & Conditions

Advanced WHERE Clause Techniques

---

18.1 WHERE Clause Basics

Simple WHERE

// Single condition
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.isActive = :active', { active: true })
  .getMany();

// SQL: WHERE user.isActive = true

Multiple Conditions (AND)

// AND conditions
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.isActive = :active', { active: true })
  .andWhere('user.role = :role', { role: 'admin' })
  .getMany();

// SQL: WHERE user.isActive = true AND user.role = 'admin'

Multiple Conditions (OR)

// OR conditions
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.role = :admin', { admin: 'admin' })
  .orWhere('user.role = :mod', { mod: 'moderator' })
  .getMany();

// SQL: WHERE user.role = 'admin' OR user.role = 'moderator'

---

18.2 Parameter Binding

Named Parameters (Recommended)

// Named parameters - SQL injection safe
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.name = :name', { name: 'John' })
  .andWhere('user.age > :age', { age: 18 })
  .getMany();

// Parameters are automatically escaped

Positional Parameters

// Positional parameters (not recommended)
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.name = ?', ['John'])
  .andWhere('user.age > ?', [18])
  .getMany();

Setting Parameters Later

const qb = userRepository.createQueryBuilder('user')
  .where('user.name = :name')
  .andWhere('user.age > :age');

// Set parameters later
qb.setParameter('name', 'John');
qb.setParameter('age', 18);

const users = await qb.getMany();

Array Parameters

// IN clause with array
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.id IN (:...ids)', { ids: [1, 2, 3, 4, 5] })
  .getMany();

// SQL: WHERE user.id IN (1, 2, 3, 4, 5)

---

18.3 Comparison Operators

Equality

// Equal
.where('user.status = :status', { status: 'active' })

// Not equal
.where('user.status != :status', { status: 'banned' })

// Or using Not()
import { Not } from 'typeorm';
.where('user.status != :status', { status: Not('banned') })

Greater/Less Than

// Greater than
.where('user.age > :age', { age: 18 })

// Greater than or equal
.where('user.age >= :age', { age: 18 })

// Less than
.where('user.age < :age', { age: 65 })

// Less than or equal
.where('user.age <= :age', { age: 65 })

BETWEEN

// Between two values
.where('user.createdAt BETWEEN :start AND :end', {
  start: new Date('2024-01-01'),
  end: new Date('2024-12-31'),
})

// SQL: WHERE user.createdAt BETWEEN '2024-01-01' AND '2024-12-31'

LIKE

// Contains
.where('user.name LIKE :name', { name: '%John%' })

// Starts with
.where('user.name LIKE :name', { name: 'John%' })

// Ends with
.where('user.name LIKE :name', { name: '%John' })

// Case-insensitive (PostgreSQL)
.where('user.name ILIKE :name', { name: '%john%' })

NULL Checks

// IS NULL
.where('user.deletedAt IS NULL')

// IS NOT NULL
.where('user.emailVerifiedAt IS NOT NULL')

---

18.4 IN Clause

Simple IN

const users = await userRepository
  .createQueryBuilder('user')
  .where('user.id IN (:...ids)', { ids: [1, 2, 3] })
  .getMany();

// SQL: WHERE user.id IN (1, 2, 3)

NOT IN

const users = await userRepository
  .createQueryBuilder('user')
  .where('user.role NOT IN (:...roles)', { roles: ['banned', 'deleted'] })
  .getMany();

// SQL: WHERE user.role NOT IN ('banned', 'deleted')

Subquery IN

const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('post.authorId')
      .from(Post, 'post')
      .where('post.viewCount > :views')
      .getQuery();

    return 'user.id IN ' + subQuery;
  })
  .setParameter('views', 1000)
  .getMany();

// SQL: WHERE user.id IN (SELECT post.authorId FROM posts post WHERE post.viewCount > 1000)

---

18.5 Complex Conditions

Using Brackets

import { Brackets } from 'typeorm';

const users = await userRepository
  .createQueryBuilder('user')
  .where('user.isActive = :active', { active: true })
  .andWhere(
    new Brackets((qb) => {
      qb.where('user.role = :admin', { admin: 'admin' })
        .orWhere('user.role = :mod', { mod: 'moderator' });
    })
  )
  .getMany();

// SQL: WHERE user.isActive = true AND (user.role = 'admin' OR user.role = 'moderator')

Nested Brackets

const users = await userRepository
  .createQueryBuilder('user')
  .where(
    new Brackets((qb) => {
      qb.where('user.isActive = :active', { active: true })
        .andWhere(
          new Brackets((innerQb) => {
            innerQb.where('user.role = :admin', { admin: 'admin' })
              .orWhere('user.role = :mod', { mod: 'moderator' });
          })
        );
    })
  )
  .getMany();

// SQL: WHERE (user.isActive = true AND (user.role = 'admin' OR user.role = 'moderator'))

Not Brackets

import { NotBrackets } from 'typeorm';

const users = await userRepository
  .createQueryBuilder('user')
  .where(
    new NotBrackets((qb) => {
      qb.where('user.isBanned = :banned', { banned: true })
        .andWhere('user.isDeleted = :deleted', { deleted: true });
    })
  )
  .getMany();

// SQL: WHERE NOT (user.isBanned = true AND user.isDeleted = true)

---

18.6 Conditional WHERE

Dynamic Conditions

interface UserFilter {
  name?: string;
  email?: string;
  isActive?: boolean;
  role?: string;
}

async function findUsers(filter: UserFilter) {
  const qb = userRepository.createQueryBuilder('user');

  if (filter.name) {
    qb.andWhere('user.name ILIKE :name', { name: `%${filter.name}%` });
  }

  if (filter.email) {
    qb.andWhere('user.email ILIKE :email', { email: `%${filter.email}%` });
  }

  if (filter.isActive !== undefined) {
    qb.andWhere('user.isActive = :active', { active: filter.isActive });
  }

  if (filter.role) {
    qb.andWhere('user.role = :role', { role: filter.role });
  }

  return qb.getMany();
}

Conditional OR Groups

function buildSearchQuery(searchTerm: string, fields: string[]) {
  const qb = userRepository.createQueryBuilder('user');

  qb.where(
    new Brackets((innerQb) => {
      fields.forEach((field, index) => {
        if (index === 0) {
          innerQb.where(`${field} ILIKE :search`);
        } else {
          innerQb.orWhere(`${field} ILIKE :search`);
        }
      });
    })
  );

  qb.setParameter('search', `%${searchTerm}%`);

  return qb.getMany();
}

// Usage
const users = await buildSearchQuery('john', ['user.name', 'user.email', 'user.bio']);

---

18.7 EXISTS Clause

EXISTS with Subquery

const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .andWhere('post.published = true')
      .getQuery();

    return `EXISTS ${subQuery}`;
  })
  .getMany();

// SQL: WHERE EXISTS (SELECT 1 FROM posts post WHERE post.authorId = user.id AND post.published = true)

NOT EXISTS

const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb.subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();

    return `NOT EXISTS ${subQuery}`;
  })
  .getMany();

// Users without any posts

---

18.8 CASE WHEN in WHERE

const result = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
  ])
  .addSelect(
    `CASE
      WHEN user.role = 'admin' THEN 'Administrator'
      WHEN user.role = 'moderator' THEN 'Moderator'
      ELSE 'Regular User'
    END`,
    'roleLabel'
  )
  .where(
    `CASE
      WHEN user.role = 'admin' THEN user.isActive = true
      ELSE true
    END`
  )
  .getRawMany();

---

18.9 Date Conditions

Date Range

const users = await userRepository
  .createQueryBuilder('user')
  .where('user.createdAt >= :start', { start: new Date('2024-01-01') })
  .andWhere('user.createdAt <= :end', { end: new Date('2024-12-31') })
  .getMany();

Relative Dates

// Last 30 days
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.createdAt >= :date', {
    date: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
  })
  .getMany();

// Using database functions
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.createdAt > NOW() - INTERVAL \'30 days\'')
  .getMany();

Date Functions

// Extract year
.where('EXTRACT(YEAR FROM user.createdAt) = :year', { year: 2024 })

// Extract month
.where('EXTRACT(MONTH FROM user.createdAt) = :month', { month: 1 })

// Date truncation (PostgreSQL)
.where("date_trunc('month', user.createdAt) = :month", { month: '2024-01-01' })

---

18.10 JSON Conditions (PostgreSQL)

JSON Path Queries

// Query JSON column
const users = await userRepository
  .createQueryBuilder('user')
  .where("user.preferences->>'theme' = :theme", { theme: 'dark' })
  .getMany();

// JSON contains
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.metadata @> :filter', { filter: { role: 'admin' } })
  .getMany();

// JSON key exists
const users = await userRepository
  .createQueryBuilder('user')
  .where("user.preferences ? 'notifications'")
  .getMany();

---

18.11 Full-Text Search

PostgreSQL Full-Text Search

// Basic full-text search
const posts = await postRepository
  .createQueryBuilder('post')
  .where("to_tsvector('english', post.content) @@ to_tsquery('english', :query)", {
    query: 'typescript & tutorial',
  })
  .getMany();

// With ranking
const posts = await postRepository
  .createQueryBuilder('post')
  .select([
    'post.id',
    'post.title',
    'post.content',
  ])
  .addSelect(
    "ts_rank(to_tsvector('english', post.content), to_tsquery('english', :query))",
    'rank'
  )
  .where("to_tsvector('english', post.content) @@ to_tsquery('english', :query)", {
    query: 'typescript & tutorial',
  })
  .orderBy('rank', 'DESC')
  .getRawMany();

---

18.12 Summary

                    WHERE Clause Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Condition           | Syntax                                   |
    |    -------------------|------------------------------------------|
    |    Equal              | .where('col = :val', { val })            |
    |    Not Equal          | .where('col != :val', { val })           |
    |    Greater Than       | .where('col > :val', { val })            |
    |    Less Than          | .where('col < :val', { val })            |
    |    Between            | .where('col BETWEEN :a AND :b', { a, b }) |
    |    LIKE               | .where('col LIKE :val', { val })          |
    |    ILIKE              | .where('col ILIKE :val', { val })         |
    |    IN                 | .where('col IN (:...vals)', { vals })     |
    |    NOT IN             | .where('col NOT IN (:...vals)', { vals }) |
    |    IS NULL            | .where('col IS NULL')                     |
    |    IS NOT NULL        | .where('col IS NOT NULL')                 |
    |    EXISTS             | .where(qb => `EXISTS ${subQuery}`)        |
    |    AND                | .andWhere('condition')                    |
    |    OR                 | .orWhere('condition')                     |
    |    Grouping           | new Brackets(qb => ...)                   |
    |                                                                   |
    +------------------------------------------------------------------+

---

Next Chapter

Chapter 19: Subqueries & Advanced Queries

---

Last Updated: February 2026