Skip to content

The open alternative to Salesforce, designed for AI.

36.8 KB
Raw Blame
import { type QueryConfig, types } from 'pg';
import {
  type FeatureFlagKey,
  type ObjectsPermissions,
} from 'twenty-shared/types';
import { isDefined, pascalCase } from 'twenty-shared/utils';
import { type ObjectLiteral } from 'typeorm';
import { InstanceChecker } from 'typeorm/util/InstanceChecker';

import { RelationType } from 'src/engine/metadata-modules/field-metadata/interfaces/relation-type.interface';

import {
  TwentyOrmException,
  TwentyOrmExceptionCode,
} from 'src/engine/twenty-orm/exceptions/twenty-orm.exception';
import { type QueryExecutor } from 'src/engine/twenty-orm/executor/types/query-executor.type';
import {
  type FindOptionsLike,
  type ObjectWhereLike,
  type OrderByConditionLike,
  type WhereConditionLike,
  type WhereExpressionLike,
  isNegatedWhereFactoryLike,
  isObjectWhereLike,
  isWhereFactoryLike,
} from 'src/engine/twenty-orm/query-builder/types/query-builder.type';
import { WorkspaceMutationQueryBuilder } from 'src/engine/twenty-orm/query-builder/workspace-mutation-query-builder';
import { type MutationKind } from 'src/engine/twenty-orm/sql/utils/build-mutation-statement.util';
import { buildOrderByClauses } from 'src/engine/twenty-orm/sql/utils/build-order-by-clauses.util';
import { collectReferencedColumnNames } from 'src/engine/twenty-orm/sql/utils/collect-referenced-column-names.util';
import { compileNamedParameters } from 'src/engine/twenty-orm/sql/utils/compile-named-parameters.util';
import {
  RESERVED_PARAMETER_NAMES,
  buildCountStatement,
  buildHydrationPathByResultAlias,
  buildPaginationParameters,
  buildProjection,
  buildSelectStatement,
  buildWhereExpression,
  collectStatementAliases,
  createRowToEntityMapper,
  normaliseColumnExpression,
  quoteColumn,
  quoteQualifiedAliasReferences,
  type ColumnSelection,
  type ExistsFilterClause,
  type ToManyDedupOrder,
  type JoinClause,
  type OrderByClause,
  type SelectClause,
  type SelectStatementState,
  type WhereClause,
} from 'src/engine/twenty-orm/sql/utils/build-select-statement.util';
import { type WorkspaceTableShape } from 'src/engine/twenty-orm/table-shape/types/workspace-table-shape.type';
import { escapeIdentifier } from 'src/engine/workspace-manager/workspace-migration/utils/remove-sql-injection.util';

let objectWhereParameterSequence = 0;
let existsFilterSequence = 0;

const isNestedWhereObject = (value: unknown): value is ObjectWhereLike =>
  isDefined(value) &&
  typeof value === 'object' &&
  !Array.isArray(value) &&
  !InstanceChecker.isFindOperator(value) &&
  !(value instanceof Date);

export type QueryBuilderContext = {
  tableShape: WorkspaceTableShape;
  executor: QueryExecutor;
  objectRecordsPermissions: ObjectsPermissions;
  featureFlagsMap?: Partial<Record<FeatureFlagKey, boolean>>;
  tableShapeByObjectMetadataId: (
    objectMetadataId: string,
  ) => WorkspaceTableShape;
  onBeforeExecute: (queryBuilder: WorkspaceSelectQueryBuilder) => void;
  formatResult: <T>(records: unknown) => T;
};

export class WorkspaceSelectQueryBuilder implements WhereExpressionLike {
  readonly alias: string;
  readonly tableShape: WorkspaceTableShape;
  readonly objectRecordsPermissions: ObjectsPermissions;
  readonly featureFlagsMap: Partial<Record<FeatureFlagKey, boolean>>;

  private readonly context: QueryBuilderContext;
  private readonly whereClauses: WhereClause[] = [];
  private readonly rowAccessConditions: string[] = [];
  private readonly joinClauses: JoinClause[] = [];
  private readonly existsFilterClauses: ExistsFilterClause[] = [];
  private readonly extraSelectClauses: SelectClause[] = [];
  private readonly pendingColumnSelections: string[] = [];
  private orderByClauses: OrderByClause[] = [];
  private groupByExpressions: string[] = [];
  private distinctOnExpressions: string[] = [];
  private parameters: Record<string, unknown> = {};
  private findOptions: FindOptionsLike = {};
  private limitValue?: number;
  private offsetValue?: number;
  private includeDeleted = false;
  private explicitSelection?: string[];
  private readonly aliasesWithRowLevelPermissionApplied = new Set<string>();

  constructor(alias: string, context: QueryBuilderContext) {
    this.alias = alias;
    this.tableShape = context.tableShape;
    this.objectRecordsPermissions = context.objectRecordsPermissions;
    this.featureFlagsMap = context.featureFlagsMap ?? {};
    this.context = context;
  }

  getJoinAliases(): { name: string; isToMany: boolean }[] {
    return [
      ...this.joinClauses.map((joinClause) => ({
        name: joinClause.alias,
        isToMany: joinClause.relationType === RelationType.ONE_TO_MANY,
      })),
      ...this.existsFilterClauses.map((existsFilterClause) => ({
        name: existsFilterClause.alias,
        isToMany: false,
      })),
    ];
  }

  clone(executor?: QueryExecutor): WorkspaceSelectQueryBuilder {
    const cloned = new WorkspaceSelectQueryBuilder(
      this.alias,
      isDefined(executor) ? { ...this.context, executor } : this.context,
    );

    cloned.whereClauses.push(...this.whereClauses);
    cloned.rowAccessConditions.push(...this.rowAccessConditions);
    cloned.existsFilterClauses.push(
      ...this.existsFilterClauses.map((existsFilterClause) => ({
        ...existsFilterClause,
        additionalOnConditions: [...existsFilterClause.additionalOnConditions],
      })),
    );
    cloned.joinClauses.push(
      ...this.joinClauses.map((joinClause) => ({
        ...joinClause,
        additionalOnConditions: [...joinClause.additionalOnConditions],
      })),
    );
    cloned.extraSelectClauses.push(...this.extraSelectClauses);
    cloned.pendingColumnSelections.push(...this.pendingColumnSelections);
    cloned.orderByClauses = [...this.orderByClauses];
    cloned.groupByExpressions = [...this.groupByExpressions];
    cloned.distinctOnExpressions = [...this.distinctOnExpressions];
    cloned.parameters = { ...this.parameters };
    cloned.findOptions = { ...this.findOptions };
    cloned.limitValue = this.limitValue;
    cloned.offsetValue = this.offsetValue;
    cloned.includeDeleted = this.includeDeleted;
    cloned.explicitSelection =
      this.explicitSelection === undefined
        ? undefined
        : [...this.explicitSelection];

    for (const alias of this.aliasesWithRowLevelPermissionApplied) {
      cloned.aliasesWithRowLevelPermissionApplied.add(alias);
    }

    return cloned;
  }

  where(
    condition: WhereConditionLike,
    parameters?: Record<string, unknown>,
  ): this {
    this.whereClauses.length = 0;

    return this.appendWhere('and', condition, parameters);
  }

  copyWhereFrom(source: WorkspaceSelectQueryBuilder): this {
    this.whereClauses.push(...source.whereClauses);
    this.rowAccessConditions.push(...source.rowAccessConditions);
    this.existsFilterClauses.push(
      ...source.existsFilterClauses.map((existsFilterClause) => ({
        ...existsFilterClause,
        additionalOnConditions: [...existsFilterClause.additionalOnConditions],
      })),
    );
    this.parameters = { ...this.parameters, ...source.parameters };

    return this;
  }

  andWhere(
    condition: WhereConditionLike,
    parameters?: Record<string, unknown>,
  ): this {
    return this.appendWhere('and', condition, parameters);
  }

  orWhere(
    condition: WhereConditionLike,
    parameters?: Record<string, unknown>,
  ): this {
    return this.appendWhere('or', condition, parameters);
  }

  addRowAccessCondition(
    condition: string,
    parameters?: Record<string, unknown>,
  ): this {
    if (isDefined(parameters)) {
      this.setParameters(parameters);
    }

    if (condition.length > 0) {
      this.rowAccessConditions.push(condition);
    }

    return this;
  }

  setParameters(parameters: Record<string, unknown>): this {
    for (const parameterName of Object.keys(parameters)) {
      if (RESERVED_PARAMETER_NAMES.includes(parameterName)) {
        throw new TwentyOrmException(
          `Parameter name "${parameterName}" is reserved for pagination`,
          TwentyOrmExceptionCode.INVALID_PARAMETER,
        );
      }
    }

    this.parameters = { ...this.parameters, ...parameters };

    return this;
  }

  setParameter(key: string, value: unknown): this {
    return this.setParameters({ [key]: value });
  }

  getParameters(): Record<string, unknown> {
    return { ...this.parameters };
  }

  setFindOptions(findOptions: FindOptionsLike): this {
    this.findOptions = findOptions;

    return this;
  }

  getFindOptions(): FindOptionsLike {
    return this.findOptions;
  }

  select(selection?: string | string[], alias?: string): this {
    this.extraSelectClauses.length = 0;
    this.pendingColumnSelections.length = 0;

    if (selection === undefined) {
      this.explicitSelection = undefined;

      return this;
    }

    if (Array.isArray(selection)) {
      this.explicitSelection = selection;

      return this;
    }

    if (selection === this.alias && alias === undefined) {
      this.explicitSelection = [...this.tableShape.columnNames];

      return this;
    }

    this.explicitSelection = [];
    this.extraSelectClauses.push({
      expression: this.normaliseColumnExpression(selection),
      alias: alias ?? selection,
    });

    return this;
  }

  addSelect(expression: string, alias?: string): this {
    if (isDefined(alias)) {
      this.extraSelectClauses.push({ expression, alias });

      return this;
    }

    if (/^\w+\.\w+$/.test(expression)) {
      this.pendingColumnSelections.push(expression);

      return this;
    }

    this.extraSelectClauses.push({ expression, alias: expression });

    return this;
  }

  orderBy(
    orderByOrExpression: OrderByConditionLike | string,
    direction: 'ASC' | 'DESC' = 'ASC',
    nulls?: 'NULLS FIRST' | 'NULLS LAST',
  ): this {
    this.orderByClauses = [];

    return this.appendOrderBy(orderByOrExpression, direction, nulls);
  }

  addOrderBy(
    orderByOrExpression: OrderByConditionLike | string,
    direction: 'ASC' | 'DESC' = 'ASC',
    nulls?: 'NULLS FIRST' | 'NULLS LAST',
  ): this {
    return this.appendOrderBy(orderByOrExpression, direction, nulls);
  }

  distinctOn(columns: string[]): this {
    this.distinctOnExpressions = columns.map((column) =>
      this.normaliseColumnExpression(column),
    );

    return this;
  }

  groupBy(expression: string): this {
    this.groupByExpressions = [this.normaliseColumnExpression(expression)];

    return this;
  }

  addGroupBy(expression: string): this {
    this.groupByExpressions.push(this.normaliseColumnExpression(expression));

    return this;
  }

  leftJoin(
    relationPath: string,
    alias: string,
    condition?: string,
    options?: {
      allowToManyJoin?: boolean;
      toManyDedupOrder?: ToManyDedupOrder[];
    },
  ): this {
    return this.addJoin('LEFT', relationPath, alias, condition, options);
  }

  innerJoin(
    relationPath: string,
    alias: string,
    condition?: string,
    options?: {
      allowToManyJoin?: boolean;
      toManyDedupOrder?: ToManyDedupOrder[];
    },
  ): this {
    return this.addJoin('INNER', relationPath, alias, condition, options);
  }

  leftJoinAndSelect(
    relationPath: string,
    alias: string,
    condition?: string,
  ): this {
    return this.addJoin('LEFT', relationPath, alias, condition, {
      select: true,
    });
  }

  innerJoinAndSelect(
    relationPath: string,
    alias: string,
    condition?: string,
  ): this {
    return this.addJoin('INNER', relationPath, alias, condition, {
      select: true,
    });
  }

  private addJoin(
    joinType: 'INNER' | 'LEFT',
    relationPath: string,
    alias: string,
    condition?: string,
    options?: {
      allowToManyJoin?: boolean;
      toManyDedupOrder?: ToManyDedupOrder[];
      select?: boolean;
    },
  ): this {
    const [parentAlias, relationFieldName] = relationPath.split('.');

    if (!isDefined(relationFieldName)) {
      throw new TwentyOrmException(
        `Join path "${relationPath}" must be of the form "<alias>.<relationField>"`,
        TwentyOrmExceptionCode.UNKNOWN_RELATION,
      );
    }

    if (alias === this.alias) {
      throw new TwentyOrmException(
        `Join alias "${alias}" collides with the main query alias`,
        TwentyOrmExceptionCode.INVALID_PARAMETER,
      );
    }

    if (this.joinClauses.some((joinClause) => joinClause.alias === alias)) {
      return this;
    }

    const parentTableShape = this.getTableShapeForAlias(parentAlias);

    if (!isDefined(parentTableShape)) {
      throw new TwentyOrmException(
        `Join path "${relationPath}" references "${parentAlias}", which is neither the main alias nor a joined alias`,
        TwentyOrmExceptionCode.UNKNOWN_RELATION,
      );
    }

    const relationShape =
      parentTableShape.relationShapeByFieldName[relationFieldName];

    if (!isDefined(relationShape)) {
      throw new TwentyOrmException(
        `Relation "${relationFieldName}" does not exist on "${parentTableShape.nameSingular}"`,
        TwentyOrmExceptionCode.UNKNOWN_RELATION,
      );
    }

    const targetTableShape = this.context.tableShapeByObjectMetadataId(
      relationShape.targetObjectMetadataId,
    );

    const joinColumnName = relationShape.joinColumnName;

    const toManyJoin = isDefined(joinColumnName)
      ? undefined
      : this.buildToManyJoin({
          parentAlias,
          alias,
          targetTableShape,
          targetFieldMetadataId: relationShape.targetFieldMetadataId,
        });

    const shouldJoinDedupedToMany = options?.allowToManyJoin === true;

    this.joinClauses.push({
      alias,
      parentAlias,
      relationFieldName,
      targetTableShape,
      relationType: relationShape.relationType,
      joinType,
      isSelected: options?.select === true,
      condition: isDefined(joinColumnName)
        ? (condition ??
          `${this.quoteColumn(parentAlias, joinColumnName)} = ${this.quoteColumn(alias, 'id')}`)
        : shouldJoinDedupedToMany
          ? (condition ?? toManyJoin?.condition)
          : undefined,
      toManyForeignKeyColumnName: shouldJoinDedupedToMany
        ? toManyJoin?.foreignKeyColumnName
        : undefined,
      toManyPlainCondition: condition ?? toManyJoin?.condition,
      toManyDedupOrder: shouldJoinDedupedToMany
        ? options?.toManyDedupOrder
        : undefined,
      additionalOnConditions: [],
    });

    return this;
  }

  private getTableShapeForAlias(
    alias: string,
  ): WorkspaceTableShape | undefined {
    if (alias === this.alias) {
      return this.tableShape;
    }

    return this.joinClauses.find((joinClause) => joinClause.alias === alias)
      ?.targetTableShape;
  }

  private resolveColumnSelections(): ColumnSelection[] {
    return this.pendingColumnSelections.map((expression) => {
      const [alias, columnName] = expression.split('.');
      const tableShape = this.getTableShapeForAlias(alias);

      if (!isDefined(tableShape)) {
        throw new TwentyOrmException(
          `Selection "${expression}" references "${alias}", which is neither the main alias nor a joined alias`,
          TwentyOrmExceptionCode.UNKNOWN_RELATION,
        );
      }

      if (!isDefined(tableShape.columnShapeByColumnName[columnName])) {
        throw new TwentyOrmException(
          `Column "${columnName}" does not exist on "${tableShape.nameSingular}"`,
          TwentyOrmExceptionCode.UNKNOWN_COLUMN,
        );
      }

      return { alias, columnName };
    });
  }

  private buildToManyJoin({
    parentAlias,
    alias,
    targetTableShape,
    targetFieldMetadataId,
  }: {
    parentAlias: string;
    alias: string;
    targetTableShape: WorkspaceTableShape;
    targetFieldMetadataId: string | null;
  }): { condition: string; foreignKeyColumnName: string } | undefined {
    if (!isDefined(targetFieldMetadataId)) {
      return undefined;
    }

    const inverseRelationShape = Object.values(
      targetTableShape.relationShapeByFieldName,
    ).find(
      (relationShape) =>
        relationShape.fieldMetadataId === targetFieldMetadataId,
    );

    const foreignKeyColumnName = inverseRelationShape?.joinColumnName;

    if (!isDefined(foreignKeyColumnName)) {
      return undefined;
    }

    return {
      condition: `${this.quoteColumn(alias, foreignKeyColumnName)} = ${this.quoteColumn(parentAlias, 'id')}`,
      foreignKeyColumnName,
    };
  }

  withDeleted(): this {
    this.includeDeleted = true;

    return this;
  }

  limit(count: number): this {
    this.limitValue = count;

    return this;
  }

  offset(count: number): this {
    this.offsetValue = count;

    return this;
  }

  take(count: number): this {
    return this.limit(count);
  }

  skip(count: number): this {
    return this.offset(count);
  }

  getQuery(): string {
    return this.buildSelectStatement().sql;
  }

  getQueryAndParameters(): [string, unknown[]] {
    const { sql, parameters } = this.buildSelectStatement();
    const compiled = compileNamedParameters(sql, parameters);

    return [compiled.text, compiled.values];
  }

  private materializeEntities<T extends ObjectLiteral>(
    rows: Record<string, unknown>[],
  ): T[] {
    const columnNameByResultAlias = this.buildColumnNameByResultAlias();

    const mapRowToEntity = createRowToEntityMapper<T>(columnNameByResultAlias);

    return rows.map(mapRowToEntity);
  }

  async getMany<T extends ObjectLiteral>(options?: {
    noFormatting?: boolean;
  }): Promise<T[]> {
    const entities = this.materializeEntities<T>(await this.executeSelect());

    if (options?.noFormatting) {
      return entities;
    }

    return this.context.formatResult<T[]>(entities);
  }

  async getRawAndEntities<T extends Record<string, unknown>>(): Promise<{
    entities: T[];
    raw: Record<string, unknown>[];
  }> {
    const rows = await this.executeSelect({
      types: {
        getTypeParser: (oid, format) =>
          types.getTypeParser(
            oid === types.builtins.TIMESTAMPTZ || oid === types.builtins.DATE
              ? types.builtins.TEXT
              : oid,
            format,
          ),
      },
    });

    return {
      raw: rows,
      entities: this.context.formatResult<T[]>(this.materializeEntities(rows)),
    };
  }

  async getOne<T extends ObjectLiteral>(options?: {
    noFormatting?: boolean;
  }): Promise<T | null> {
    const previousLimit = this.limitValue;

    this.limitValue = 1;

    let rows: Record<string, unknown>[];

    try {
      rows = await this.executeSelect();
    } finally {
      this.limitValue = previousLimit;
    }

    if (rows.length === 0) {
      return null;
    }

    const [entity] = this.materializeEntities<T>([rows[0]]);

    if (options?.noFormatting) {
      return entity;
    }

    return this.context.formatResult<T>(entity);
  }

  async getRawOne<T extends Record<string, unknown>>(): Promise<T | undefined> {
    const previousLimit = this.limitValue;

    this.limitValue = this.limitValue ?? 1;

    try {
      const rows = await this.executeSelect({ allowPlainToManyJoins: true });

      return rows[0] as T | undefined;
    } finally {
      this.limitValue = previousLimit;
    }
  }

  async getRawMany<T extends Record<string, unknown>>(): Promise<T[]> {
    const rows = await this.executeSelect({ allowPlainToManyJoins: true });

    return rows as T[];
  }

  applyRowLevelPermissions(): this {
    this.context.onBeforeExecute(this);

    return this;
  }

  async getCount(): Promise<number> {
    this.context.onBeforeExecute(this);

    const sql = buildCountStatement(
      this.toSelectStatementState({ allowPlainToManyJoins: true }),
    );
    const compiled = compileNamedParameters(sql, this.parameters);
    const rows = await this.context.executor.execute(compiled);

    return Number(rows[0]?.count ?? 0);
  }

  addJoinCondition(alias: string, condition: string): this {
    const joinClause = this.joinClauses.find(
      (candidate) => candidate.alias === alias,
    );

    if (isDefined(joinClause)) {
      joinClause.additionalOnConditions.push(condition);
    }

    for (const existsFilterClause of this.existsFilterClauses) {
      if (existsFilterClause.alias === alias) {
        existsFilterClause.additionalOnConditions.push(condition);
      }
    }

    return this;
  }

  getJoinedTableShape(alias: string): WorkspaceTableShape | undefined {
    return (
      this.joinClauses.find((joinClause) => joinClause.alias === alias)
        ?.targetTableShape ??
      this.existsFilterClauses.find(
        (existsFilterClause) => existsFilterClause.alias === alias,
      )?.targetTableShape
    );
  }

  markRowLevelPermissionApplied(alias: string): boolean {
    if (this.aliasesWithRowLevelPermissionApplied.has(alias)) {
      return false;
    }

    this.aliasesWithRowLevelPermissionApplied.add(alias);

    return true;
  }

  getReferencedColumnNamesByAlias(): Record<string, string[]> {
    const state = this.toSelectStatementState();
    const aliases = collectStatementAliases(state);

    return collectReferencedColumnNames({
      mainAlias: this.alias,
      mainAliasColumnNames: this.buildProjection().mainAliasColumnNames,
      extraSelectClauses: this.extraSelectClauses.map((selectClause) => ({
        ...selectClause,
        expression: quoteQualifiedAliasReferences(
          selectClause.expression,
          aliases,
        ),
      })),
      orderByClauses: this.orderByClauses
        .filter(
          (orderByClause) =>
            !this.extraSelectClauses.some(
              (selectClause) =>
                escapeIdentifier(selectClause.alias) ===
                orderByClause.expression,
            ),
        )
        .map((orderByClause) => ({
          ...orderByClause,
          expression: quoteQualifiedAliasReferences(
            orderByClause.expression,
            aliases,
          ),
        })),
      distinctOnExpressions: this.distinctOnExpressions,
    });
  }

  getSelectedColumnNames(): string[] {
    return this.getReferencedColumnNamesByAlias()[this.alias] ?? [];
  }

  // Not an instance method, so builders from createQueryBuilder expose no write that skips the repository checks
  static toMutationQueryBuilder(
    selectQueryBuilder: WorkspaceSelectQueryBuilder,
    kind: MutationKind,
  ): WorkspaceMutationQueryBuilder {
    if (selectQueryBuilder.joinClauses.length > 0) {
      throw new TwentyOrmException(
        `A mutation cannot carry a relation join; rewrite the filter as an "id IN (subquery)" predicate first`,
        TwentyOrmExceptionCode.UNSUPPORTED_OPERATION,
      );
    }

    // Row-level permission predicates are injected on the select path only, so an EXISTS here would be unfiltered
    if (selectQueryBuilder.existsFilterClauses.length > 0) {
      throw new TwentyOrmException(
        `A mutation cannot carry a relation filter; rewrite the filter as an "id IN (subquery)" predicate first`,
        TwentyOrmExceptionCode.UNSUPPORTED_OPERATION,
      );
    }

    return new WorkspaceMutationQueryBuilder({
      alias: selectQueryBuilder.alias,
      kind,
      context: {
        tableShape: selectQueryBuilder.tableShape,
        executor: selectQueryBuilder.context.executor,
        formatResult: selectQueryBuilder.context.formatResult,
      },
      whereClauses: selectQueryBuilder.whereClauses,
      rowAccessConditions: selectQueryBuilder.rowAccessConditions,
      includeDeleted: selectQueryBuilder.includeDeleted,
      parameters: selectQueryBuilder.parameters,
    });
  }

  private appendWhere(
    operator: 'and' | 'or',
    condition: WhereConditionLike,
    parameters?: Record<string, unknown>,
  ): this {
    if (isDefined(parameters)) {
      this.setParameters(parameters);
    }

    if (isWhereFactoryLike(condition)) {
      const nestedBuilder = new WorkspaceSelectQueryBuilder(
        this.alias,
        this.context,
      );

      condition.whereFactory(nestedBuilder);

      const nestedSql = nestedBuilder.buildWhereExpression({
        includeSoftDeletePredicate: false,
        substituteExistsFilters: false,
      });

      this.setParameters(nestedBuilder.parameters);
      this.existsFilterClauses.push(...nestedBuilder.existsFilterClauses);

      if (nestedSql.length > 0) {
        this.whereClauses.push({
          operator,
          sql: isNegatedWhereFactoryLike(condition)
            ? `NOT (${nestedSql})`
            : `(${nestedSql})`,
        });
      }

      return this;
    }

    if (isObjectWhereLike(condition)) {
      const { sql, parameters: objectParameters } =
        this.buildObjectWhereClause(condition);

      this.setParameters(objectParameters);

      if (sql.length > 0) {
        this.whereClauses.push({ operator, sql: `(${sql})` });
      }

      return this;
    }

    // Dropping an unreadable condition would widen the query, or make a delete hit every row
    if (typeof condition !== 'string') {
      throw new TwentyOrmException(
        'A where condition must be a SQL string, a where object or a where factory',
        TwentyOrmExceptionCode.INVALID_QUERY,
      );
    }

    if (condition.length > 0) {
      this.whereClauses.push({ operator, sql: `(${condition})` });
    }

    return this;
  }

  private buildObjectWhereClause(where: ObjectWhereLike): {
    sql: string;
    parameters: Record<string, unknown>;
  } {
    const conditions: string[] = [];
    const parameters: Record<string, unknown> = {};

    for (const [columnName, value] of Object.entries(where)) {
      if (isDefined(this.tableShape.columnShapeByColumnName[columnName])) {
        conditions.push(
          this.buildValueCondition(
            quoteColumn(this.alias, columnName),
            columnName,
            value,
            parameters,
          ),
        );

        continue;
      }

      const relationShape =
        this.tableShape.relationShapeByFieldName[columnName];

      if (isDefined(relationShape) && isNestedWhereObject(value)) {
        conditions.push(
          this.buildRelationExistsCondition({
            relationFieldName: columnName,
            relationShape,
            applyWhere: (nestedBuilder) => nestedBuilder.where(value),
            parameters,
          }),
        );

        continue;
      }

      const hasCompositeChildColumns = Object.values(
        this.tableShape.columnShapeByColumnName,
      ).some((shape) => shape.compositeParentFieldName === columnName);

      if (
        hasCompositeChildColumns &&
        isDefined(value) &&
        typeof value === 'object' &&
        !InstanceChecker.isFindOperator(value) &&
        !Array.isArray(value)
      ) {
        for (const [subFieldName, subValue] of Object.entries(
          value as Record<string, unknown>,
        )) {
          const compositeColumnName = `${columnName}${pascalCase(subFieldName)}`;

          if (
            !isDefined(
              this.tableShape.columnShapeByColumnName[compositeColumnName],
            )
          ) {
            throw new TwentyOrmException(
              `Column "${compositeColumnName}" does not exist on "${this.tableShape.nameSingular}"`,
              TwentyOrmExceptionCode.UNKNOWN_COLUMN,
            );
          }

          conditions.push(
            this.buildValueCondition(
              quoteColumn(this.alias, compositeColumnName),
              compositeColumnName,
              subValue,
              parameters,
            ),
          );
        }

        continue;
      }

      throw new TwentyOrmException(
        `Column "${columnName}" does not exist on "${this.tableShape.nameSingular}"`,
        TwentyOrmExceptionCode.UNKNOWN_COLUMN,
      );
    }

    return { sql: conditions.join(' AND '), parameters };
  }

  // EXISTS rather than a join so filtering a to-many relation never duplicates root rows
  addRelationExistsFilter({
    relationFieldName,
    applyWhere,
  }: {
    relationFieldName: string;
    applyWhere: (nestedBuilder: WorkspaceSelectQueryBuilder) => void;
  }): string {
    const relationShape =
      this.tableShape.relationShapeByFieldName[relationFieldName];

    if (!isDefined(relationShape)) {
      throw new TwentyOrmException(
        `Unknown relation "${relationFieldName}" on "${this.tableShape.nameSingular}"`,
        TwentyOrmExceptionCode.UNKNOWN_RELATION,
      );
    }

    const parameters: Record<string, unknown> = {};

    const token = this.buildRelationExistsCondition({
      relationFieldName,
      relationShape,
      applyWhere,
      parameters,
    });

    this.setParameters(parameters);

    return token;
  }

  private buildRelationExistsCondition({
    relationFieldName,
    relationShape,
    applyWhere,
    parameters,
  }: {
    relationFieldName: string;
    relationShape: WorkspaceTableShape['relationShapeByFieldName'][string];
    applyWhere: (nestedBuilder: WorkspaceSelectQueryBuilder) => void;
    parameters: Record<string, unknown>;
  }): string {
    const targetTableShape = this.context.tableShapeByObjectMetadataId(
      relationShape.targetObjectMetadataId,
    );

    const alias = this.buildExistsFilterAlias(relationFieldName);

    const correlationCondition = isDefined(relationShape.joinColumnName)
      ? `${this.quoteColumn(this.alias, relationShape.joinColumnName)} = ${this.quoteColumn(alias, 'id')}`
      : this.buildToManyJoin({
          parentAlias: this.alias,
          alias,
          targetTableShape,
          targetFieldMetadataId: relationShape.targetFieldMetadataId,
        })?.condition;

    if (!isDefined(correlationCondition)) {
      throw new TwentyOrmException(
        `Relation "${relationFieldName}" on "${this.tableShape.nameSingular}" cannot be filtered on because its inverse foreign key could not be resolved`,
        TwentyOrmExceptionCode.UNKNOWN_RELATION,
      );
    }

    const nestedBuilder = new WorkspaceSelectQueryBuilder(alias, {
      ...this.context,
      tableShape: targetTableShape,
    });

    applyWhere(nestedBuilder);

    Object.assign(parameters, nestedBuilder.parameters);

    const token = `__ormExistsFilter_${existsFilterSequence++}__`;

    this.existsFilterClauses.push(
      {
        token,
        alias,
        parentAlias: this.alias,
        relationFieldName,
        targetTableShape,
        correlationCondition,
        conditionSql: nestedBuilder.buildWhereExpression({
          includeSoftDeletePredicate: false,
          substituteExistsFilters: false,
        }),
        additionalOnConditions: [],
      },
      ...nestedBuilder.existsFilterClauses,
    );

    return token;
  }

  private buildExistsFilterAlias(relationFieldName: string): string {
    const baseAlias = `${this.alias}_${relationFieldName}_filter`;

    const takenAliases = new Set([
      this.alias,
      ...this.joinClauses.map((joinClause) => joinClause.alias),
      ...this.existsFilterClauses.map(
        (existsFilterClause) => existsFilterClause.alias,
      ),
    ]);

    if (!takenAliases.has(baseAlias)) {
      return baseAlias;
    }

    let suffix = 2;

    while (takenAliases.has(`${baseAlias}_${suffix}`)) {
      suffix++;
    }

    return `${baseAlias}_${suffix}`;
  }

  private buildValueCondition(
    quotedColumn: string,
    columnName: string,
    value: unknown,
    parameters: Record<string, unknown>,
  ): string {
    if (value === null) {
      return `${quotedColumn} IS NULL`;
    }

    const nextParameter = (parameterValue: unknown): string => {
      const parameterName = `ormObjectWhere_${objectWhereParameterSequence++}`;

      parameters[parameterName] = parameterValue;

      return parameterName;
    };

    if (InstanceChecker.isFindOperator(value)) {
      switch (value.type) {
        case 'in':
          return `${quotedColumn} IN (:...${nextParameter(value.value)})`;
        case 'any':
          return `${quotedColumn} = ANY(:${nextParameter(value.value)})`;
        case 'equal':
          return `${quotedColumn} = :${nextParameter(value.value)}`;
        case 'lessThan':
          return `${quotedColumn} < :${nextParameter(value.value)}`;
        case 'lessThanOrEqual':
          return `${quotedColumn} <= :${nextParameter(value.value)}`;
        case 'moreThan':
          return `${quotedColumn} > :${nextParameter(value.value)}`;
        case 'moreThanOrEqual':
          return `${quotedColumn} >= :${nextParameter(value.value)}`;
        case 'like':
          return `${quotedColumn} LIKE :${nextParameter(value.value)}`;
        case 'ilike':
          return `${quotedColumn} ILIKE :${nextParameter(value.value)}`;
        case 'arrayContains':
          return `${quotedColumn} @> :${nextParameter(value.value)}`;
        case 'isNull':
          return `${quotedColumn} IS NULL`;
        case 'between': {
          const [from, to] = value.value as [unknown, unknown];

          return `${quotedColumn} BETWEEN :${nextParameter(
            from,
          )} AND :${nextParameter(to)}`;
        }
        case 'not':
          return `NOT (${this.buildValueCondition(
            quotedColumn,
            columnName,
            value.child ?? value.value,
            parameters,
          )})`;
        case 'and':
        case 'or': {
          const childOperators = value.value as unknown[];
          const separator = value.type === 'and' ? ' AND ' : ' OR ';

          return `(${childOperators
            .map((childOperator) =>
              this.buildValueCondition(
                quotedColumn,
                columnName,
                childOperator,
                parameters,
              ),
            )
            .join(separator)})`;
        }
        case 'raw': {
          const rawValue = value.value as unknown;

          let rawSql: string;

          if (typeof rawValue === 'function') {
            rawSql = (rawValue as (columnAlias: string) => string)(
              quotedColumn,
            );
          } else if (typeof rawValue === 'string') {
            rawSql = rawValue;
          } else {
            rawSql = value.getSql?.(quotedColumn) ?? '';
          }

          Object.assign(parameters, value.objectLiteralParameters ?? {});

          return rawSql;
        }
        default:
          throw new TwentyOrmException(
            `Object where does not support the "${value.type}" operator on "${columnName}"`,
            TwentyOrmExceptionCode.UNSUPPORTED_OPERATION,
          );
      }
    }

    return `${quotedColumn} = :${nextParameter(value)}`;
  }

  private appendOrderBy(
    orderByOrExpression: OrderByConditionLike | string,
    direction: 'ASC' | 'DESC',
    nulls?: 'NULLS FIRST' | 'NULLS LAST',
  ): this {
    this.orderByClauses.push(
      ...buildOrderByClauses({
        orderByOrExpression,
        direction,
        nulls,
        normaliseColumnExpression: (expression) =>
          this.normaliseOrderByExpression(expression),
      }),
    );

    return this;
  }

  private normaliseOrderByExpression(expression: string): string {
    const isBareIdentifier = /^\w+$/.test(expression);

    if (
      isBareIdentifier &&
      this.extraSelectClauses.some(
        (selectClause) => selectClause.alias === expression,
      )
    ) {
      return escapeIdentifier(expression);
    }

    return this.normaliseColumnExpression(expression);
  }

  private async executeSelect(options?: {
    allowPlainToManyJoins?: boolean;
    types?: QueryConfig['types'];
  }): Promise<Record<string, unknown>[]> {
    this.context.onBeforeExecute(this);

    const { sql, parameters } = this.buildSelectStatement(options);
    const compiled = compileNamedParameters(sql, parameters);

    return this.context.executor.execute({
      ...compiled,
      types: options?.types,
    });
  }

  private buildSelectStatement(options?: { allowPlainToManyJoins?: boolean }): {
    sql: string;
    parameters: Record<string, unknown>;
  } {
    const state = this.toSelectStatementState(options);

    return {
      sql: buildSelectStatement(state),
      parameters: { ...this.parameters, ...buildPaginationParameters(state) },
    };
  }

  private buildProjection(): {
    expressions: string[];
    mainAliasColumnNames: string[];
  } {
    return buildProjection(this.toSelectStatementState());
  }

  private buildColumnNameByResultAlias(): Record<string, string> {
    return buildHydrationPathByResultAlias(this.toSelectStatementState());
  }

  private buildWhereExpression(options?: {
    includeSoftDeletePredicate?: boolean;
    substituteExistsFilters?: boolean;
  }): string {
    return buildWhereExpression(this.toSelectStatementState(), options);
  }

  private normaliseColumnExpression(expression: string): string {
    return normaliseColumnExpression(expression, this.alias);
  }

  private quoteColumn(alias: string, columnName: string): string {
    return quoteColumn(alias, columnName);
  }

  private toSelectStatementState(options?: {
    allowPlainToManyJoins?: boolean;
  }): SelectStatementState {
    return {
      alias: this.alias,
      tableShape: this.tableShape,
      findOptions: this.findOptions,
      explicitSelection: this.explicitSelection,
      extraSelectClauses: this.extraSelectClauses,
      columnSelections: this.resolveColumnSelections(),
      joinClauses: this.joinClauses,
      whereClauses: this.whereClauses,
      rowAccessConditions: this.rowAccessConditions,
      existsFilterClauses: this.existsFilterClauses,
      groupByExpressions: this.groupByExpressions,
      orderByClauses: this.orderByClauses,
      distinctOnExpressions: this.distinctOnExpressions,
      includeDeleted: this.includeDeleted,
      allowPlainToManyJoins: options?.allowPlainToManyJoins ?? false,
      limitValue: this.limitValue,
      offsetValue: this.offsetValue,
    };
  }
}