typeorm / typeorm / 19549987525

import { ObjectLiteral } from "../common/ObjectLiteral"

import { QueryRunner } from "../query-runner/QueryRunner"

import { DataSource } from "../data-source/DataSource"

import { QueryBuilderCteOptions } from "./QueryBuilderCte"

import { QueryExpressionMap } from "./QueryExpressionMap"

import { SelectQueryBuilder } from "./SelectQueryBuilder"

import { UpdateQueryBuilder } from "./UpdateQueryBuilder"

import { DeleteQueryBuilder } from "./DeleteQueryBuilder"

import { SoftDeleteQueryBuilder } from "./SoftDeleteQueryBuilder"

import { InsertQueryBuilder } from "./InsertQueryBuilder"

import { RelationQueryBuilder } from "./RelationQueryBuilder"

import { EntityTarget } from "../common/EntityTarget"

import { Alias } from "./Alias"

import { Brackets } from "./Brackets"

import { QueryDeepPartialEntity } from "./QueryPartialEntity"

import { EntityMetadata } from "../metadata/EntityMetadata"

import { ColumnMetadata } from "../metadata/ColumnMetadata"

import { FindOperator } from "../find-options/FindOperator"

import { In } from "../find-options/operator/In"

import { TypeORMError } from "../error"

import { WhereClause, WhereClauseCondition } from "./WhereClause"

import { NotBrackets } from "./NotBrackets"

import { EntityPropertyNotFoundError } from "../error/EntityPropertyNotFoundError"

import { ReturningType } from "../driver/Driver"

import { OracleDriver } from "../driver/oracle/OracleDriver"

import { InstanceChecker } from "../util/InstanceChecker"

import { escapeRegExp } from "../util/escapeRegExp"

// todo: completely cover query builder with tests

// todo: entityOrProperty can be target name. implement proper behaviour if it is.

// todo: check in persistment if id exist on object and throw exception (can be in partial selection?)

// todo: fix problem with long aliases eg getMaxIdentifierLength

// todo: fix replacing in .select("COUNT(post.id) AS cnt") statement

// todo: implement joinAlways in relations and relationId

// todo: finish partial selection

// todo: sugar methods like: .addCount and .selectCount, selectCountAndMap, selectSum, selectSumAndMap, ...

// todo: implement @Select decorator

// todo: add select and map functions

// todo: implement relation/entity loading and setting them into properties within a separate query

// .loadAndMap("post.categories", "post.categories", qb => ...)

// .loadAndMap("post.categories", Category, qb => ...)

/**

 * Allows to build complex sql queries in a fashion way and execute those queries.

 */

export abstract class QueryBuilder<Entity extends ObjectLiteral> {

    readonly "@instanceof" = Symbol.for("QueryBuilder")

    // -------------------------------------------------------------------------

    // Public Properties

    // -------------------------------------------------------------------------

    /**

     * Connection on which QueryBuilder was created.

     */

    readonly connection: DataSource

    /**

     * Contains all properties of the QueryBuilder that needs to be build a final query.

     */

    readonly expressionMap: QueryExpressionMap

    // -------------------------------------------------------------------------

    // Protected Properties

    // -------------------------------------------------------------------------

    /**

     * Query runner used to execute query builder query.

     */

    protected queryRunner?: QueryRunner

    /**

     * If QueryBuilder was created in a subquery mode then its parent QueryBuilder (who created subquery) will be stored here.

     */

    protected parentQueryBuilder: QueryBuilder<any>

    /**

     * Memo to help keep place of current parameter index for `createParameter`

     */

    private parameterIndex = 0

    /**

     * Contains all registered query builder classes.

     */

    private static queryBuilderRegistry: Record<string, any> = {}

    // -------------------------------------------------------------------------

    // Constructor

    // -------------------------------------------------------------------------

    /**

     * QueryBuilder can be initialized from given Connection and QueryRunner objects or from given other QueryBuilder.

     */

    constructor(queryBuilder: QueryBuilder<any>)

    /**

     * QueryBuilder can be initialized from given Connection and QueryRunner objects or from given other QueryBuilder.

     */

    constructor(connection: DataSource, queryRunner?: QueryRunner)

    /**

     * QueryBuilder can be initialized from given Connection and QueryRunner objects or from given other QueryBuilder.

     */

    constructor(

        connectionOrQueryBuilder: DataSource | QueryBuilder<any>,

        queryRunner?: QueryRunner,

    ) {

        if (InstanceChecker.isDataSource(connectionOrQueryBuilder)) {

            this.connection = connectionOrQueryBuilder

            this.queryRunner = queryRunner

            this.expressionMap = new QueryExpressionMap(this.connection)

        } else {

            this.connection = connectionOrQueryBuilder.connection

            this.queryRunner = connectionOrQueryBuilder.queryRunner

            this.expressionMap = connectionOrQueryBuilder.expressionMap.clone()

        }

    }

    static registerQueryBuilderClass(name: string, factory: any) {

        QueryBuilder.queryBuilderRegistry[name] = factory

    }

    // -------------------------------------------------------------------------

    // Abstract Methods

    // -------------------------------------------------------------------------

    /**

     * Gets generated SQL query without parameters being replaced.

     */

    abstract getQuery(): string

    // -------------------------------------------------------------------------

    // Accessors

    // -------------------------------------------------------------------------

    /**

     * Gets the main alias string used in this query builder.

     */

    get alias(): string {

        if (!this.expressionMap.mainAlias)

        return this.expressionMap.mainAlias.name

    }

    // -------------------------------------------------------------------------

    // Public Methods

    // -------------------------------------------------------------------------

    /**

     * Creates SELECT query.

     * Replaces all previous selections if they exist.

     */

    select(): SelectQueryBuilder<Entity>

    /**

     * Creates SELECT query and selects given data.

     * Replaces all previous selections if they exist.

     */

    select(

        selection: string,

        selectionAliasName?: string,

    ): SelectQueryBuilder<Entity>

    /**

     * Creates SELECT query and selects given data.

     * Replaces all previous selections if they exist.

     */

    select(selection: string[]): SelectQueryBuilder<Entity>

    /**

     * Creates SELECT query and selects given data.

     * Replaces all previous selections if they exist.

     */

    select(

        selection?: string | string[],

        selectionAliasName?: string,

    ): SelectQueryBuilder<Entity> {

        this.expressionMap.queryType = "select"

        } else if (selection) {

            this.expressionMap.selects = [

                { selection: selection, aliasName: selectionAliasName },

            ]

        }

        return QueryBuilder.queryBuilderRegistry["SelectQueryBuilder"](this)

    }

    /**

     * Creates INSERT query.

     */

    insert(): InsertQueryBuilder<Entity> {

        this.expressionMap.queryType = "insert"

        return QueryBuilder.queryBuilderRegistry["InsertQueryBuilder"](this)

    }

    /**

     * Creates UPDATE query and applies given update values.

     */

    update(): UpdateQueryBuilder<Entity>

    /**

     * Creates UPDATE query and applies given update values.

     */

    update(

        updateSet: QueryDeepPartialEntity<Entity>,

    ): UpdateQueryBuilder<Entity>

    /**

     * Creates UPDATE query for the given entity and applies given update values.

     */

    update<Entity extends ObjectLiteral>(

        entity: EntityTarget<Entity>,

        updateSet?: QueryDeepPartialEntity<Entity>,

    ): UpdateQueryBuilder<Entity>

    /**

     * Creates UPDATE query for the given table name and applies given update values.

     */

    update(

        tableName: string,

        updateSet?: QueryDeepPartialEntity<Entity>,

    ): UpdateQueryBuilder<Entity>

    /**

     * Creates UPDATE query and applies given update values.

     */

    update(

        entityOrTableNameUpdateSet?: EntityTarget<any> | ObjectLiteral,

        maybeUpdateSet?: ObjectLiteral,

    ): UpdateQueryBuilder<any> {

        const updateSet = maybeUpdateSet

            : (entityOrTableNameUpdateSet as ObjectLiteral | undefined)

        entityOrTableNameUpdateSet = InstanceChecker.isEntitySchema(

            entityOrTableNameUpdateSet,

        )

            : entityOrTableNameUpdateSet

        if (

            typeof entityOrTableNameUpdateSet === "function" ||

            typeof entityOrTableNameUpdateSet === "string"

        ) {

            const mainAlias = this.createFromAlias(entityOrTableNameUpdateSet)

            this.expressionMap.setMainAlias(mainAlias)

        }

        this.expressionMap.queryType = "update"

        this.expressionMap.valuesSet = updateSet

        return QueryBuilder.queryBuilderRegistry["UpdateQueryBuilder"](this)

    }

    /**

     * Creates DELETE query.

     */

    delete(): DeleteQueryBuilder<Entity> {

        this.expressionMap.queryType = "delete"

        return QueryBuilder.queryBuilderRegistry["DeleteQueryBuilder"](this)

    }

    softDelete(): SoftDeleteQueryBuilder<any> {

        this.expressionMap.queryType = "soft-delete"

        return QueryBuilder.queryBuilderRegistry["SoftDeleteQueryBuilder"](this)

    }

    restore(): SoftDeleteQueryBuilder<any> {

        this.expressionMap.queryType = "restore"

        return QueryBuilder.queryBuilderRegistry["SoftDeleteQueryBuilder"](this)

    }

    /**

     * Sets entity's relation with which this query builder gonna work.

     */

    relation(propertyPath: string): RelationQueryBuilder<Entity>

    /**

     * Sets entity's relation with which this query builder gonna work.

     */

    relation<T extends ObjectLiteral>(

        entityTarget: EntityTarget<T>,

        propertyPath: string,

    ): RelationQueryBuilder<T>

    /**

     * Sets entity's relation with which this query builder gonna work.

     */

    relation(

        entityTargetOrPropertyPath: Function | string,

        maybePropertyPath?: string,

    ): RelationQueryBuilder<Entity> {

        const entityTarget =

        const propertyPath =

            arguments.length === 2

                ? (maybePropertyPath as string)

        this.expressionMap.queryType = "relation"

        this.expressionMap.relationPropertyPath = propertyPath

        if (entityTarget) {

            const mainAlias = this.createFromAlias(entityTarget)

            this.expressionMap.setMainAlias(mainAlias)

        }

        return QueryBuilder.queryBuilderRegistry["RelationQueryBuilder"](this)

    }

    /**

     * Checks if given relation exists in the entity.

     * Returns true if relation exists, false otherwise.

     *

     * todo: move this method to manager? or create a shortcut?

     */

    hasRelation<T>(target: EntityTarget<T>, relation: string): boolean

    /**

     * Checks if given relations exist in the entity.

     * Returns true if relation exists, false otherwise.

     *

     * todo: move this method to manager? or create a shortcut?

     */

    hasRelation<T>(target: EntityTarget<T>, relation: string[]): boolean

    /**

     * Checks if given relation or relations exist in the entity.

     * Returns true if relation exists, false otherwise.

     *

     * todo: move this method to manager? or create a shortcut?

     */

    hasRelation<T>(

    /**

     * Check the existence of a parameter for this query builder.

     */

    hasParameter(key: string): boolean {

        return (

            this.parentQueryBuilder?.hasParameter(key) ||

            key in this.expressionMap.parameters

        )

    }

    /**

     * Sets parameter name and its value.

     *

     * The key for this parameter may contain numbers, letters, underscores, or periods.

     */

    setParameter(key: string, value: any): this {

        if (typeof value === "function") {

            throw new TypeORMError(

                `Function parameter isn't supported in the parameters. Please check "${key}" parameter.`,

            )

        }

            throw new TypeORMError(

                "QueryBuilder parameter keys may only contain numbers, letters, underscores, or periods.",

            )

        }

        if (this.parentQueryBuilder) {

            this.parentQueryBuilder.setParameter(key, value)

        }

        this.expressionMap.parameters[key] = value

        return this

    }

    /**

     * Adds all parameters from the given object.

     */

    setParameters(parameters: ObjectLiteral): this {

        for (const [key, value] of Object.entries(parameters)) {

            this.setParameter(key, value)

        }

        return this

    }

    protected createParameter(value: any): string {

        let parameterName

        do {

            parameterName = `orm_param_${this.parameterIndex++}`

        } while (this.hasParameter(parameterName))

        this.setParameter(parameterName, value)

        return `:${parameterName}`

    }

    /**

     * Adds native parameters from the given object.

     *

     * @deprecated Use `setParameters` instead

     */

    setNativeParameters(parameters: ObjectLiteral): this {

        // set parent query builder parameters as well in sub-query mode

        Object.keys(parameters).forEach((key) => {

        })

        return this

    }

    /**

     * Gets all parameters.

     */

    getParameters(): ObjectLiteral {

        const parameters: ObjectLiteral = Object.assign(

            {},

            this.expressionMap.parameters,

        )

        // add discriminator column parameter if it exist

        if (

            this.expressionMap.mainAlias &&

            this.expressionMap.mainAlias.hasMetadata

        ) {

            const metadata = this.expressionMap.mainAlias!.metadata

            if (metadata.discriminatorColumn && metadata.parentEntityMetadata) {

                const values = metadata.childEntityMetadatas

                    .filter(

                        (childMetadata) => childMetadata.discriminatorColumn,

                    )

                    .map((childMetadata) => childMetadata.discriminatorValue)

                values.push(metadata.discriminatorValue)

                parameters["discriminatorColumnValues"] = values

            }

        }

        return parameters

    }

    /**

     * Prints sql to stdout using console.log.

     */

    printSql(): this {

    /**

     * Gets generated sql that will be executed.

     * Parameters in the query are escaped for the currently used driver.

     */

    getSql(): string {

        return this.getQueryAndParameters()[0]

    }

    /**

     * Gets query to be executed with all parameters used in it.

     */

    getQueryAndParameters(): [string, any[]] {

        // this execution order is important because getQuery method generates this.expressionMap.nativeParameters values

        const query = this.getQuery()

        const parameters = this.getParameters()

        return this.connection.driver.escapeQueryWithParameters(

            query,

            parameters,

            this.expressionMap.nativeParameters,

        )

    }

    /**

     * Executes sql generated by query builder and returns raw database results.

     */

    async execute(): Promise<any> {

        const [sql, parameters] = this.getQueryAndParameters()

        const queryRunner = this.obtainQueryRunner()

        try {

            return await queryRunner.query(sql, parameters) // await is needed here because we are using finally

        } finally {

            if (queryRunner !== this.queryRunner) {

                // means we created our own query runner

                await queryRunner.release()

            }

        }

    }

    /**

     * Creates a completely new query builder.

     * Uses same query runner as current QueryBuilder.

     */

    createQueryBuilder(queryRunner?: QueryRunner): this {

        return new (this.constructor as any)(

            this.connection,

            queryRunner ?? this.queryRunner,

        )

    }

    /**

     * Clones query builder as it is.

     * Note: it uses new query runner, if you want query builder that uses exactly same query runner,

     * you can create query builder using its constructor, for example new SelectQueryBuilder(queryBuilder)

     * where queryBuilder is cloned QueryBuilder.

     */

    clone(): this {

        return new (this.constructor as any)(this)

    }

    /**

     * Includes a Query comment in the query builder.  This is helpful for debugging purposes,

     * such as finding a specific query in the database server's logs, or for categorization using

     * an APM product.

     */

    comment(comment: string): this {

        this.expressionMap.comment = comment

        return this

    }

    /**

     * Disables escaping.

     */

    disableEscaping(): this {

        this.expressionMap.disableEscaping = false

        return this

    }

    /**

     * Escapes table name, column name or alias name using current database's escaping character.

     */

    escape(name: string): string {

        if (!this.expressionMap.disableEscaping) return name

        return this.connection.driver.escape(name)

    }

    /**

     * Sets or overrides query builder's QueryRunner.

     */

    setQueryRunner(queryRunner: QueryRunner): this {

        this.queryRunner = queryRunner

        return this

    }

    /**

     * Indicates if listeners and subscribers must be called before and after query execution.

     * Enabled by default.

     */

    callListeners(enabled: boolean): this {

        this.expressionMap.callListeners = enabled

        return this

    }

    /**

     * If set to true the query will be wrapped into a transaction.

     */

    useTransaction(enabled: boolean): this {

        this.expressionMap.useTransaction = enabled

        return this

    }

    /**

     * Adds CTE to query

     */

    addCommonTableExpression(

        queryBuilder: QueryBuilder<any> | string,

        alias: string,

        options?: QueryBuilderCteOptions,

    ): this {

        this.expressionMap.commonTableExpressions.push({

            queryBuilder,

            alias,

        })

        return this

    }

    // -------------------------------------------------------------------------

    // Protected Methods

    // -------------------------------------------------------------------------

    /**

     * Gets escaped table name with schema name if SqlServer driver used with custom

     * schema name, otherwise returns escaped table name.

     */

    protected getTableName(tablePath: string): string {

        return tablePath

            .split(".")

            .map((i) => {

                // this condition need because in SQL Server driver when custom database name was specified and schema name was not, we got `dbName..tableName` string, and doesn't need to escape middle empty string

                return this.escape(i)

            })

            .join(".")

    }

    /**

     * Gets name of the table where insert should be performed.

     */

    protected getMainTableName(): string {

        if (!this.expressionMap.mainAlias)

        if (this.expressionMap.mainAlias.hasMetadata)

            return this.expressionMap.mainAlias.metadata.tablePath

        return this.expressionMap.mainAlias.tablePath!

    }

    /**

     * Specifies FROM which entity's table select/update/delete will be executed.

     * Also sets a main string alias of the selection data.

     */

    protected createFromAlias(

        entityTarget:

            | EntityTarget<any>

            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),

        aliasName?: string,

    ): Alias {

        // if table has a metadata then find it to properly escape its properties

        // const metadata = this.connection.entityMetadatas.find(metadata => metadata.tableName === tableName);

        if (this.connection.hasMetadata(entityTarget)) {

            const metadata = this.connection.getMetadata(entityTarget)

            return this.expressionMap.createAlias({

                type: "from",

                name: aliasName,

                metadata: this.connection.getMetadata(entityTarget),

                tablePath: metadata.tablePath,

            })

        } else {

            if (typeof entityTarget === "string") {

                const isSubquery =

                    entityTarget.substr(0, 1) === "(" &&

                    entityTarget.substr(-1) === ")"

                return this.expressionMap.createAlias({

                    type: "from",

                    name: aliasName,

                    tablePath: !isSubquery

                        ? (entityTarget as string)

                        : undefined,

                    subQuery: isSubquery ? entityTarget : undefined,

                })

            }

            const subQueryBuilder: SelectQueryBuilder<any> = (

                entityTarget as any

            )((this as any as SelectQueryBuilder<any>).subQuery())

            this.setParameters(subQueryBuilder.getParameters())

            const subquery = subQueryBuilder.getQuery()

            return this.expressionMap.createAlias({

                type: "from",

                name: aliasName,

                subQuery: subquery,

            })

        }

    }

    /**

     * @deprecated this way of replace property names is too slow.

     *  Instead, we'll replace property names at the end - once query is build.

     */

    protected replacePropertyNames(statement: string) {

        return statement

    }

    /**

     * Replaces all entity's propertyName to name in the given SQL string.

     */

    protected replacePropertyNamesForTheWholeQuery(statement: string) {

        const replacements: { [key: string]: { [key: string]: string } } = {}

        for (const alias of this.expressionMap.aliases) {

            if (!alias.hasMetadata) continue

            const replaceAliasNamePrefix =

                this.expressionMap.aliasNamePrefixingEnabled && alias.name

                    ? `${alias.name}.`

                    : ""

            if (!replacements[replaceAliasNamePrefix]) {

                replacements[replaceAliasNamePrefix] = {}

            }

            // Insert & overwrite the replacements from least to most relevant in our replacements object.

            // To do this we iterate and overwrite in the order of relevance.

            // Least to Most Relevant:

            // * Relation Property Path to first join column key

            // * Relation Property Path + Column Path

            // * Column Database Name

            // * Column Property Name

            // * Column Property Path

            for (const relation of alias.metadata.relations) {

                if (relation.joinColumns.length > 0)

                    replacements[replaceAliasNamePrefix][

                        relation.propertyPath

                    ] = relation.joinColumns[0].databaseName

            }

            for (const relation of alias.metadata.relations) {

                const allColumns = [

                    ...relation.joinColumns,

                    ...relation.inverseJoinColumns,

                ]

                for (const joinColumn of allColumns) {

                    const propertyKey = `${relation.propertyPath}.${

                        joinColumn.referencedColumn!.propertyPath

                    }`

                    replacements[replaceAliasNamePrefix][propertyKey] =

                        joinColumn.databaseName

                }

            }

            for (const column of alias.metadata.columns) {

                replacements[replaceAliasNamePrefix][column.databaseName] =

                    column.databaseName

            }

            for (const column of alias.metadata.columns) {

                replacements[replaceAliasNamePrefix][column.propertyName] =

                    column.databaseName

            }

            for (const column of alias.metadata.columns) {

                replacements[replaceAliasNamePrefix][column.propertyPath] =

                    column.databaseName

            }

        }

        const replacementKeys = Object.keys(replacements)

        const replaceAliasNamePrefixes = replacementKeys

            .map((key) => escapeRegExp(key))

            .join("|")

        if (replacementKeys.length > 0) {

            statement = statement.replace(

                new RegExp(

                    // Avoid a lookbehind here since it's not well supported

                    `([ =(]|^.{0})` + // any of ' =(' or start of line

                        // followed by our prefix, e.g. 'tablename.' or ''

                        `${

                            replaceAliasNamePrefixes

                                ? "(" + replaceAliasNamePrefixes + ")"

                                : ""

                        }([^ =(),]+)` + // a possible property name: sequence of anything but ' =(),'

                        // terminated by ' =),' or end of line

                        `(?=[ =),]|.{0}$)`,

                    "gm",

                ),

                (...matches) => {

                    let match: string, pre: string, p: string

                    if (replaceAliasNamePrefixes) {

                        match = matches[0]

                        pre = matches[1]

                        p = matches[3]

                        if (replacements[matches[2]][p]) {

                            return `${pre}${this.escape(

                                matches[2].substring(0, matches[2].length - 1),

                            )}.${this.escape(replacements[matches[2]][p])}`

                        }

                    } else {

                        match = matches[0]

                        pre = matches[1]

                        p = matches[2]

                        if (replacements[""][p]) {

                            return `${pre}${this.escape(replacements[""][p])}`

                        }

                    }

                    return match

                },

            )

        }

        return statement

    }

    protected createComment(): string {

        if (!this.expressionMap.comment) {

            return ""

        }

        // ANSI SQL 2003 support C style comments - comments that start with `/*` and end with `*/`

        // In some dialects query nesting is available - but not all.  Because of this, we'll need

        // to scrub "ending" characters from the SQL but otherwise we can leave everything else

        // as-is and it should be valid.

        return `/* ${this.expressionMap.comment.replace(/\*\//g, "")} */ `

    }

    /**

     * Time travel queries for CockroachDB

     */

    protected createTimeTravelQuery(): string {

        if (

            this.expressionMap.queryType === "select" &&

            this.expressionMap.timeTravel

            return ` AS OF SYSTEM TIME ${this.expressionMap.timeTravel}`

        }

        return ""

    }

    /**

     * Creates "WHERE" expression.

     */

    protected createWhereExpression() {

        const conditionsArray = []

        const whereExpression = this.createWhereClausesExpression(

            this.expressionMap.wheres,

        )

        if (whereExpression.length > 0 && whereExpression !== "1=1") {

            conditionsArray.push(this.replacePropertyNames(whereExpression))

        }

        if (this.expressionMap.mainAlias!.hasMetadata) {

            const metadata = this.expressionMap.mainAlias!.metadata

            // Adds the global condition of "non-deleted" for the entity with delete date columns in select query.

            if (

                this.expressionMap.queryType === "select" &&

                !this.expressionMap.withDeleted &&

                metadata.deleteDateColumn

            ) {

                const column = this.expressionMap.aliasNamePrefixingEnabled

                    ? this.expressionMap.mainAlias!.name +

                      "." +

                      metadata.deleteDateColumn.propertyName

                const condition = `${this.replacePropertyNames(column)} IS NULL`

                conditionsArray.push(condition)

            }

            if (metadata.discriminatorColumn && metadata.parentEntityMetadata) {