Find Options & Operators

/**
 * Options for finding multiple entities
 */
interface FindManyOptions<Entity = any> {
  /** Columns to select */
  select?: FindOptionsSelect<Entity>;
  /** WHERE conditions */
  where?: FindOptionsWhere<Entity>[] | FindOptionsWhere<Entity>;
  /** Relations to load */
  relations?: FindOptionsRelations<Entity>;
  /** Ordering configuration */
  order?: FindOptionsOrder<Entity>;
  /** Number of entities to skip */
  skip?: number;
  /** Maximum number of entities to return */
  take?: number;
  /** Enable/disable caching */
  cache?: boolean | number | { id: any; milliseconds: number };
  /** Row locking configuration */
  lock?: { mode: "optimistic"; version: number | Date } |
         { mode: "pessimistic_read" | "pessimistic_write" | "dirty_read" |
                 "pessimistic_partial_write" | "pessimistic_write_or_fail" | "for_no_key_update" };
  /** Include soft-deleted entities */
  withDeleted?: boolean;
  /** Load relation IDs instead of relations */
  loadRelationIds?: boolean | FindOptionsRelationIds;
  /** Relation loading strategy */
  relationLoadStrategy?: "join" | "query";
  /** Comment for the query */
  comment?: string;
}

/**
 * Options for finding a single entity
 */
interface FindOneOptions<Entity = any> extends Omit<FindManyOptions<Entity>, "skip" | "take"> {}

/**
 * WHERE clause conditions with operator support
 */
type FindOptionsWhere<Entity> = {
  [P in keyof Entity]?: P extends "toString" ? unknown :
    Entity[P] extends never ? never :
    Entity[P] extends string ? FindOperator<string> | Entity[P] :
    Entity[P] extends number ? FindOperator<number> | Entity[P] :
    Entity[P] extends boolean ? FindOperator<boolean> | Entity[P] :
    Entity[P] extends Buffer ? FindOperator<Entity[P]> | Entity[P] :
    FindOperator<Entity[P]> | Entity[P] | FindOptionsWhere<Entity[P]>;
};

/**
 * SELECT clause configuration
 */
type FindOptionsSelect<Entity> = {
  [P in keyof Entity]?: P extends "toString" ? false :
    Entity[P] extends object ? FindOptionsSelect<Entity[P]> | boolean :
    boolean;
};

/**
 * Relations loading configuration
 */
type FindOptionsRelations<Entity> = {
  [P in keyof Entity]?: P extends "toString" ? false :
    Entity[P] extends object ? FindOptionsRelations<Entity[P]> | boolean :
    boolean;
};

/**
 * ORDER BY clause configuration
 */
type FindOptionsOrder<Entity> = {
  [P in keyof Entity]?: P extends "toString" ? never :
    Entity[P] extends object ? FindOptionsOrder<Entity[P]> :
    "ASC" | "DESC" | 1 | -1 | {
      direction?: "asc" | "desc" | "ASC" | "DESC";
      nulls?: "first" | "last" | "FIRST" | "LAST"
    };
};

import { FindManyOptions, FindOneOptions } from "typeorm";

// Basic find with select and where
const users = await userRepository.find({
  select: {
    id: true,
    name: true,
    email: true,
    profile: {
      bio: true
    }
  },
  where: {
    active: true,
    age: MoreThan(18)
  },
  relations: {
    profile: true,
    posts: true
  },
  order: {
    createdAt: "DESC"
  },
  take: 10,
  skip: 20
});

// Complex WHERE conditions with multiple options
const complexQuery: FindManyOptions<User> = {
  where: [
    { name: Like("%john%"), active: true },
    { email: Like("%@company.com"), status: In(["premium", "pro"]) }
  ],
  order: {
    name: "ASC",
    createdAt: { direction: "DESC", nulls: "last" }
  },
  cache: {
    id: "users_active",
    milliseconds: 60000
  }
};

/**
 * Equality operator (can be omitted for simple equality)
 * @param value - Value to compare
 * @returns FindOperator for equality comparison
 */
function Equal(value: any): FindOperator<any>;

/**
 * Negation operator
 * @param value - Value or operator to negate
 * @returns FindOperator for negation
 */
function Not(value: any | FindOperator<any>): FindOperator<any>;

/**
 * Less than operator
 * @param value - Value to compare against
 * @returns FindOperator for less than comparison
 */
function LessThan(value: any): FindOperator<any>;

/**
 * Less than or equal operator
 * @param value - Value to compare against
 * @returns FindOperator for less than or equal comparison
 */
function LessThanOrEqual(value: any): FindOperator<any>;

/**
 * Greater than operator
 * @param value - Value to compare against
 * @returns FindOperator for greater than comparison
 */
function MoreThan(value: any): FindOperator<any>;

/**
 * Greater than or equal operator
 * @param value - Value to compare against
 * @returns FindOperator for greater than or equal comparison
 */
function MoreThanOrEqual(value: any): FindOperator<any>;

/**
 * LIKE pattern matching operator
 * @param pattern - SQL LIKE pattern with % and _ wildcards
 * @returns FindOperator for LIKE comparison
 */
function Like(pattern: string): FindOperator<string>;

/**
 * Case-insensitive LIKE operator (PostgreSQL)
 * @param pattern - SQL LIKE pattern with % and _ wildcards
 * @returns FindOperator for case-insensitive LIKE
 */
function ILike(pattern: string): FindOperator<string>;

/**
 * IN operator for multiple values
 * @param values - Array of values to match
 * @returns FindOperator for IN comparison
 */
function In(values: any[]): FindOperator<any>;

/**
 * BETWEEN operator for range queries
 * @param from - Start of range
 * @param to - End of range
 * @returns FindOperator for BETWEEN comparison
 */
function Between(from: any, to: any): FindOperator<any>;

/**
 * IS NULL operator
 * @returns FindOperator for NULL check
 */
function IsNull(): FindOperator<any>;

/**
 * IS NOT NULL operator
 * @returns FindOperator for NOT NULL check
 */
function IsNotNull(): FindOperator<any>;

/**
 * ANY operator for array/subquery matching
 * @param values - Array of values or subquery
 * @returns FindOperator for ANY comparison
 */
function Any(values: any[] | SelectQueryBuilder<any>): FindOperator<any>;

/**
 * Raw SQL operator for custom conditions
 * @param condition - Raw SQL condition
 * @param parameters - Parameters for the condition
 * @returns FindOperator for raw SQL
 */
function Raw(condition: (columnAlias: string) => string, parameters?: ObjectLiteral): FindOperator<any>;

/**
 * Array contains operator (PostgreSQL)
 * @param values - Array that should be contained
 * @returns FindOperator for array contains
 */
function ArrayContains(values: any[]): FindOperator<any>;

/**
 * Array contained by operator (PostgreSQL)
 * @param values - Array that should contain the column value
 * @returns FindOperator for array contained by
 */
function ArrayContainedBy(values: any[]): FindOperator<any>;

/**
 * Array overlap operator (PostgreSQL)
 * @param values - Array to check for overlap
 * @returns FindOperator for array overlap
 */
function ArrayOverlap(values: any[]): FindOperator<any>;

/**
 * JSON contains operator (PostgreSQL, MySQL)
 * @param value - JSON value to check for containment
 * @returns FindOperator for JSON contains
 */
function JsonContains(value: any): FindOperator<any>;

/**
 * Logical AND operator for combining conditions
 * @param conditions - Array of conditions to combine with AND
 * @returns FindOperator for AND logic
 */
function And(...conditions: FindOperator<any>[]): FindOperator<any>;

/**
 * Logical OR operator for combining conditions
 * @param conditions - Array of conditions to combine with OR
 * @returns FindOperator for OR logic
 */
function Or(...conditions: FindOperator<any>[]): FindOperator<any>;

import {
  Equal, Not, LessThan, MoreThan, Like, In, Between, IsNull,
  Raw, ArrayContains, JsonContains
} from "typeorm";

// Basic operators
const users = await userRepository.find({
  where: {
    age: MoreThan(18),
    name: Like("John%"),
    status: In(["active", "premium"]),
    createdAt: Between(startDate, endDate),
    deletedAt: IsNull(),
    email: Not(Like("%temp%"))
  }
});

// PostgreSQL array operators
const postsWithTags = await postRepository.find({
  where: {
    tags: ArrayContains(["typescript", "tutorial"])
  }
});

// JSON operators
const usersWithMetadata = await userRepository.find({
  where: {
    metadata: JsonContains({ premium: true })
  }
});

// Raw SQL conditions
const customQuery = await userRepository.find({
  where: {
    name: Raw(alias => `UPPER(${alias}) = UPPER(:name)`, { name: "john" })
  }
});

// Complex combinations
const complexWhere = await userRepository.find({
  where: {
    age: And(MoreThan(18), LessThan(65)),
    email: Or(Like("%@company.com"), Like("%@enterprise.com"))
  }
});

/**
 * Configuration for loading relation IDs
 */
interface FindOptionsRelationIds {
  /** Relations to load IDs for */
  relations?: string[];
  /** Disable mixing with relation objects */
  disableMixedMap?: boolean;
}

/**
 * Tree-specific find options
 */
interface FindTreeOptions {
  /** Relations to load in tree entities */
  relations?: string[];
  /** Maximum tree depth to load */
  depth?: number;
}

/**
 * Base class for find operators
 */
abstract class FindOperator<T> {
  /** Operator type */
  readonly type: FindOperatorType;
  /** Operator value */
  readonly value: any;
  /** Whether to use parameter */
  readonly useParameter: boolean;
  /** Multiple values flag */
  readonly multipleParameters: boolean;
  /** SQL generator function */
  readonly sql: (columnName: string) => string;
  /** Parameter transformation */
  readonly parameters: ObjectLiteral;
}

/**
 * Supported find operator types
 */
type FindOperatorType =
  | "not" | "lessThan" | "lessThanOrEqual" | "moreThan" | "moreThanOrEqual"
  | "equal" | "between" | "in" | "any" | "isNull" | "isNotNull"
  | "like" | "ilike" | "raw" | "arrayContains" | "arrayContainedBy"
  | "arrayOverlap" | "jsonContains" | "and" | "or";

function buildUserQuery(filters: {
  name?: string;
  minAge?: number;
  maxAge?: number;
  statuses?: string[];
  includeInactive?: boolean;
}) {
  const where: FindOptionsWhere<User> = {};

  if (filters.name) {
    where.name = Like(`%${filters.name}%`);
  }

  if (filters.minAge !== undefined) {
    where.age = MoreThanOrEqual(filters.minAge);
  }

  if (filters.maxAge !== undefined) {
    where.age = where.age
      ? And(where.age as FindOperator<number>, LessThanOrEqual(filters.maxAge))
      : LessThanOrEqual(filters.maxAge);
  }

  if (filters.statuses?.length) {
    where.status = In(filters.statuses);
  }

  if (!filters.includeInactive) {
    where.active = true;
  }

  return { where };
}

// Usage
const query = buildUserQuery({
  name: "john",
  minAge: 18,
  statuses: ["active", "premium"]
});
const users = await userRepository.find(query);

interface PaginationOptions<T> {
  page: number;
  limit: number;
  sortBy?: keyof T;
  sortOrder?: "ASC" | "DESC";
}

async function paginateUsers(options: PaginationOptions<User>) {
  const findOptions: FindManyOptions<User> = {
    skip: (options.page - 1) * options.limit,
    take: options.limit,
  };

  if (options.sortBy) {
    findOptions.order = {
      [options.sortBy]: options.sortOrder || "ASC"
    } as any;
  }

  const [users, total] = await userRepository.findAndCount(findOptions);

  return {
    data: users,
    total,
    page: options.page,
    pages: Math.ceil(total / options.limit)
  };
}