SelectQueryBuilder.ts

import { RawSqlResultsToEntityTransformer } from "./transformer/RawSqlResultsToEntityTransformer"
import { ObjectLiteral } from "../common/ObjectLiteral"
import { PessimisticLockTransactionRequiredError } from "../error/PessimisticLockTransactionRequiredError"
import { NoVersionOrUpdateDateColumnError } from "../error/NoVersionOrUpdateDateColumnError"
import { OptimisticLockVersionMismatchError } from "../error/OptimisticLockVersionMismatchError"
import { OptimisticLockCanNotBeUsedError } from "../error/OptimisticLockCanNotBeUsedError"
import { JoinAttribute } from "./JoinAttribute"
import { RelationIdAttribute } from "./relation-id/RelationIdAttribute"
import { RelationCountAttribute } from "./relation-count/RelationCountAttribute"
import { RelationIdLoader } from "./relation-id/RelationIdLoader"
import { RelationIdMetadataToAttributeTransformer } from "./relation-id/RelationIdMetadataToAttributeTransformer"
import { RelationCountLoader } from "./relation-count/RelationCountLoader"
import { RelationCountMetadataToAttributeTransformer } from "./relation-count/RelationCountMetadataToAttributeTransformer"
import { QueryBuilder } from "./QueryBuilder"
import { ReadStream } from "../platform/PlatformTools"
import { LockNotSupportedOnGivenDriverError } from "../error/LockNotSupportedOnGivenDriverError"
import { MysqlDriver } from "../driver/mysql/MysqlDriver"
import { SelectQuery } from "./SelectQuery"
import { EntityMetadata } from "../metadata/EntityMetadata"
import { ColumnMetadata } from "../metadata/ColumnMetadata"
import { OrderByCondition } from "../find-options/OrderByCondition"
import { QueryExpressionMap } from "./QueryExpressionMap"
import { EntityTarget } from "../common/EntityTarget"
import { QueryRunner } from "../query-runner/QueryRunner"
import { WhereExpressionBuilder } from "./WhereExpressionBuilder"
import { Brackets } from "./Brackets"
import { QueryResultCacheOptions } from "../cache/QueryResultCacheOptions"
import { OffsetWithoutLimitNotSupportedError } from "../error/OffsetWithoutLimitNotSupportedError"
import { SelectQueryBuilderOption } from "./SelectQueryBuilderOption"
import { ObjectUtils } from "../util/ObjectUtils"
import { DriverUtils } from "../driver/DriverUtils"
import { EntityNotFoundError } from "../error/EntityNotFoundError"
import { TypeORMError } from "../error"
import { FindManyOptions } from "../find-options/FindManyOptions"
import { FindOptionsSelect } from "../find-options/FindOptionsSelect"
import { RelationMetadata } from "../metadata/RelationMetadata"
import { FindOptionsOrder } from "../find-options/FindOptionsOrder"
import { FindOptionsWhere } from "../find-options/FindOptionsWhere"
import { FindOptionsUtils } from "../find-options/FindOptionsUtils"
import { FindOptionsRelations } from "../find-options/FindOptionsRelations"
import { ApplyValueTransformers } from "../util/ApplyValueTransformers"
import { OrmUtils } from "../util/OrmUtils"
import { EntityPropertyNotFoundError } from "../error/EntityPropertyNotFoundError"
import { AuroraMysqlDriver } from "../driver/aurora-mysql/AuroraMysqlDriver"
import { InstanceChecker } from "../util/InstanceChecker"

/**
 * Allows to build complex sql queries in a fashion way and execute those queries.
 */
export class SelectQueryBuilder<Entity>
    extends QueryBuilder<Entity>
    implements WhereExpressionBuilder
{
    readonly "@instanceof" = Symbol.for("SelectQueryBuilder")

    protected findOptions: FindManyOptions = {}
    protected selects: string[] = []
    protected joins: {
        type: "inner" | "left"
        alias: string
        parentAlias: string
        relationMetadata: RelationMetadata
        select: boolean
        selection: FindOptionsSelect<any> | undefined
    }[] = []
    protected conditions: string = ""
    protected orderBys: {
        alias: string
        direction: "ASC" | "DESC"
        nulls?: "NULLS FIRST" | "NULLS LAST"
    }[] = []
    protected relationMetadatas: RelationMetadata[] = []

    // -------------------------------------------------------------------------
    // Public Implemented Methods
    // -------------------------------------------------------------------------

    /**
     * Gets generated SQL query without parameters being replaced.
     */
    getQuery(): string {
        let sql = this.createComment()
        sql += this.createCteExpression()
        sql += this.createSelectExpression()
        sql += this.createJoinExpression()
        sql += this.createWhereExpression()
        sql += this.createGroupByExpression()
        sql += this.createHavingExpression()
        sql += this.createOrderByExpression()
        sql += this.createLimitOffsetExpression()
        sql += this.createLockExpression()
        sql = sql.trim()
        if (this.expressionMap.subQuery) sql = "(" + sql + ")"
        return sql
    }

    // -------------------------------------------------------------------------
    // Public Methods
    // -------------------------------------------------------------------------

    setFindOptions(findOptions: FindManyOptions<Entity>) {
        this.findOptions = findOptions
        this.applyFindOptions()
        return this
    }

    /**
     * Creates a subquery - query that can be used inside other queries.
     */
    subQuery(): SelectQueryBuilder<any> {
        const qb = this.createQueryBuilder()
        qb.expressionMap.subQuery = true
        qb.parentQueryBuilder = this
        return qb
    }

    /**
     * Creates SELECT query.
     * Replaces all previous selections if they exist.
     */
    select(): this

    /**
     * Creates SELECT query.
     * Replaces all previous selections if they exist.
     */
    select(
        selection: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>,
        selectionAliasName?: string,
    ): this

    /**
     * Creates SELECT query and selects given data.
     * Replaces all previous selections if they exist.
     */
    select(selection: string, selectionAliasName?: string): this

    /**
     * Creates SELECT query and selects given data.
     * Replaces all previous selections if they exist.
     */
    select(selection: string[]): this

    /**
     * Creates SELECT query and selects given data.
     * Replaces all previous selections if they exist.
     */
    select(
        selection?:
            | string
            | string[]
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        selectionAliasName?: string,
    ): SelectQueryBuilder<Entity> {
        this.expressionMap.queryType = "select"
        if (Array.isArray(selection)) {
            this.expressionMap.selects = selection.map((selection) => ({
                selection: selection,
            }))
        } else if (typeof selection === "function") {
            const subQueryBuilder = selection(this.subQuery())
            this.setParameters(subQueryBuilder.getParameters())
            this.expressionMap.selects.push({
                selection: subQueryBuilder.getQuery(),
                aliasName: selectionAliasName,
            })
        } else if (selection) {
            this.expressionMap.selects = [
                { selection: selection, aliasName: selectionAliasName },
            ]
        }

        return this
    }

    /**
     * Adds new selection to the SELECT query.
     */
    addSelect(
        selection: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>,
        selectionAliasName?: string,
    ): this

    /**
     * Adds new selection to the SELECT query.
     */
    addSelect(selection: string, selectionAliasName?: string): this

    /**
     * Adds new selection to the SELECT query.
     */
    addSelect(selection: string[]): this

    /**
     * Adds new selection to the SELECT query.
     */
    addSelect(
        selection:
            | string
            | string[]
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        selectionAliasName?: string,
    ): this {
        if (!selection) return this

        if (Array.isArray(selection)) {
            this.expressionMap.selects = this.expressionMap.selects.concat(
                selection.map((selection) => ({ selection: selection })),
            )
        } else if (typeof selection === "function") {
            const subQueryBuilder = selection(this.subQuery())
            this.setParameters(subQueryBuilder.getParameters())
            this.expressionMap.selects.push({
                selection: subQueryBuilder.getQuery(),
                aliasName: selectionAliasName,
            })
        } else if (selection) {
            this.expressionMap.selects.push({
                selection: selection,
                aliasName: selectionAliasName,
            })
        }

        return this
    }

    /**
     * Set max execution time.
     * @param milliseconds
     */
    maxExecutionTime(milliseconds: number): this {
        this.expressionMap.maxExecutionTime = milliseconds
        return this
    }

    /**
     * Sets whether the selection is DISTINCT.
     */
    distinct(distinct: boolean = true): this {
        this.expressionMap.selectDistinct = distinct
        return this
    }

    /**
     * Sets the distinct on clause for Postgres.
     */
    distinctOn(distinctOn: string[]): this {
        this.expressionMap.selectDistinctOn = distinctOn
        return this
    }

    /**
     * Specifies FROM which entity's table select/update/delete will be executed.
     * Also sets a main string alias of the selection data.
     * Removes all previously set from-s.
     */
    from<T>(
        entityTarget: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>,
        aliasName: string,
    ): SelectQueryBuilder<T>

    /**
     * Specifies FROM which entity's table select/update/delete will be executed.
     * Also sets a main string alias of the selection data.
     * Removes all previously set from-s.
     */
    from<T>(
        entityTarget: EntityTarget<T>,
        aliasName: string,
    ): SelectQueryBuilder<T>

    /**
     * Specifies FROM which entity's table select/update/delete will be executed.
     * Also sets a main string alias of the selection data.
     * Removes all previously set from-s.
     */
    from<T>(
        entityTarget:
            | EntityTarget<T>
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        aliasName: string,
    ): SelectQueryBuilder<T> {
        const mainAlias = this.createFromAlias(entityTarget, aliasName)
        this.expressionMap.setMainAlias(mainAlias)
        return this as any as SelectQueryBuilder<T>
    }

    /**
     * Specifies FROM which entity's table select/update/delete will be executed.
     * Also sets a main string alias of the selection data.
     */
    addFrom<T>(
        entityTarget: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>,
        aliasName: string,
    ): SelectQueryBuilder<T>

    /**
     * Specifies FROM which entity's table select/update/delete will be executed.
     * Also sets a main string alias of the selection data.
     */
    addFrom<T>(
        entityTarget: EntityTarget<T>,
        aliasName: string,
    ): SelectQueryBuilder<T>

    /**
     * Specifies FROM which entity's table select/update/delete will be executed.
     * Also sets a main string alias of the selection data.
     */
    addFrom<T>(
        entityTarget:
            | EntityTarget<T>
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        aliasName: string,
    ): SelectQueryBuilder<T> {
        const alias = this.createFromAlias(entityTarget, aliasName)
        if (!this.expressionMap.mainAlias)
            this.expressionMap.setMainAlias(alias)

        return this as any as SelectQueryBuilder<T>
    }

    /**
     * INNER JOINs (without selection) given subquery.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoin(
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs (without selection) entity's property.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoin(
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs (without selection) given entity's table.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoin(
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs (without selection) given table.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoin(
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs (without selection).
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoin(
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.join("INNER", entityOrProperty, alias, condition, parameters)
        return this
    }

    /**
     * LEFT JOINs (without selection) given subquery.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoin(
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs (without selection) entity's property.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoin(
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs (without selection) entity's table.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoin(
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs (without selection) given table.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoin(
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs (without selection).
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoin(
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.join("LEFT", entityOrProperty, alias, condition, parameters)
        return this
    }

    /**
     * INNER JOINs given subquery and adds all selection properties to SELECT..
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndSelect(
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs entity's property and adds all selection properties to SELECT.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndSelect(
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs entity and adds all selection properties to SELECT.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndSelect(
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs table and adds all selection properties to SELECT.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndSelect(
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs and adds all selection properties to SELECT.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndSelect(
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.addSelect(alias)
        this.innerJoin(entityOrProperty, alias, condition, parameters)
        return this
    }

    /**
     * LEFT JOINs given subquery and adds all selection properties to SELECT..
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndSelect(
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs entity's property and adds all selection properties to SELECT.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndSelect(
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs entity and adds all selection properties to SELECT.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndSelect(
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs table and adds all selection properties to SELECT.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndSelect(
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs and adds all selection properties to SELECT.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndSelect(
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.addSelect(alias)
        this.leftJoin(entityOrProperty, alias, condition, parameters)
        return this
    }

    /**
     * INNER JOINs given subquery, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapMany(
        mapToProperty: string,
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs entity's property, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapMany(
        mapToProperty: string,
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs entity's table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapMany(
        mapToProperty: string,
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapMany(
        mapToProperty: string,
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapMany(
        mapToProperty: string,
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.addSelect(alias)
        this.join(
            "INNER",
            entityOrProperty,
            alias,
            condition,
            parameters,
            mapToProperty,
            true,
        )
        return this
    }

    /**
     * INNER JOINs given subquery, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapOne(
        mapToProperty: string,
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs entity's property, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapOne(
        mapToProperty: string,
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs entity's table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapOne(
        mapToProperty: string,
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapOne(
        mapToProperty: string,
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * INNER JOINs, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    innerJoinAndMapOne(
        mapToProperty: string,
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.addSelect(alias)
        this.join(
            "INNER",
            entityOrProperty,
            alias,
            condition,
            parameters,
            mapToProperty,
            false,
        )
        return this
    }

    /**
     * LEFT JOINs given subquery, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapMany(
        mapToProperty: string,
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs entity's property, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapMany(
        mapToProperty: string,
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs entity's table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapMany(
        mapToProperty: string,
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapMany(
        mapToProperty: string,
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there are multiple rows of selecting data, and mapped result will be an array.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapMany(
        mapToProperty: string,
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.addSelect(alias)
        this.join(
            "LEFT",
            entityOrProperty,
            alias,
            condition,
            parameters,
            mapToProperty,
            true,
        )
        return this
    }

    /**
     * LEFT JOINs given subquery, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapOne(
        mapToProperty: string,
        subQueryFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs entity's property, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * Given entity property should be a relation.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapOne(
        mapToProperty: string,
        property: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs entity's table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapOne(
        mapToProperty: string,
        entity: Function | string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs table, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapOne(
        mapToProperty: string,
        tableName: string,
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this

    /**
     * LEFT JOINs, SELECTs the data returned by a join and MAPs all that data to some entity's property.
     * This is extremely useful when you want to select some data and map it to some virtual property.
     * It will assume that there is a single row of selecting data, and mapped result will be a single selected value.
     * You also need to specify an alias of the joined data.
     * Optionally, you can add condition and parameters used in condition.
     */
    leftJoinAndMapOne(
        mapToProperty: string,
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        alias: string,
        condition?: string,
        parameters?: ObjectLiteral,
    ): this {
        this.addSelect(alias)
        this.join(
            "LEFT",
            entityOrProperty,
            alias,
            condition,
            parameters,
            mapToProperty,
            false,
        )
        return this
    }

    /**
     */
    // selectAndMap(mapToProperty: string, property: string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>)): this;

    /**
     */
    // selectAndMap(mapToProperty: string, entity: Function|string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>)): this;

    /**
     */
    // selectAndMap(mapToProperty: string, tableName: string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>)): this;

    /**
     */
    // selectAndMap(mapToProperty: string, entityOrProperty: Function|string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>)): this {
    //     const select = new SelectAttribute(this.expressionMap);
    //     select.mapToProperty = mapToProperty;
    //     select.entityOrProperty = entityOrProperty;
    //     select.aliasName = aliasName;
    //     select.qbFactory = qbFactory;
    //     return this;
    // }

    /**
     * LEFT JOINs relation id and maps it into some entity's property.
     * Optionally, you can add condition and parameters used in condition.
     */
    loadRelationIdAndMap(
        mapToProperty: string,
        relationName: string,
        options?: { disableMixedMap?: boolean },
    ): this

    /**
     * LEFT JOINs relation id and maps it into some entity's property.
     * Optionally, you can add condition and parameters used in condition.
     */
    loadRelationIdAndMap(
        mapToProperty: string,
        relationName: string,
        alias: string,
        queryBuilderFactory: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
    ): this

    /**
     * LEFT JOINs relation id and maps it into some entity's property.
     * Optionally, you can add condition and parameters used in condition.
     */
    loadRelationIdAndMap(
        mapToProperty: string,
        relationName: string,
        aliasNameOrOptions?: string | { disableMixedMap?: boolean },
        queryBuilderFactory?: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
    ): this {
        const relationIdAttribute = new RelationIdAttribute(this.expressionMap)
        relationIdAttribute.mapToProperty = mapToProperty
        relationIdAttribute.relationName = relationName
        if (typeof aliasNameOrOptions === "string")
            relationIdAttribute.alias = aliasNameOrOptions
        if (
            typeof aliasNameOrOptions === "object" &&
            (aliasNameOrOptions as any).disableMixedMap
        )
            relationIdAttribute.disableMixedMap = true

        relationIdAttribute.queryBuilderFactory = queryBuilderFactory
        this.expressionMap.relationIdAttributes.push(relationIdAttribute)

        if (relationIdAttribute.relation.junctionEntityMetadata) {
            this.expressionMap.createAlias({
                type: "other",
                name: relationIdAttribute.junctionAlias,
                metadata: relationIdAttribute.relation.junctionEntityMetadata,
            })
        }
        return this
    }

    /**
     * Counts number of entities of entity's relation and maps the value into some entity's property.
     * Optionally, you can add condition and parameters used in condition.
     */
    loadRelationCountAndMap(
        mapToProperty: string,
        relationName: string,
        aliasName?: string,
        queryBuilderFactory?: (
            qb: SelectQueryBuilder<any>,
        ) => SelectQueryBuilder<any>,
    ): this {
        const relationCountAttribute = new RelationCountAttribute(
            this.expressionMap,
        )
        relationCountAttribute.mapToProperty = mapToProperty
        relationCountAttribute.relationName = relationName
        relationCountAttribute.alias = aliasName
        relationCountAttribute.queryBuilderFactory = queryBuilderFactory
        this.expressionMap.relationCountAttributes.push(relationCountAttribute)

        this.expressionMap.createAlias({
            type: "other",
            name: relationCountAttribute.junctionAlias,
        })
        if (relationCountAttribute.relation.junctionEntityMetadata) {
            this.expressionMap.createAlias({
                type: "other",
                name: relationCountAttribute.junctionAlias,
                metadata:
                    relationCountAttribute.relation.junctionEntityMetadata,
            })
        }
        return this
    }

    /**
     * Loads all relation ids for all relations of the selected entity.
     * All relation ids will be mapped to relation property themself.
     * If array of strings is given then loads only relation ids of the given properties.
     */
    loadAllRelationIds(options?: {
        relations?: string[]
        disableMixedMap?: boolean
    }): this {
        // todo: add skip relations
        this.expressionMap.mainAlias!.metadata.relations.forEach((relation) => {
            if (
                options !== undefined &&
                options.relations !== undefined &&
                options.relations.indexOf(relation.propertyPath) === -1
            )
                return

            this.loadRelationIdAndMap(
                this.expressionMap.mainAlias!.name +
                    "." +
                    relation.propertyPath,
                this.expressionMap.mainAlias!.name +
                    "." +
                    relation.propertyPath,
                options,
            )
        })
        return this
    }

    /**
     * Sets WHERE condition in the query builder.
     * If you had previously WHERE expression defined,
     * calling this function will override previously set WHERE conditions.
     * Additionally you can add parameters used in where expression.
     */
    where(
        where:
            | Brackets
            | string
            | ((qb: this) => string)
            | ObjectLiteral
            | ObjectLiteral[],
        parameters?: ObjectLiteral,
    ): this {
        this.expressionMap.wheres = [] // don't move this block below since computeWhereParameter can add where expressions
        const condition = this.getWhereCondition(where)
        if (condition)
            this.expressionMap.wheres = [
                { type: "simple", condition: condition },
            ]
        if (parameters) this.setParameters(parameters)
        return this
    }

    /**
     * Adds new AND WHERE condition in the query builder.
     * Additionally you can add parameters used in where expression.
     */
    andWhere(
        where:
            | string
            | Brackets
            | ((qb: this) => string)
            | ObjectLiteral
            | ObjectLiteral[],
        parameters?: ObjectLiteral,
    ): this {
        this.expressionMap.wheres.push({
            type: "and",
            condition: this.getWhereCondition(where),
        })
        if (parameters) this.setParameters(parameters)
        return this
    }

    /**
     * Adds new OR WHERE condition in the query builder.
     * Additionally you can add parameters used in where expression.
     */
    orWhere(
        where:
            | Brackets
            | string
            | ((qb: this) => string)
            | ObjectLiteral
            | ObjectLiteral[],
        parameters?: ObjectLiteral,
    ): this {
        this.expressionMap.wheres.push({
            type: "or",
            condition: this.getWhereCondition(where),
        })
        if (parameters) this.setParameters(parameters)
        return this
    }

    /**
     * Adds new AND WHERE with conditions for the given ids.
     *
     * Ids are mixed.
     * It means if you have single primary key you can pass a simple id values, for example [1, 2, 3].
     * If you have multiple primary keys you need to pass object with property names and values specified,
     * for example [{ firstId: 1, secondId: 2 }, { firstId: 2, secondId: 3 }, ...]
     */
    whereInIds(ids: any | any[]): this {
        return this.where(this.getWhereInIdsCondition(ids))
    }

    /**
     * Adds new AND WHERE with conditions for the given ids.
     *
     * Ids are mixed.
     * It means if you have single primary key you can pass a simple id values, for example [1, 2, 3].
     * If you have multiple primary keys you need to pass object with property names and values specified,
     * for example [{ firstId: 1, secondId: 2 }, { firstId: 2, secondId: 3 }, ...]
     */
    andWhereInIds(ids: any | any[]): this {
        return this.andWhere(this.getWhereInIdsCondition(ids))
    }

    /**
     * Adds new OR WHERE with conditions for the given ids.
     *
     * Ids are mixed.
     * It means if you have single primary key you can pass a simple id values, for example [1, 2, 3].
     * If you have multiple primary keys you need to pass object with property names and values specified,
     * for example [{ firstId: 1, secondId: 2 }, { firstId: 2, secondId: 3 }, ...]
     */
    orWhereInIds(ids: any | any[]): this {
        return this.orWhere(this.getWhereInIdsCondition(ids))
    }

    /**
     * Sets HAVING condition in the query builder.
     * If you had previously HAVING expression defined,
     * calling this function will override previously set HAVING conditions.
     * Additionally you can add parameters used in where expression.
     */
    having(having: string, parameters?: ObjectLiteral): this {
        this.expressionMap.havings.push({ type: "simple", condition: having })
        if (parameters) this.setParameters(parameters)
        return this
    }

    /**
     * Adds new AND HAVING condition in the query builder.
     * Additionally you can add parameters used in where expression.
     */
    andHaving(having: string, parameters?: ObjectLiteral): this {
        this.expressionMap.havings.push({ type: "and", condition: having })
        if (parameters) this.setParameters(parameters)
        return this
    }

    /**
     * Adds new OR HAVING condition in the query builder.
     * Additionally you can add parameters used in where expression.
     */
    orHaving(having: string, parameters?: ObjectLiteral): this {
        this.expressionMap.havings.push({ type: "or", condition: having })
        if (parameters) this.setParameters(parameters)
        return this
    }

    /**
     * Sets GROUP BY condition in the query builder.
     * If you had previously GROUP BY expression defined,
     * calling this function will override previously set GROUP BY conditions.
     */
    groupBy(): this

    /**
     * Sets GROUP BY condition in the query builder.
     * If you had previously GROUP BY expression defined,
     * calling this function will override previously set GROUP BY conditions.
     */
    groupBy(groupBy: string): this

    /**
     * Sets GROUP BY condition in the query builder.
     * If you had previously GROUP BY expression defined,
     * calling this function will override previously set GROUP BY conditions.
     */
    groupBy(groupBy?: string): this {
        if (groupBy) {
            this.expressionMap.groupBys = [groupBy]
        } else {
            this.expressionMap.groupBys = []
        }
        return this
    }

    /**
     * Adds GROUP BY condition in the query builder.
     */
    addGroupBy(groupBy: string): this {
        this.expressionMap.groupBys.push(groupBy)
        return this
    }

    /**
     * Sets ORDER BY condition in the query builder.
     * If you had previously ORDER BY expression defined,
     * calling this function will override previously set ORDER BY conditions.
     *
     * Calling order by without order set will remove all previously set order bys.
     */
    orderBy(): this

    /**
     * Sets ORDER BY condition in the query builder.
     * If you had previously ORDER BY expression defined,
     * calling this function will override previously set ORDER BY conditions.
     */
    orderBy(
        sort: string,
        order?: "ASC" | "DESC",
        nulls?: "NULLS FIRST" | "NULLS LAST",
    ): this

    /**
     * Sets ORDER BY condition in the query builder.
     * If you had previously ORDER BY expression defined,
     * calling this function will override previously set ORDER BY conditions.
     */
    orderBy(order: OrderByCondition): this

    /**
     * Sets ORDER BY condition in the query builder.
     * If you had previously ORDER BY expression defined,
     * calling this function will override previously set ORDER BY conditions.
     */
    orderBy(
        sort?: string | OrderByCondition,
        order: "ASC" | "DESC" = "ASC",
        nulls?: "NULLS FIRST" | "NULLS LAST",
    ): this {
        if (order !== undefined && order !== "ASC" && order !== "DESC")
            throw new TypeORMError(
                `SelectQueryBuilder.addOrderBy "order" can accept only "ASC" and "DESC" values.`,
            )
        if (
            nulls !== undefined &&
            nulls !== "NULLS FIRST" &&
            nulls !== "NULLS LAST"
        )
            throw new TypeORMError(
                `SelectQueryBuilder.addOrderBy "nulls" can accept only "NULLS FIRST" and "NULLS LAST" values.`,
            )

        if (sort) {
            if (typeof sort === "object") {
                this.expressionMap.orderBys = sort as OrderByCondition
            } else {
                if (nulls) {
                    this.expressionMap.orderBys = {
                        [sort as string]: { order, nulls },
                    }
                } else {
                    this.expressionMap.orderBys = { [sort as string]: order }
                }
            }
        } else {
            this.expressionMap.orderBys = {}
        }
        return this
    }

    /**
     * Adds ORDER BY condition in the query builder.
     */
    addOrderBy(
        sort: string,
        order: "ASC" | "DESC" = "ASC",
        nulls?: "NULLS FIRST" | "NULLS LAST",
    ): this {
        if (order !== undefined && order !== "ASC" && order !== "DESC")
            throw new TypeORMError(
                `SelectQueryBuilder.addOrderBy "order" can accept only "ASC" and "DESC" values.`,
            )
        if (
            nulls !== undefined &&
            nulls !== "NULLS FIRST" &&
            nulls !== "NULLS LAST"
        )
            throw new TypeORMError(
                `SelectQueryBuilder.addOrderBy "nulls" can accept only "NULLS FIRST" and "NULLS LAST" values.`,
            )

        if (nulls) {
            this.expressionMap.orderBys[sort] = { order, nulls }
        } else {
            this.expressionMap.orderBys[sort] = order
        }
        return this
    }

    /**
     * Set's LIMIT - maximum number of rows to be selected.
     * NOTE that it may not work as you expect if you are using joins.
     * If you want to implement pagination, and you are having join in your query,
     * then use instead take method instead.
     */
    limit(limit?: number): this {
        this.expressionMap.limit = this.normalizeNumber(limit)
        if (
            this.expressionMap.limit !== undefined &&
            isNaN(this.expressionMap.limit)
        )
            throw new TypeORMError(
                `Provided "limit" value is not a number. Please provide a numeric value.`,
            )

        return this
    }

    /**
     * Set's OFFSET - selection offset.
     * NOTE that it may not work as you expect if you are using joins.
     * If you want to implement pagination, and you are having join in your query,
     * then use instead skip method instead.
     */
    offset(offset?: number): this {
        this.expressionMap.offset = this.normalizeNumber(offset)
        if (
            this.expressionMap.offset !== undefined &&
            isNaN(this.expressionMap.offset)
        )
            throw new TypeORMError(
                `Provided "offset" value is not a number. Please provide a numeric value.`,
            )

        return this
    }

    /**
     * Sets maximal number of entities to take.
     */
    take(take?: number): this {
        this.expressionMap.take = this.normalizeNumber(take)
        if (
            this.expressionMap.take !== undefined &&
            isNaN(this.expressionMap.take)
        )
            throw new TypeORMError(
                `Provided "take" value is not a number. Please provide a numeric value.`,
            )

        return this
    }

    /**
     * Sets number of entities to skip.
     */
    skip(skip?: number): this {
        this.expressionMap.skip = this.normalizeNumber(skip)
        if (
            this.expressionMap.skip !== undefined &&
            isNaN(this.expressionMap.skip)
        )
            throw new TypeORMError(
                `Provided "skip" value is not a number. Please provide a numeric value.`,
            )

        return this
    }

    /**
     * Set certain index to be used by the query.
     *
     * @param index Name of index to be used.
     */
    useIndex(index: string): this {
        this.expressionMap.useIndex = index

        return this
    }

    /**
     * Sets locking mode.
     */
    setLock(lockMode: "optimistic", lockVersion: number | Date): this

    /**
     * Sets locking mode.
     */
    setLock(
        lockMode:
            | "pessimistic_read"
            | "pessimistic_write"
            | "dirty_read"
            | "pessimistic_partial_write"
            | "pessimistic_write_or_fail"
            | "for_no_key_update",
        lockVersion?: undefined,
        lockTables?: string[],
    ): this

    /**
     * Sets locking mode.
     */
    setLock(
        lockMode:
            | "optimistic"
            | "pessimistic_read"
            | "pessimistic_write"
            | "dirty_read"
            | "pessimistic_partial_write"
            | "pessimistic_write_or_fail"
            | "for_no_key_update",
        lockVersion?: number | Date,
        lockTables?: string[],
    ): this {
        this.expressionMap.lockMode = lockMode
        this.expressionMap.lockVersion = lockVersion
        this.expressionMap.lockTables = lockTables
        return this
    }

    /**
     * Disables the global condition of "non-deleted" for the entity with delete date columns.
     */
    withDeleted(): this {
        this.expressionMap.withDeleted = true
        return this
    }

    /**
     * Gets first raw result returned by execution of generated query builder sql.
     */
    async getRawOne<T = any>(): Promise<T | undefined> {
        return (await this.getRawMany())[0]
    }

    /**
     * Gets all raw results returned by execution of generated query builder sql.
     */
    async getRawMany<T = any>(): Promise<T[]> {
        if (this.expressionMap.lockMode === "optimistic")
            throw new OptimisticLockCanNotBeUsedError()

        this.expressionMap.queryEntity = false
        const queryRunner = this.obtainQueryRunner()
        let transactionStartedByUs: boolean = false
        try {
            // start transaction if it was enabled
            if (
                this.expressionMap.useTransaction === true &&
                queryRunner.isTransactionActive === false
            ) {
                await queryRunner.startTransaction()
                transactionStartedByUs = true
            }

            const results = await this.loadRawResults(queryRunner)

            // close transaction if we started it
            if (transactionStartedByUs) {
                await queryRunner.commitTransaction()
            }

            return results
        } catch (error) {
            // rollback transaction if we started it
            if (transactionStartedByUs) {
                try {
                    await queryRunner.rollbackTransaction()
                } catch (rollbackError) {}
            }
            throw error
        } finally {
            if (queryRunner !== this.queryRunner) {
                // means we created our own query runner
                await queryRunner.release()
            }
        }
    }

    /**
     * Executes sql generated by query builder and returns object with raw results and entities created from them.
     */
    async getRawAndEntities<T = any>(): Promise<{
        entities: Entity[]
        raw: T[]
    }> {
        const queryRunner = this.obtainQueryRunner()
        let transactionStartedByUs: boolean = false
        try {
            // start transaction if it was enabled
            if (
                this.expressionMap.useTransaction === true &&
                queryRunner.isTransactionActive === false
            ) {
                await queryRunner.startTransaction()
                transactionStartedByUs = true
            }

            this.expressionMap.queryEntity = true
            const results = await this.executeEntitiesAndRawResults(queryRunner)

            // close transaction if we started it
            if (transactionStartedByUs) {
                await queryRunner.commitTransaction()
            }

            return results
        } catch (error) {
            // rollback transaction if we started it
            if (transactionStartedByUs) {
                try {
                    await queryRunner.rollbackTransaction()
                } catch (rollbackError) {}
            }
            throw error
        } finally {
            if (queryRunner !== this.queryRunner)
                // means we created our own query runner
                await queryRunner.release()
        }
    }

    /**
     * Gets single entity returned by execution of generated query builder sql.
     */
    async getOne(): Promise<Entity | null> {
        const results = await this.getRawAndEntities()
        const result = results.entities[0] as any

        if (
            result &&
            this.expressionMap.lockMode === "optimistic" &&
            this.expressionMap.lockVersion
        ) {
            const metadata = this.expressionMap.mainAlias!.metadata

            if (this.expressionMap.lockVersion instanceof Date) {
                const actualVersion =
                    metadata.updateDateColumn!.getEntityValue(result) // what if columns arent set?
                if (
                    actualVersion.getTime() !==
                    this.expressionMap.lockVersion.getTime()
                )
                    throw new OptimisticLockVersionMismatchError(
                        metadata.name,
                        this.expressionMap.lockVersion,
                        actualVersion,
                    )
            } else {
                const actualVersion =
                    metadata.versionColumn!.getEntityValue(result) // what if columns arent set?
                if (actualVersion !== this.expressionMap.lockVersion)
                    throw new OptimisticLockVersionMismatchError(
                        metadata.name,
                        this.expressionMap.lockVersion,
                        actualVersion,
                    )
            }
        }

        if (result === undefined) {
            return null
        }
        return result
    }

    /**
     * Gets the first entity returned by execution of generated query builder sql or rejects the returned promise on error.
     */
    async getOneOrFail(): Promise<Entity> {
        const entity = await this.getOne()

        if (!entity) {
            throw new EntityNotFoundError(
                this.expressionMap.mainAlias!.target,
                this,
            )
        }

        return entity
    }

    /**
     * Gets entities returned by execution of generated query builder sql.
     */
    async getMany(): Promise<Entity[]> {
        if (this.expressionMap.lockMode === "optimistic")
            throw new OptimisticLockCanNotBeUsedError()

        const results = await this.getRawAndEntities()
        return results.entities
    }

    /**
     * Gets count - number of entities selected by sql generated by this query builder.
     * Count excludes all limitations set by setFirstResult and setMaxResults methods call.
     */
    async getCount(): Promise<number> {
        if (this.expressionMap.lockMode === "optimistic")
            throw new OptimisticLockCanNotBeUsedError()

        const queryRunner = this.obtainQueryRunner()
        let transactionStartedByUs: boolean = false
        try {
            // start transaction if it was enabled
            if (
                this.expressionMap.useTransaction === true &&
                queryRunner.isTransactionActive === false
            ) {
                await queryRunner.startTransaction()
                transactionStartedByUs = true
            }

            this.expressionMap.queryEntity = false
            const results = await this.executeCountQuery(queryRunner)

            // close transaction if we started it
            if (transactionStartedByUs) {
                await queryRunner.commitTransaction()
            }

            return results
        } catch (error) {
            // rollback transaction if we started it
            if (transactionStartedByUs) {
                try {
                    await queryRunner.rollbackTransaction()
                } catch (rollbackError) {}
            }
            throw error
        } finally {
            if (queryRunner !== this.queryRunner)
                // means we created our own query runner
                await queryRunner.release()
        }
    }

    /**
     * Executes built SQL query and returns entities and overall entities count (without limitation).
     * This method is useful to build pagination.
     */
    async getManyAndCount(): Promise<[Entity[], number]> {
        if (this.expressionMap.lockMode === "optimistic")
            throw new OptimisticLockCanNotBeUsedError()

        const queryRunner = this.obtainQueryRunner()
        let transactionStartedByUs: boolean = false
        try {
            // start transaction if it was enabled
            if (
                this.expressionMap.useTransaction === true &&
                queryRunner.isTransactionActive === false
            ) {
                await queryRunner.startTransaction()
                transactionStartedByUs = true
            }

            this.expressionMap.queryEntity = true
            const entitiesAndRaw = await this.executeEntitiesAndRawResults(
                queryRunner,
            )
            this.expressionMap.queryEntity = false
            const cacheId = this.expressionMap.cacheId
            // Creates a new cacheId for the count query, or it will retreive the above query results
            // and count will return 0.
            this.expressionMap.cacheId = cacheId ? `${cacheId}-count` : cacheId
            const count = await this.executeCountQuery(queryRunner)
            const results: [Entity[], number] = [entitiesAndRaw.entities, count]

            // close transaction if we started it
            if (transactionStartedByUs) {
                await queryRunner.commitTransaction()
            }

            return results
        } catch (error) {
            // rollback transaction if we started it
            if (transactionStartedByUs) {
                try {
                    await queryRunner.rollbackTransaction()
                } catch (rollbackError) {}
            }
            throw error
        } finally {
            if (queryRunner !== this.queryRunner)
                // means we created our own query runner
                await queryRunner.release()
        }
    }

    /**
     * Executes built SQL query and returns raw data stream.
     */
    async stream(): Promise<ReadStream> {
        this.expressionMap.queryEntity = false
        const [sql, parameters] = this.getQueryAndParameters()
        const queryRunner = this.obtainQueryRunner()
        let transactionStartedByUs: boolean = false
        try {
            // start transaction if it was enabled
            if (
                this.expressionMap.useTransaction === true &&
                queryRunner.isTransactionActive === false
            ) {
                await queryRunner.startTransaction()
                transactionStartedByUs = true
            }

            const releaseFn = () => {
                if (queryRunner !== this.queryRunner)
                    // means we created our own query runner
                    return queryRunner.release()
                return
            }
            const results = queryRunner.stream(
                sql,
                parameters,
                releaseFn,
                releaseFn,
            )

            // close transaction if we started it
            if (transactionStartedByUs) {
                await queryRunner.commitTransaction()
            }

            return results
        } catch (error) {
            // rollback transaction if we started it
            if (transactionStartedByUs) {
                try {
                    await queryRunner.rollbackTransaction()
                } catch (rollbackError) {}
            }
            throw error
        }
    }

    /**
     * Enables or disables query result caching.
     */
    cache(enabled: boolean): this

    /**
     * Enables query result caching and sets in milliseconds in which cache will expire.
     * If not set then global caching time will be used.
     */
    cache(milliseconds: number): this

    /**
     * Enables query result caching and sets cache id and milliseconds in which cache will expire.
     */
    cache(id: any, milliseconds?: number): this

    /**
     * Enables or disables query result caching.
     */
    cache(
        enabledOrMillisecondsOrId: boolean | number | string,
        maybeMilliseconds?: number,
    ): this {
        if (typeof enabledOrMillisecondsOrId === "boolean") {
            this.expressionMap.cache = enabledOrMillisecondsOrId
        } else if (typeof enabledOrMillisecondsOrId === "number") {
            this.expressionMap.cache = true
            this.expressionMap.cacheDuration = enabledOrMillisecondsOrId
        } else if (
            typeof enabledOrMillisecondsOrId === "string" ||
            typeof enabledOrMillisecondsOrId === "number"
        ) {
            this.expressionMap.cache = true
            this.expressionMap.cacheId = enabledOrMillisecondsOrId
        }

        if (maybeMilliseconds) {
            this.expressionMap.cacheDuration = maybeMilliseconds
        }

        return this
    }

    /**
     * Sets extra options that can be used to configure how query builder works.
     */
    setOption(option: SelectQueryBuilderOption): this {
        this.expressionMap.options.push(option)
        return this
    }

    // -------------------------------------------------------------------------
    // Protected Methods
    // -------------------------------------------------------------------------

    protected join(
        direction: "INNER" | "LEFT",
        entityOrProperty:
            | Function
            | string
            | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>),
        aliasName: string,
        condition?: string,
        parameters?: ObjectLiteral,
        mapToProperty?: string,
        isMappingMany?: boolean,
    ): void {
        this.setParameters(parameters || {})

        const joinAttribute = new JoinAttribute(
            this.connection,
            this.expressionMap,
        )
        joinAttribute.direction = direction
        joinAttribute.mapToProperty = mapToProperty
        joinAttribute.isMappingMany = isMappingMany
        joinAttribute.entityOrProperty = entityOrProperty // relationName
        joinAttribute.condition = condition ? condition : undefined // joinInverseSideCondition
        // joinAttribute.junctionAlias = joinAttribute.relation.isOwning ? parentAlias + "_" + destinationTableAlias : destinationTableAlias + "_" + parentAlias;
        this.expressionMap.joinAttributes.push(joinAttribute)

        if (joinAttribute.metadata) {
            if (
                joinAttribute.metadata.deleteDateColumn &&
                !this.expressionMap.withDeleted
            ) {
                const conditionDeleteColumn = `${aliasName}.${joinAttribute.metadata.deleteDateColumn.propertyName} IS NULL`
                joinAttribute.condition = joinAttribute.condition
                    ? ` ${joinAttribute.condition} AND ${conditionDeleteColumn}`
                    : `${conditionDeleteColumn}`
            }
            // todo: find and set metadata right there?
            joinAttribute.alias = this.expressionMap.createAlias({
                type: "join",
                name: aliasName,
                metadata: joinAttribute.metadata,
            })
            if (
                joinAttribute.relation &&
                joinAttribute.relation.junctionEntityMetadata
            ) {
                this.expressionMap.createAlias({
                    type: "join",
                    name: joinAttribute.junctionAlias,
                    metadata: joinAttribute.relation.junctionEntityMetadata,
                })
            }
        } else {
            let subQuery: string = ""
            if (typeof entityOrProperty === "function") {
                const subQueryBuilder: SelectQueryBuilder<any> = (
                    entityOrProperty as any
                )((this as any as SelectQueryBuilder<any>).subQuery())
                this.setParameters(subQueryBuilder.getParameters())
                subQuery = subQueryBuilder.getQuery()
            } else {
                subQuery = entityOrProperty
            }
            const isSubQuery =
                typeof entityOrProperty === "function" ||
                (entityOrProperty.substr(0, 1) === "(" &&
                    entityOrProperty.substr(-1) === ")")
            joinAttribute.alias = this.expressionMap.createAlias({
                type: "join",
                name: aliasName,
                tablePath:
                    isSubQuery === false
                        ? (entityOrProperty as string)
                        : undefined,
                subQuery: isSubQuery === true ? subQuery : undefined,
            })
        }
    }

    /**
     * Creates "SELECT FROM" part of SQL query.
     */
    protected createSelectExpression() {
        if (!this.expressionMap.mainAlias)
            throw new TypeORMError(
                "Cannot build query because main alias is not set (call qb#from method)",
            )

        // todo throw exception if selects or from is missing

        const allSelects: SelectQuery[] = []
        const excludedSelects: SelectQuery[] = []

        if (this.expressionMap.mainAlias.hasMetadata) {
            const metadata = this.expressionMap.mainAlias.metadata
            allSelects.push(
                ...this.buildEscapedEntityColumnSelects(
                    this.expressionMap.mainAlias.name,
                    metadata,
                ),
            )
            excludedSelects.push(
                ...this.findEntityColumnSelects(
                    this.expressionMap.mainAlias.name,
                    metadata,
                ),
            )
        }

        // add selects from joins
        this.expressionMap.joinAttributes.forEach((join) => {
            if (join.metadata) {
                allSelects.push(
                    ...this.buildEscapedEntityColumnSelects(
                        join.alias.name!,
                        join.metadata,
                    ),
                )
                excludedSelects.push(
                    ...this.findEntityColumnSelects(
                        join.alias.name!,
                        join.metadata,
                    ),
                )
            } else {
                const hasMainAlias = this.expressionMap.selects.some(
                    (select) => select.selection === join.alias.name,
                )
                if (hasMainAlias) {
                    allSelects.push({
                        selection: this.escape(join.alias.name!) + ".*",
                    })
                    const excludedSelect = this.expressionMap.selects.find(
                        (select) => select.selection === join.alias.name,
                    )
                    excludedSelects.push(excludedSelect!)
                }
            }
        })

        // add all other selects
        this.expressionMap.selects
            .filter((select) => excludedSelects.indexOf(select) === -1)
            .forEach((select) =>
                allSelects.push({
                    selection: this.replacePropertyNames(select.selection),
                    aliasName: select.aliasName,
                }),
            )

        // if still selection is empty, then simply set it to all (*)
        if (allSelects.length === 0) allSelects.push({ selection: "*" })

        // Use certain index
        let useIndex: string = ""
        if (this.expressionMap.useIndex) {
            if (DriverUtils.isMySQLFamily(this.connection.driver)) {
                useIndex = ` USE INDEX (${this.expressionMap.useIndex})`
            }
        }

        // create a selection query
        const froms = this.expressionMap.aliases
            .filter(
                (alias) =>
                    alias.type === "from" &&
                    (alias.tablePath || alias.subQuery),
            )
            .map((alias) => {
                if (alias.subQuery)
                    return alias.subQuery + " " + this.escape(alias.name)

                return (
                    this.getTableName(alias.tablePath!) +
                    " " +
                    this.escape(alias.name)
                )
            })

        const select = this.createSelectDistinctExpression()
        const selection = allSelects
            .map(
                (select) =>
                    select.selection +
                    (select.aliasName
                        ? " AS " + this.escape(select.aliasName)
                        : ""),
            )
            .join(", ")

        return (
            select +
            selection +
            " FROM " +
            froms.join(", ") +
            this.createTableLockExpression() +
            useIndex
        )
    }

    /**
     * Creates select | select distinct part of SQL query.
     */
    protected createSelectDistinctExpression(): string {
        const { selectDistinct, selectDistinctOn, maxExecutionTime } =
            this.expressionMap
        const { driver } = this.connection

        let select = "SELECT "

        if (maxExecutionTime > 0) {
            if (DriverUtils.isMySQLFamily(driver)) {
                select += `/*+ MAX_EXECUTION_TIME(${this.expressionMap.maxExecutionTime}) */ `
            }
        }

        if (driver.options.type === "postgres" && selectDistinctOn.length > 0) {
            const selectDistinctOnMap = selectDistinctOn
                .map((on) => this.replacePropertyNames(on))
                .join(", ")

            select = `SELECT DISTINCT ON (${selectDistinctOnMap}) `
        } else if (selectDistinct) {
            select = "SELECT DISTINCT "
        }

        return select
    }

    /**
     * Creates "JOIN" part of SQL query.
     */
    protected createJoinExpression(): string {
        // examples:
        // select from owning side
        // qb.select("post")
        //     .leftJoinAndSelect("post.category", "category");
        // select from non-owning side
        // qb.select("category")
        //     .leftJoinAndSelect("category.post", "post");

        const joins = this.expressionMap.joinAttributes.map((joinAttr) => {
            const relation = joinAttr.relation
            const destinationTableName = joinAttr.tablePath
            const destinationTableAlias = joinAttr.alias.name
            let appendedCondition = joinAttr.condition
                ? " AND (" + joinAttr.condition + ")"
                : ""
            const parentAlias = joinAttr.parentAlias

            // if join was build without relation (e.g. without "post.category") then it means that we have direct
            // table to join, without junction table involved. This means we simply join direct table.
            if (!parentAlias || !relation) {
                const destinationJoin = joinAttr.alias.subQuery
                    ? joinAttr.alias.subQuery
                    : this.getTableName(destinationTableName)
                return (
                    " " +
                    joinAttr.direction +
                    " JOIN " +
                    destinationJoin +
                    " " +
                    this.escape(destinationTableAlias) +
                    this.createTableLockExpression() +
                    (joinAttr.condition
                        ? " ON " + this.replacePropertyNames(joinAttr.condition)
                        : "")
                )
            }

            // if real entity relation is involved
            if (relation.isManyToOne || relation.isOneToOneOwner) {
                // JOIN `category` `category` ON `category`.`id` = `post`.`categoryId`
                const condition = relation.joinColumns
                    .map((joinColumn) => {
                        return (
                            destinationTableAlias +
                            "." +
                            joinColumn.referencedColumn!.propertyPath +
                            "=" +
                            parentAlias +
                            "." +
                            relation.propertyPath +
                            "." +
                            joinColumn.referencedColumn!.propertyPath
                        )
                    })
                    .join(" AND ")

                return (
                    " " +
                    joinAttr.direction +
                    " JOIN " +
                    this.getTableName(destinationTableName) +
                    " " +
                    this.escape(destinationTableAlias) +
                    this.createTableLockExpression() +
                    " ON " +
                    this.replacePropertyNames(condition + appendedCondition)
                )
            } else if (relation.isOneToMany || relation.isOneToOneNotOwner) {
                // JOIN `post` `post` ON `post`.`categoryId` = `category`.`id`
                const condition = relation
                    .inverseRelation!.joinColumns.map((joinColumn) => {
                        if (
                            relation.inverseEntityMetadata.tableType ===
                                "entity-child" &&
                            relation.inverseEntityMetadata.discriminatorColumn
                        ) {
                            appendedCondition +=
                                " AND " +
                                destinationTableAlias +
                                "." +
                                relation.inverseEntityMetadata
                                    .discriminatorColumn.databaseName +
                                "='" +
                                relation.inverseEntityMetadata
                                    .discriminatorValue +
                                "'"
                        }

                        return (
                            destinationTableAlias +
                            "." +
                            relation.inverseRelation!.propertyPath +
                            "." +
                            joinColumn.referencedColumn!.propertyPath +
                            "=" +
                            parentAlias +
                            "." +
                            joinColumn.referencedColumn!.propertyPath
                        )
                    })
                    .join(" AND ")

                return (
                    " " +
                    joinAttr.direction +
                    " JOIN " +
                    this.getTableName(destinationTableName) +
                    " " +
                    this.escape(destinationTableAlias) +
                    this.createTableLockExpression() +
                    " ON " +
                    this.replacePropertyNames(condition + appendedCondition)
                )
            } else {
                // means many-to-many
                const junctionTableName =
                    relation.junctionEntityMetadata!.tablePath

                const junctionAlias = joinAttr.junctionAlias
                let junctionCondition = "",
                    destinationCondition = ""

                if (relation.isOwning) {
                    junctionCondition = relation.joinColumns
                        .map((joinColumn) => {
                            // `post_category`.`postId` = `post`.`id`
                            return (
                                junctionAlias +
                                "." +
                                joinColumn.propertyPath +
                                "=" +
                                parentAlias +
                                "." +
                                joinColumn.referencedColumn!.propertyPath
                            )
                        })
                        .join(" AND ")

                    destinationCondition = relation.inverseJoinColumns
                        .map((joinColumn) => {
                            // `category`.`id` = `post_category`.`categoryId`
                            return (
                                destinationTableAlias +
                                "." +
                                joinColumn.referencedColumn!.propertyPath +
                                "=" +
                                junctionAlias +
                                "." +
                                joinColumn.propertyPath
                            )
                        })
                        .join(" AND ")
                } else {
                    junctionCondition = relation
                        .inverseRelation!.inverseJoinColumns.map(
                            (joinColumn) => {
                                // `post_category`.`categoryId` = `category`.`id`
                                return (
                                    junctionAlias +
                                    "." +
                                    joinColumn.propertyPath +
                                    "=" +
                                    parentAlias +
                                    "." +
                                    joinColumn.referencedColumn!.propertyPath
                                )
                            },
                        )
                        .join(" AND ")

                    destinationCondition = relation
                        .inverseRelation!.joinColumns.map((joinColumn) => {
                            // `post`.`id` = `post_category`.`postId`
                            return (
                                destinationTableAlias +
                                "." +
                                joinColumn.referencedColumn!.propertyPath +
                                "=" +
                                junctionAlias +
                                "." +
                                joinColumn.propertyPath
                            )
                        })
                        .join(" AND ")
                }

                return (
                    " " +
                    joinAttr.direction +
                    " JOIN " +
                    this.getTableName(junctionTableName) +
                    " " +
                    this.escape(junctionAlias) +
                    this.createTableLockExpression() +
                    " ON " +
                    this.replacePropertyNames(junctionCondition) +
                    " " +
                    joinAttr.direction +
                    " JOIN " +
                    this.getTableName(destinationTableName) +
                    " " +
                    this.escape(destinationTableAlias) +
                    this.createTableLockExpression() +
                    " ON " +
                    this.replacePropertyNames(
                        destinationCondition + appendedCondition,
                    )
                )
            }
        })

        return joins.join(" ")
    }

    /**
     * Creates "GROUP BY" part of SQL query.
     */
    protected createGroupByExpression() {
        if (!this.expressionMap.groupBys || !this.expressionMap.groupBys.length)
            return ""
        return (
            " GROUP BY " +
            this.replacePropertyNames(this.expressionMap.groupBys.join(", "))
        )
    }

    /**
     * Creates "ORDER BY" part of SQL query.
     */
    protected createOrderByExpression() {
        const orderBys = this.expressionMap.allOrderBys
        if (Object.keys(orderBys).length > 0)
            return (
                " ORDER BY " +
                Object.keys(orderBys)
                    .map((columnName) => {
                        if (typeof orderBys[columnName] === "string") {
                            return (
                                this.replacePropertyNames(columnName) +
                                " " +
                                orderBys[columnName]
                            )
                        } else {
                            return (
                                this.replacePropertyNames(columnName) +
                                " " +
                                (orderBys[columnName] as any).order +
                                " " +
                                (orderBys[columnName] as any).nulls
                            )
                        }
                    })
                    .join(", ")
            )

        return ""
    }

    /**
     * Creates "LIMIT" and "OFFSET" parts of SQL query.
     */
    protected createLimitOffsetExpression(): string {
        // in the case if nothing is joined in the query builder we don't need to make two requests to get paginated results
        // we can use regular limit / offset, that's why we add offset and limit construction here based on skip and take values
        let offset: number | undefined = this.expressionMap.offset,
            limit: number | undefined = this.expressionMap.limit
        if (
            !offset &&
            !limit &&
            this.expressionMap.joinAttributes.length === 0
        ) {
            offset = this.expressionMap.skip
            limit = this.expressionMap.take
        }

        if (this.connection.driver.options.type === "mssql") {
            // Due to a limitation in SQL Server's parser implementation it does not support using
            // OFFSET or FETCH NEXT without an ORDER BY clause being provided. In cases where the
            // user does not request one we insert a dummy ORDER BY that does nothing and should
            // have no effect on the query planner or on the order of the results returned.
            // https://dba.stackexchange.com/a/193799
            let prefix = ""
            if (
                (limit || offset) &&
                Object.keys(this.expressionMap.allOrderBys).length <= 0
            ) {
                prefix = " ORDER BY (SELECT NULL)"
            }

            if (limit && offset)
                return (
                    prefix +
                    " OFFSET " +
                    offset +
                    " ROWS FETCH NEXT " +
                    limit +
                    " ROWS ONLY"
                )
            if (limit)
                return (
                    prefix + " OFFSET 0 ROWS FETCH NEXT " + limit + " ROWS ONLY"
                )
            if (offset) return prefix + " OFFSET " + offset + " ROWS"
        } else if (
            DriverUtils.isMySQLFamily(this.connection.driver) ||
            this.connection.driver.options.type === "aurora-mysql" ||
            this.connection.driver.options.type === "sap"
        ) {
            if (limit && offset) return " LIMIT " + limit + " OFFSET " + offset
            if (limit) return " LIMIT " + limit
            if (offset) throw new OffsetWithoutLimitNotSupportedError()
        } else if (DriverUtils.isSQLiteFamily(this.connection.driver)) {
            if (limit && offset) return " LIMIT " + limit + " OFFSET " + offset
            if (limit) return " LIMIT " + limit
            if (offset) return " LIMIT -1 OFFSET " + offset
        } else if (this.connection.driver.options.type === "oracle") {
            if (limit && offset)
                return (
                    " OFFSET " +
                    offset +
                    " ROWS FETCH NEXT " +
                    limit +
                    " ROWS ONLY"
                )
            if (limit) return " FETCH NEXT " + limit + " ROWS ONLY"
            if (offset) return " OFFSET " + offset + " ROWS"
        } else {
            if (limit && offset) return " LIMIT " + limit + " OFFSET " + offset
            if (limit) return " LIMIT " + limit
            if (offset) return " OFFSET " + offset
        }

        return ""
    }

    /**
     * Creates "LOCK" part of SELECT Query after table Clause
     * ex.
     *  SELECT 1
     *  FROM USER U WITH (NOLOCK)
     *  JOIN ORDER O WITH (NOLOCK)
     *      ON U.ID=O.OrderID
     */
    private createTableLockExpression(): string {
        if (this.connection.driver.options.type === "mssql") {
            switch (this.expressionMap.lockMode) {
                case "pessimistic_read":
                    return " WITH (HOLDLOCK, ROWLOCK)"
                case "pessimistic_write":
                    return " WITH (UPDLOCK, ROWLOCK)"
                case "dirty_read":
                    return " WITH (NOLOCK)"
            }
        }

        return ""
    }

    /**
     * Creates "LOCK" part of SQL query.
     */
    protected createLockExpression(): string {
        const driver = this.connection.driver

        let lockTablesClause = ""

        if (this.expressionMap.lockTables) {
            if (
                !(
                    driver.options.type === "postgres" ||
                    driver.options.type === "cockroachdb"
                )
            ) {
                throw new TypeORMError(
                    "Lock tables not supported in selected driver",
                )
            }
            if (this.expressionMap.lockTables.length < 1) {
                throw new TypeORMError("lockTables cannot be an empty array")
            }
            lockTablesClause = " OF " + this.expressionMap.lockTables.join(", ")
        }

        switch (this.expressionMap.lockMode) {
            case "pessimistic_read":
                if (
                    DriverUtils.isMySQLFamily(driver) ||
                    driver.options.type === "aurora-mysql"
                ) {
                    return " LOCK IN SHARE MODE"
                } else if (driver.options.type === "postgres") {
                    return " FOR SHARE" + lockTablesClause
                } else if (driver.options.type === "oracle") {
                    return " FOR UPDATE"
                } else if (driver.options.type === "mssql") {
                    return ""
                } else {
                    throw new LockNotSupportedOnGivenDriverError()
                }
            case "pessimistic_write":
                if (
                    DriverUtils.isMySQLFamily(driver) ||
                    driver.options.type === "aurora-mysql" ||
                    driver.options.type === "oracle"
                ) {
                    return " FOR UPDATE"
                } else if (
                    driver.options.type === "postgres" ||
                    driver.options.type === "cockroachdb"
                ) {
                    return " FOR UPDATE" + lockTablesClause
                } else if (driver.options.type === "mssql") {
                    return ""
                } else {
                    throw new LockNotSupportedOnGivenDriverError()
                }
            case "pessimistic_partial_write":
                if (driver.options.type === "postgres") {
                    return " FOR UPDATE" + lockTablesClause + " SKIP LOCKED"
                } else if (DriverUtils.isMySQLFamily(driver)) {
                    return " FOR UPDATE SKIP LOCKED"
                } else {
                    throw new LockNotSupportedOnGivenDriverError()
                }
            case "pessimistic_write_or_fail":
                if (
                    driver.options.type === "postgres" ||
                    driver.options.type === "cockroachdb"
                ) {
                    return " FOR UPDATE" + lockTablesClause + " NOWAIT"
                } else if (DriverUtils.isMySQLFamily(driver)) {
                    return " FOR UPDATE NOWAIT"
                } else {
                    throw new LockNotSupportedOnGivenDriverError()
                }

            case "for_no_key_update":
                if (
                    driver.options.type === "postgres" ||
                    driver.options.type === "cockroachdb"
                ) {
                    return " FOR NO KEY UPDATE" + lockTablesClause
                } else {
                    throw new LockNotSupportedOnGivenDriverError()
                }
            default:
                return ""
        }
    }

    /**
     * Creates "HAVING" part of SQL query.
     */
    protected createHavingExpression() {
        if (!this.expressionMap.havings || !this.expressionMap.havings.length)
            return ""
        const conditions = this.expressionMap.havings
            .map((having, index) => {
                switch (having.type) {
                    case "and":
                        return (
                            (index > 0 ? "AND " : "") +
                            this.replacePropertyNames(having.condition)
                        )
                    case "or":
                        return (
                            (index > 0 ? "OR " : "") +
                            this.replacePropertyNames(having.condition)
                        )
                    default:
                        return this.replacePropertyNames(having.condition)
                }
            })
            .join(" ")

        if (!conditions.length) return ""
        return " HAVING " + conditions
    }

    protected buildEscapedEntityColumnSelects(
        aliasName: string,
        metadata: EntityMetadata,
    ): SelectQuery[] {
        const hasMainAlias = this.expressionMap.selects.some(
            (select) => select.selection === aliasName,
        )

        const columns: ColumnMetadata[] = []
        if (hasMainAlias) {
            columns.push(
                ...metadata.columns.filter(
                    (column) => column.isSelect === true,
                ),
            )
        }
        columns.push(
            ...metadata.columns.filter((column) => {
                return this.expressionMap.selects.some(
                    (select) =>
                        select.selection ===
                        aliasName + "." + column.propertyPath,
                )
            }),
        )

        // if user used partial selection and did not select some primary columns which are required to be selected
        // we select those primary columns and mark them as "virtual". Later virtual column values will be removed from final entity
        // to make entity contain exactly what user selected
        if (columns.length === 0)
            // however not in the case when nothing (even partial) was selected from this target (for example joins without selection)
            return []

        const nonSelectedPrimaryColumns = this.expressionMap.queryEntity
            ? metadata.primaryColumns.filter(
                  (primaryColumn) => columns.indexOf(primaryColumn) === -1,
              )
            : []
        const allColumns = [...columns, ...nonSelectedPrimaryColumns]

        const finalSelects: SelectQuery[] = []
        allColumns.forEach((column) => {
            let selectionPath =
                this.escape(aliasName) + "." + this.escape(column.databaseName)
            if (
                this.connection.driver.spatialTypes.indexOf(column.type) !== -1
            ) {
                if (
                    DriverUtils.isMySQLFamily(this.connection.driver) ||
                    this.connection.driver.options.type === "aurora-mysql"
                ) {
                    const useLegacy = (
                        this.connection.driver as
                            | MysqlDriver
                            | AuroraMysqlDriver
                    ).options.legacySpatialSupport
                    const asText = useLegacy ? "AsText" : "ST_AsText"
                    selectionPath = `${asText}(${selectionPath})`
                }

                if (this.connection.driver.options.type === "postgres")
                    if (column.precision) {
                        // cast to JSON to trigger parsing in the driver
                        selectionPath = `ST_AsGeoJSON(${selectionPath}, ${column.precision})::json`
                    } else {
                        selectionPath = `ST_AsGeoJSON(${selectionPath})::json`
                    }
                if (this.connection.driver.options.type === "mssql")
                    selectionPath = `${selectionPath}.ToString()`
            }

            const selections = this.expressionMap.selects.filter(
                (select) =>
                    select.selection === aliasName + "." + column.propertyPath,
            )
            if (selections.length) {
                selections.forEach((selection) => {
                    finalSelects.push({
                        selection: selectionPath,
                        aliasName: selection.aliasName
                            ? selection.aliasName
                            : DriverUtils.buildAlias(
                                  this.connection.driver,
                                  aliasName,
                                  column.databaseName,
                              ),
                        // todo: need to keep in mind that custom selection.aliasName breaks hydrator. fix it later!
                        virtual: selection.virtual,
                    })
                })
            } else {
                finalSelects.push({
                    selection: selectionPath,
                    aliasName: DriverUtils.buildAlias(
                        this.connection.driver,
                        aliasName,
                        column.databaseName,
                    ),
                    // todo: need to keep in mind that custom selection.aliasName breaks hydrator. fix it later!
                    virtual: hasMainAlias,
                })
            }
        })
        return finalSelects
    }

    protected findEntityColumnSelects(
        aliasName: string,
        metadata: EntityMetadata,
    ): SelectQuery[] {
        const mainSelect = this.expressionMap.selects.find(
            (select) => select.selection === aliasName,
        )
        if (mainSelect) return [mainSelect]

        return this.expressionMap.selects.filter((select) => {
            return metadata.columns.some(
                (column) =>
                    select.selection === aliasName + "." + column.propertyPath,
            )
        })
    }

    private computeCountExpression() {
        const mainAlias = this.expressionMap.mainAlias!.name // todo: will this work with "fromTableName"?
        const metadata = this.expressionMap.mainAlias!.metadata

        const primaryColumns = metadata.primaryColumns
        const distinctAlias = this.escape(mainAlias)

        // If we aren't doing anything that will create a join, we can use a simpler `COUNT` instead
        // so we prevent poor query patterns in the most likely cases
        if (
            this.expressionMap.joinAttributes.length === 0 &&
            this.expressionMap.relationIdAttributes.length === 0 &&
            this.expressionMap.relationCountAttributes.length === 0
        ) {
            return "COUNT(1)"
        }

        // For everything else, we'll need to do some hackery to get the correct count values.

        if (
            this.connection.driver.options.type === "cockroachdb" ||
            this.connection.driver.options.type === "postgres"
        ) {
            // Postgres and CockroachDB can pass multiple parameters to the `DISTINCT` function
            // https://www.postgresql.org/docs/9.5/sql-select.html#SQL-DISTINCT
            return (
                "COUNT(DISTINCT(" +
                primaryColumns
                    .map(
                        (c) =>
                            `${distinctAlias}.${this.escape(c.databaseName)}`,
                    )
                    .join(", ") +
                "))"
            )
        }

        if (DriverUtils.isMySQLFamily(this.connection.driver)) {
            // MySQL & MariaDB can pass multiple parameters to the `DISTINCT` language construct
            // https://mariadb.com/kb/en/count-distinct/
            return (
                "COUNT(DISTINCT " +
                primaryColumns
                    .map(
                        (c) =>
                            `${distinctAlias}.${this.escape(c.databaseName)}`,
                    )
                    .join(", ") +
                ")"
            )
        }

        if (this.connection.driver.options.type === "mssql") {
            // SQL Server has gotta be different from everyone else.  They don't support
            // distinct counting multiple columns & they don't have the same operator
            // characteristic for concatenating, so we gotta use the `CONCAT` function.
            // However, If it's exactly 1 column we can omit the `CONCAT` for better performance.

            const columnsExpression = primaryColumns
                .map(
                    (primaryColumn) =>
                        `${distinctAlias}.${this.escape(
                            primaryColumn.databaseName,
                        )}`,
                )
                .join(", '|;|', ")

            if (primaryColumns.length === 1) {
                return `COUNT(DISTINCT(${columnsExpression}))`
            }

            return `COUNT(DISTINCT(CONCAT(${columnsExpression})))`
        }

        // If all else fails, fall back to a `COUNT` and `DISTINCT` across all the primary columns concatenated.
        // Per the SQL spec, this is the canonical string concatenation mechanism which is most
        // likely to work across servers implementing the SQL standard.

        // Please note, if there is only one primary column that the concatenation does not occur in this
        // query and the query is a standard `COUNT DISTINCT` in that case.

        return (
            `COUNT(DISTINCT(` +
            primaryColumns
                .map((c) => `${distinctAlias}.${this.escape(c.databaseName)}`)
                .join(" || '|;|' || ") +
            "))"
        )
    }

    protected async executeCountQuery(
        queryRunner: QueryRunner,
    ): Promise<number> {
        const countSql = this.computeCountExpression()

        const results = await this.clone()
            .orderBy()
            .groupBy()
            .offset(undefined)
            .limit(undefined)
            .skip(undefined)
            .take(undefined)
            .select(countSql, "cnt")
            .setOption("disable-global-order")
            .loadRawResults(queryRunner)

        if (!results || !results[0] || !results[0]["cnt"]) return 0

        return parseInt(results[0]["cnt"])
    }

    protected applyFindOptions() {
        // todo: convert relations: string[] to object map to simplify code
        // todo: same with selects

        if (this.expressionMap.mainAlias!.metadata) {
            if (this.findOptions.relationLoadStrategy) {
                this.expressionMap.relationLoadStrategy =
                    this.findOptions.relationLoadStrategy
            }

            if (this.findOptions.comment) {
                this.comment(this.findOptions.comment)
            }

            if (this.findOptions.withDeleted) {
                this.withDeleted()
            }

            if (this.findOptions.select) {
                const select = Array.isArray(this.findOptions.select)
                    ? OrmUtils.propertyPathsToTruthyObject(
                          this.findOptions.select as string[],
                      )
                    : this.findOptions.select

                this.buildSelect(
                    select,
                    this.expressionMap.mainAlias!.metadata,
                    this.expressionMap.mainAlias!.name,
                )
            }

            if (this.selects.length) {
                this.select(this.selects)
            }

            this.selects = []
            if (this.findOptions.relations) {
                const relations = Array.isArray(this.findOptions.relations)
                    ? OrmUtils.propertyPathsToTruthyObject(
                          this.findOptions.relations,
                      )
                    : this.findOptions.relations

                this.buildRelations(
                    relations,
                    typeof this.findOptions.select === "object"
                        ? (this.findOptions.select as FindOptionsSelect<any>)
                        : undefined,
                    this.expressionMap.mainAlias!.metadata,
                    this.expressionMap.mainAlias!.name,
                )
                if (
                    this.findOptions.loadEagerRelations !== false &&
                    this.expressionMap.relationLoadStrategy === "join"
                ) {
                    this.buildEagerRelations(
                        relations,
                        typeof this.findOptions.select === "object"
                            ? (this.findOptions
                                  .select as FindOptionsSelect<any>)
                            : undefined,
                        this.expressionMap.mainAlias!.metadata,
                        this.expressionMap.mainAlias!.name,
                    )
                }
            }
            if (this.selects.length) {
                this.addSelect(this.selects)
            }

            if (this.findOptions.where) {
                this.conditions = this.buildWhere(
                    this.findOptions.where,
                    this.expressionMap.mainAlias!.metadata,
                    this.expressionMap.mainAlias!.name,
                )

                if (this.conditions.length)
                    this.andWhere(
                        this.conditions.substr(0, 1) !== "("
                            ? "(" + this.conditions + ")"
                            : this.conditions,
                    ) // temporary and where and braces
            }

            if (this.findOptions.order) {
                this.buildOrder(
                    this.findOptions.order,
                    this.expressionMap.mainAlias!.metadata,
                    this.expressionMap.mainAlias!.name,
                )
            }

            // apply joins
            if (this.joins.length) {
                this.joins.forEach((join) => {
                    if (join.select && !join.selection) {
                        // if (join.selection) {
                        //
                        // } else {
                        if (join.type === "inner") {
                            this.innerJoinAndSelect(
                                `${join.parentAlias}.${join.relationMetadata.propertyPath}`,
                                join.alias,
                            )
                        } else {
                            this.leftJoinAndSelect(
                                `${join.parentAlias}.${join.relationMetadata.propertyPath}`,
                                join.alias,
                            )
                        }
                        // }
                    } else {
                        if (join.type === "inner") {
                            this.innerJoin(
                                `${join.parentAlias}.${join.relationMetadata.propertyPath}`,
                                join.alias,
                            )
                        } else {
                            this.leftJoin(
                                `${join.parentAlias}.${join.relationMetadata.propertyPath}`,
                                join.alias,
                            )
                        }
                    }

                    // if (join.select) {
                    //     if (this.findOptions.loadEagerRelations !== false) {
                    //         FindOptionsUtils.joinEagerRelations(
                    //             this,
                    //             join.alias,
                    //             join.relationMetadata.inverseEntityMetadata
                    //         );
                    //     }
                    // }
                })
            }

            // if (this.conditions.length) {
            //     this.where(this.conditions.join(" AND "));
            // }

            // apply offset
            if (this.findOptions.skip !== undefined) {
                // if (this.findOptions.options && this.findOptions.options.pagination === false) {
                //     this.offset(this.findOptions.skip);
                // } else {
                this.skip(this.findOptions.skip)
                // }
            }

            // apply limit
            if (this.findOptions.take !== undefined) {
                // if (this.findOptions.options && this.findOptions.options.pagination === false) {
                //     this.limit(this.findOptions.take);
                // } else {
                this.take(this.findOptions.take)
                // }
            }

            // apply caching options
            if (typeof this.findOptions.cache === "number") {
                this.cache(this.findOptions.cache)
            } else if (typeof this.findOptions.cache === "boolean") {
                this.cache(this.findOptions.cache)
            } else if (typeof this.findOptions.cache === "object") {
                this.cache(
                    this.findOptions.cache.id,
                    this.findOptions.cache.milliseconds,
                )
            }

            if (this.findOptions.join) {
                if (this.findOptions.join.leftJoin)
                    Object.keys(this.findOptions.join.leftJoin).forEach(
                        (key) => {
                            this.leftJoin(
                                this.findOptions.join!.leftJoin![key],
                                key,
                            )
                        },
                    )

                if (this.findOptions.join.innerJoin)
                    Object.keys(this.findOptions.join.innerJoin).forEach(
                        (key) => {
                            this.innerJoin(
                                this.findOptions.join!.innerJoin![key],
                                key,
                            )
                        },
                    )

                if (this.findOptions.join.leftJoinAndSelect)
                    Object.keys(
                        this.findOptions.join.leftJoinAndSelect,
                    ).forEach((key) => {
                        this.leftJoinAndSelect(
                            this.findOptions.join!.leftJoinAndSelect![key],
                            key,
                        )
                    })

                if (this.findOptions.join.innerJoinAndSelect)
                    Object.keys(
                        this.findOptions.join.innerJoinAndSelect,
                    ).forEach((key) => {
                        this.innerJoinAndSelect(
                            this.findOptions.join!.innerJoinAndSelect![key],
                            key,
                        )
                    })
            }

            if (this.findOptions.lock) {
                if (this.findOptions.lock.mode === "optimistic") {
                    this.setLock(
                        this.findOptions.lock.mode,
                        this.findOptions.lock.version,
                    )
                } else if (
                    this.findOptions.lock.mode === "pessimistic_read" ||
                    this.findOptions.lock.mode === "pessimistic_write" ||
                    this.findOptions.lock.mode === "dirty_read" ||
                    this.findOptions.lock.mode ===
                        "pessimistic_partial_write" ||
                    this.findOptions.lock.mode ===
                        "pessimistic_write_or_fail" ||
                    this.findOptions.lock.mode === "for_no_key_update"
                ) {
                    const tableNames = this.findOptions.lock.tables
                        ? this.findOptions.lock.tables.map((table) => {
                              const tableAlias =
                                  this.expressionMap.aliases.find((alias) => {
                                      return (
                                          alias.metadata
                                              .tableNameWithoutPrefix === table
                                      )
                                  })
                              if (!tableAlias) {
                                  throw new TypeORMError(
                                      `"${table}" is not part of this query`,
                                  )
                              }
                              return this.escape(tableAlias.name)
                          })
                        : undefined
                    this.setLock(
                        this.findOptions.lock.mode,
                        undefined,
                        tableNames,
                    )
                }
            }

            if (this.findOptions.loadRelationIds === true) {
                this.loadAllRelationIds()
            } else if (typeof this.findOptions.loadRelationIds === "object") {
                this.loadAllRelationIds(this.findOptions.loadRelationIds as any)
            }

            if (this.findOptions.loadEagerRelations !== false) {
                FindOptionsUtils.joinEagerRelations(
                    this,
                    this.expressionMap.mainAlias!.name,
                    this.expressionMap.mainAlias!.metadata,
                )
            }

            if (this.findOptions.transaction === true) {
                this.expressionMap.useTransaction = true
            }

            // if (this.orderBys.length) {
            //     this.orderBys.forEach(orderBy => {
            //         this.addOrderBy(orderBy.alias, orderBy.direction, orderBy.nulls);
            //     });
            // }

            // todo
            // if (this.options.options && this.options.options.eagerRelations) {
            //     this.queryBuilder
            // }

            // todo
            // if (this.findOptions.options && this.findOptions.listeners === false) {
            //     this.callListeners(false);
            // }
        }
    }

    /**
     * Executes sql generated by query builder and returns object with raw results and entities created from them.
     */
    protected async executeEntitiesAndRawResults(
        queryRunner: QueryRunner,
    ): Promise<{ entities: Entity[]; raw: any[] }> {
        if (!this.expressionMap.mainAlias)
            throw new TypeORMError(
                `Alias is not set. Use "from" method to set an alias.`,
            )

        if (
            (this.expressionMap.lockMode === "pessimistic_read" ||
                this.expressionMap.lockMode === "pessimistic_write" ||
                this.expressionMap.lockMode === "pessimistic_partial_write" ||
                this.expressionMap.lockMode === "pessimistic_write_or_fail" ||
                this.expressionMap.lockMode === "for_no_key_update") &&
            !queryRunner.isTransactionActive
        )
            throw new PessimisticLockTransactionRequiredError()

        if (this.expressionMap.lockMode === "optimistic") {
            const metadata = this.expressionMap.mainAlias.metadata
            if (!metadata.versionColumn && !metadata.updateDateColumn)
                throw new NoVersionOrUpdateDateColumnError(metadata.name)
        }

        const relationIdLoader = new RelationIdLoader(
            this.connection,
            queryRunner,
            this.expressionMap.relationIdAttributes,
        )
        const relationCountLoader = new RelationCountLoader(
            this.connection,
            queryRunner,
            this.expressionMap.relationCountAttributes,
        )
        const relationIdMetadataTransformer =
            new RelationIdMetadataToAttributeTransformer(this.expressionMap)
        relationIdMetadataTransformer.transform()
        const relationCountMetadataTransformer =
            new RelationCountMetadataToAttributeTransformer(this.expressionMap)
        relationCountMetadataTransformer.transform()

        let rawResults: any[] = [],
            entities: any[] = []

        // for pagination enabled (e.g. skip and take) its much more complicated - its a special process
        // where we make two queries to find the data we need
        // first query find ids in skip and take range
        // and second query loads the actual data in given ids range
        if (
            (this.expressionMap.skip || this.expressionMap.take) &&
            this.expressionMap.joinAttributes.length > 0
        ) {
            // we are skipping order by here because its not working in subqueries anyway
            // to make order by working we need to apply it on a distinct query
            const [selects, orderBys] =
                this.createOrderByCombinedWithSelectExpression("distinctAlias")
            const metadata = this.expressionMap.mainAlias.metadata
            const mainAliasName = this.expressionMap.mainAlias.name

            const querySelects = metadata.primaryColumns.map(
                (primaryColumn) => {
                    const distinctAlias = this.escape("distinctAlias")
                    const columnAlias = this.escape(
                        DriverUtils.buildAlias(
                            this.connection.driver,
                            mainAliasName,
                            primaryColumn.databaseName,
                        ),
                    )
                    if (!orderBys[columnAlias])
                        // make sure we aren't overriding user-defined order in inverse direction
                        orderBys[columnAlias] = "ASC"

                    const alias = DriverUtils.buildAlias(
                        this.connection.driver,
                        "ids_" + mainAliasName,
                        primaryColumn.databaseName,
                    )

                    return `${distinctAlias}.${columnAlias} as "${alias}"`
                },
            )

            rawResults = await new SelectQueryBuilder(
                this.connection,
                queryRunner,
            )
                .select(`DISTINCT ${querySelects.join(", ")}`)
                .addSelect(selects)
                .from(`(${this.clone().orderBy().getQuery()})`, "distinctAlias")
                .offset(this.expressionMap.skip)
                .limit(this.expressionMap.take)
                .orderBy(orderBys)
                .cache(
                    this.expressionMap.cache
                        ? this.expressionMap.cache
                        : this.expressionMap.cacheId,
                    this.expressionMap.cacheDuration,
                )
                .setParameters(this.getParameters())
                .setNativeParameters(this.expressionMap.nativeParameters)
                .getRawMany()

            if (rawResults.length > 0) {
                let condition = ""
                const parameters: ObjectLiteral = {}
                if (metadata.hasMultiplePrimaryKeys) {
                    condition = rawResults
                        .map((result, index) => {
                            return metadata.primaryColumns
                                .map((primaryColumn) => {
                                    const paramKey = `orm_distinct_ids_${index}_${primaryColumn.databaseName}`
                                    parameters[paramKey] =
                                        result[
                                            `ids_${mainAliasName}_${primaryColumn.databaseName}`
                                        ]
                                    return `${mainAliasName}.${primaryColumn.propertyPath}=:${paramKey}`
                                })
                                .join(" AND ")
                        })
                        .join(" OR ")
                } else {
                    const alias = DriverUtils.buildAlias(
                        this.connection.driver,
                        "ids_" + mainAliasName,
                        metadata.primaryColumns[0].databaseName,
                    )

                    const ids = rawResults.map((result) => result[alias])
                    const areAllNumbers = ids.every(
                        (id: any) => typeof id === "number",
                    )
                    if (areAllNumbers) {
                        // fixes #190. if all numbers then its safe to perform query without parameter
                        condition = `${mainAliasName}.${
                            metadata.primaryColumns[0].propertyPath
                        } IN (${ids.join(", ")})`
                    } else {
                        parameters["orm_distinct_ids"] = ids
                        condition =
                            mainAliasName +
                            "." +
                            metadata.primaryColumns[0].propertyPath +
                            " IN (:...orm_distinct_ids)"
                    }
                }
                rawResults = await this.clone()
                    .mergeExpressionMap({
                        extraAppendedAndWhereCondition: condition,
                    })
                    .setParameters(parameters)
                    .loadRawResults(queryRunner)
            }
        } else {
            rawResults = await this.loadRawResults(queryRunner)
        }

        if (rawResults.length > 0) {
            // transform raw results into entities
            const rawRelationIdResults = await relationIdLoader.load(rawResults)
            const rawRelationCountResults = await relationCountLoader.load(
                rawResults,
            )
            const transformer = new RawSqlResultsToEntityTransformer(
                this.expressionMap,
                this.connection.driver,
                rawRelationIdResults,
                rawRelationCountResults,
                this.queryRunner,
            )
            entities = transformer.transform(
                rawResults,
                this.expressionMap.mainAlias!,
            )

            // broadcast all "after load" events
            if (
                this.expressionMap.callListeners === true &&
                this.expressionMap.mainAlias.hasMetadata
            ) {
                await queryRunner.broadcaster.broadcast(
                    "Load",
                    this.expressionMap.mainAlias.metadata,
                    entities,
                )
            }
        }

        if (this.expressionMap.relationLoadStrategy === "query") {
            await Promise.all(
                this.relationMetadatas.map(async (relation) => {
                    const relationTarget = relation.inverseEntityMetadata.target
                    const relationAlias =
                        relation.inverseEntityMetadata.targetName

                    const select = Array.isArray(this.findOptions.select)
                        ? OrmUtils.propertyPathsToTruthyObject(
                              this.findOptions.select as string[],
                          )
                        : this.findOptions.select
                    const relations = Array.isArray(this.findOptions.relations)
                        ? OrmUtils.propertyPathsToTruthyObject(
                              this.findOptions.relations,
                          )
                        : this.findOptions.relations

                    const queryBuilder = this.createQueryBuilder()
                        .select(relationAlias)
                        .from(relationTarget, relationAlias)
                        .setFindOptions({
                            select: select
                                ? OrmUtils.deepValue(
                                      select,
                                      relation.propertyPath,
                                  )
                                : undefined,
                            order: this.findOptions.order
                                ? OrmUtils.deepValue(
                                      this.findOptions.order,
                                      relation.propertyPath,
                                  )
                                : undefined,
                            relations: relations
                                ? OrmUtils.deepValue(
                                      relations,
                                      relation.propertyPath,
                                  )
                                : undefined,
                            withDeleted: this.findOptions.withDeleted,
                            relationLoadStrategy:
                                this.findOptions.relationLoadStrategy,
                        })
                    if (entities.length > 0) {
                        const relatedEntityGroups: any[] =
                            await this.connection.relationIdLoader.loadManyToManyRelationIdsAndGroup(
                                relation,
                                entities,
                                undefined,
                                queryBuilder,
                            )
                        entities.forEach((entity) => {
                            const relatedEntityGroup = relatedEntityGroups.find(
                                (group) => group.entity === entity,
                            )
                            if (relatedEntityGroup) {
                                const value =
                                    relatedEntityGroup.related === undefined
                                        ? null
                                        : relatedEntityGroup.related
                                relation.setEntityValue(entity, value)
                            }
                        })
                    }
                }),
            )
        }

        return {
            raw: rawResults,
            entities: entities,
        }
    }

    protected createOrderByCombinedWithSelectExpression(
        parentAlias: string,
    ): [string, OrderByCondition] {
        // if table has a default order then apply it
        const orderBys = this.expressionMap.allOrderBys
        const selectString = Object.keys(orderBys)
            .map((orderCriteria) => {
                if (orderCriteria.indexOf(".") !== -1) {
                    const criteriaParts = orderCriteria.split(".")
                    const aliasName = criteriaParts[0]
                    const propertyPath = criteriaParts.slice(1).join(".")
                    const alias = this.expressionMap.findAliasByName(aliasName)
                    const column =
                        alias.metadata.findColumnWithPropertyPath(propertyPath)
                    return (
                        this.escape(parentAlias) +
                        "." +
                        this.escape(
                            DriverUtils.buildAlias(
                                this.connection.driver,
                                aliasName,
                                column!.databaseName,
                            ),
                        )
                    )
                } else {
                    if (
                        this.expressionMap.selects.find(
                            (select) =>
                                select.selection === orderCriteria ||
                                select.aliasName === orderCriteria,
                        )
                    )
                        return this.escape(parentAlias) + "." + orderCriteria

                    return ""
                }
            })
            .join(", ")

        const orderByObject: OrderByCondition = {}
        Object.keys(orderBys).forEach((orderCriteria) => {
            if (orderCriteria.indexOf(".") !== -1) {
                const criteriaParts = orderCriteria.split(".")
                const aliasName = criteriaParts[0]
                const propertyPath = criteriaParts.slice(1).join(".")
                const alias = this.expressionMap.findAliasByName(aliasName)
                const column =
                    alias.metadata.findColumnWithPropertyPath(propertyPath)
                orderByObject[
                    this.escape(parentAlias) +
                        "." +
                        this.escape(
                            DriverUtils.buildAlias(
                                this.connection.driver,
                                aliasName,
                                column!.databaseName,
                            ),
                        )
                ] = orderBys[orderCriteria]
            } else {
                if (
                    this.expressionMap.selects.find(
                        (select) =>
                            select.selection === orderCriteria ||
                            select.aliasName === orderCriteria,
                    )
                ) {
                    orderByObject[
                        this.escape(parentAlias) + "." + orderCriteria
                    ] = orderBys[orderCriteria]
                } else {
                    orderByObject[orderCriteria] = orderBys[orderCriteria]
                }
            }
        })

        return [selectString, orderByObject]
    }

    /**
     * Loads raw results from the database.
     */
    protected async loadRawResults(queryRunner: QueryRunner) {
        const [sql, parameters] = this.getQueryAndParameters()
        const queryId = sql + " -- PARAMETERS: " + JSON.stringify(parameters)
        const cacheOptions =
            typeof this.connection.options.cache === "object"
                ? this.connection.options.cache
                : {}
        let savedQueryResultCacheOptions: QueryResultCacheOptions | undefined =
            undefined
        let cacheError = false
        if (
            this.connection.queryResultCache &&
            (this.expressionMap.cache || cacheOptions.alwaysEnabled)
        ) {
            try {
                savedQueryResultCacheOptions =
                    await this.connection.queryResultCache.getFromCache(
                        {
                            identifier: this.expressionMap.cacheId,
                            query: queryId,
                            duration:
                                this.expressionMap.cacheDuration ||
                                cacheOptions.duration ||
                                1000,
                        },
                        queryRunner,
                    )
                if (
                    savedQueryResultCacheOptions &&
                    !this.connection.queryResultCache.isExpired(
                        savedQueryResultCacheOptions,
                    )
                ) {
                    return JSON.parse(savedQueryResultCacheOptions.result)
                }
            } catch (error) {
                if (!cacheOptions.ignoreErrors) {
                    throw error
                }
                cacheError = true
            }
        }

        const results = await queryRunner.query(sql, parameters, true)

        if (
            !cacheError &&
            this.connection.queryResultCache &&
            (this.expressionMap.cache || cacheOptions.alwaysEnabled)
        ) {
            try {
                await this.connection.queryResultCache.storeInCache(
                    {
                        identifier: this.expressionMap.cacheId,
                        query: queryId,
                        time: new Date().getTime(),
                        duration:
                            this.expressionMap.cacheDuration ||
                            cacheOptions.duration ||
                            1000,
                        result: JSON.stringify(results.records),
                    },
                    savedQueryResultCacheOptions,
                    queryRunner,
                )
            } catch (error) {
                if (!cacheOptions.ignoreErrors) {
                    throw error
                }
            }
        }

        return results.records
    }

    /**
     * Merges into expression map given expression map properties.
     */
    protected mergeExpressionMap(
        expressionMap: Partial<QueryExpressionMap>,
    ): this {
        ObjectUtils.assign(this.expressionMap, expressionMap)
        return this
    }

    /**
     * Normalizes a give number - converts to int if possible.
     */
    protected normalizeNumber(num: any) {
        if (typeof num === "number" || num === undefined || num === null)
            return num

        return Number(num)
    }

    /**
     * Creates a query builder used to execute sql queries inside this query builder.
     */
    protected obtainQueryRunner() {
        return this.queryRunner || this.connection.createQueryRunner("slave")
    }

    protected buildSelect(
        select: FindOptionsSelect<any>,
        metadata: EntityMetadata,
        alias: string,
        embedPrefix?: string,
    ) {
        for (let key in select) {
            if (select[key] === undefined || select[key] === false) continue

            const propertyPath = embedPrefix ? embedPrefix + "." + key : key
            const column =
                metadata.findColumnWithPropertyPathStrict(propertyPath)
            const embed = metadata.findEmbeddedWithPropertyPath(propertyPath)
            const relation = metadata.findRelationWithPropertyPath(propertyPath)

            if (!embed && !column && !relation)
                throw new EntityPropertyNotFoundError(propertyPath, metadata)

            if (column) {
                this.selects.push(alias + "." + propertyPath)
                // this.addSelect(alias + "." + propertyPath);
            } else if (embed) {
                this.buildSelect(
                    select[key] as FindOptionsSelect<any>,
                    metadata,
                    alias,
                    key,
                )

                // } else if (relation) {
                //     const joinAlias = alias + "_" + relation.propertyName;
                //     const existJoin = this.joins.find(join => join.alias === joinAlias);
                //     if (!existJoin) {
                //         this.joins.push({
                //             type: "left",
                //             select: false,
                //             alias: joinAlias,
                //             parentAlias: alias,
                //             relationMetadata: relation
                //         });
                //     }
                //     this.buildOrder(select[key] as FindOptionsOrder<any>, relation.inverseEntityMetadata, joinAlias);
            }
        }
    }

    protected buildRelations(
        relations: FindOptionsRelations<any>,
        selection: FindOptionsSelect<any> | undefined,
        metadata: EntityMetadata,
        alias: string,
        embedPrefix?: string,
    ) {
        if (!relations) return

        Object.keys(relations).forEach((relationName) => {
            const relationValue = (relations as any)[relationName]
            const propertyPath = embedPrefix
                ? embedPrefix + "." + relationName
                : relationName
            const embed = metadata.findEmbeddedWithPropertyPath(propertyPath)
            const relation = metadata.findRelationWithPropertyPath(propertyPath)
            if (!embed && !relation)
                throw new EntityPropertyNotFoundError(propertyPath, metadata)

            if (embed) {
                this.buildRelations(
                    relationValue,
                    typeof selection === "object"
                        ? OrmUtils.deepValue(selection, embed.propertyPath)
                        : undefined,
                    metadata,
                    alias,
                    propertyPath,
                )
            } else if (relation) {
                let joinAlias = alias + "_" + propertyPath.replace(".", "_")
                joinAlias = DriverUtils.buildAlias(
                    this.connection.driver,
                    { joiner: "__" },
                    alias,
                    joinAlias,
                )
                if (
                    relationValue === true ||
                    typeof relationValue === "object"
                ) {
                    if (this.expressionMap.relationLoadStrategy === "query") {
                        this.relationMetadatas.push(relation)
                    } else {
                        // join
                        this.joins.push({
                            type: "left",
                            select: true,
                            selection:
                                selection &&
                                typeof selection[relationName] === "object"
                                    ? (selection[
                                          relationName
                                      ] as FindOptionsSelect<any>)
                                    : undefined,
                            alias: joinAlias,
                            parentAlias: alias,
                            relationMetadata: relation,
                        })

                        if (
                            selection &&
                            typeof selection[relationName] === "object"
                        ) {
                            this.buildSelect(
                                selection[
                                    relationName
                                ] as FindOptionsSelect<any>,
                                relation.inverseEntityMetadata,
                                joinAlias,
                            )
                        }
                    }
                }

                if (
                    typeof relationValue === "object" &&
                    this.expressionMap.relationLoadStrategy === "join"
                ) {
                    this.buildRelations(
                        relationValue,
                        typeof selection === "object"
                            ? OrmUtils.deepValue(
                                  selection,
                                  relation.propertyPath,
                              )
                            : undefined,
                        relation.inverseEntityMetadata,
                        joinAlias,
                        undefined,
                    )
                }
            }
        })
    }

    protected buildEagerRelations(
        relations: FindOptionsRelations<any>,
        selection: FindOptionsSelect<any> | undefined,
        metadata: EntityMetadata,
        alias: string,
        embedPrefix?: string,
    ) {
        if (!relations) return

        Object.keys(relations).forEach((relationName) => {
            const relationValue = (relations as any)[relationName]
            const propertyPath = embedPrefix
                ? embedPrefix + "." + relationName
                : relationName
            const embed = metadata.findEmbeddedWithPropertyPath(propertyPath)
            const relation = metadata.findRelationWithPropertyPath(propertyPath)
            if (!embed && !relation)
                throw new EntityPropertyNotFoundError(propertyPath, metadata)

            if (embed) {
                this.buildEagerRelations(
                    relationValue,
                    typeof selection === "object"
                        ? OrmUtils.deepValue(selection, embed.propertyPath)
                        : undefined,
                    metadata,
                    alias,
                    propertyPath,
                )
            } else if (relation) {
                let joinAlias = alias + "_" + propertyPath.replace(".", "_")
                joinAlias = DriverUtils.buildAlias(
                    this.connection.driver,
                    { joiner: "__" },
                    alias,
                    joinAlias,
                )

                if (
                    relationValue === true ||
                    typeof relationValue === "object"
                ) {
                    relation.inverseEntityMetadata.eagerRelations.forEach(
                        (eagerRelation) => {
                            let eagerRelationJoinAlias =
                                joinAlias +
                                "_" +
                                eagerRelation.propertyPath.replace(".", "_")
                            eagerRelationJoinAlias = DriverUtils.buildAlias(
                                this.connection.driver,
                                { joiner: "__" },
                                joinAlias,
                                eagerRelationJoinAlias,
                            )

                            const existJoin = this.joins.find(
                                (join) => join.alias === eagerRelationJoinAlias,
                            )
                            if (!existJoin) {
                                this.joins.push({
                                    type: "left",
                                    select: true,
                                    alias: eagerRelationJoinAlias,
                                    parentAlias: joinAlias,
                                    selection: undefined,
                                    relationMetadata: eagerRelation,
                                })
                            }

                            if (
                                selection &&
                                typeof selection[relationName] === "object"
                            ) {
                                this.buildSelect(
                                    selection[
                                        relationName
                                    ] as FindOptionsSelect<any>,
                                    relation.inverseEntityMetadata,
                                    joinAlias,
                                )
                            }
                        },
                    )
                }

                if (typeof relationValue === "object") {
                    this.buildEagerRelations(
                        relationValue,
                        typeof selection === "object"
                            ? OrmUtils.deepValue(
                                  selection,
                                  relation.propertyPath,
                              )
                            : undefined,
                        relation.inverseEntityMetadata,
                        joinAlias,
                        undefined,
                    )
                }
            }
        })
    }

    protected buildOrder(
        order: FindOptionsOrder<any>,
        metadata: EntityMetadata,
        alias: string,
        embedPrefix?: string,
    ) {
        for (let key in order) {
            if (order[key] === undefined) continue

            const propertyPath = embedPrefix ? embedPrefix + "." + key : key
            const column =
                metadata.findColumnWithPropertyPathStrict(propertyPath)
            const embed = metadata.findEmbeddedWithPropertyPath(propertyPath)
            const relation = metadata.findRelationWithPropertyPath(propertyPath)

            if (!embed && !column && !relation)
                throw new EntityPropertyNotFoundError(propertyPath, metadata)

            if (column) {
                let direction =
                    typeof order[key] === "object"
                        ? (order[key] as any).direction
                        : order[key]
                direction =
                    direction === "DESC" ||
                    direction === "desc" ||
                    direction === -1
                        ? "DESC"
                        : "ASC"
                let nulls =
                    typeof order[key] === "object"
                        ? (order[key] as any).nulls
                        : undefined
                nulls =
                    nulls === "first"
                        ? "NULLS FIRST"
                        : nulls === "last"
                        ? "NULLS LAST"
                        : undefined

                this.addOrderBy(`${alias}.${propertyPath}`, direction, nulls)
                // this.orderBys.push({ alias: alias + "." + propertyPath, direction, nulls });
            } else if (embed) {
                this.buildOrder(
                    order[key] as FindOptionsOrder<any>,
                    metadata,
                    alias,
                    propertyPath,
                )
            } else if (relation) {
                let joinAlias = alias + "_" + propertyPath.replace(".", "_")
                joinAlias = DriverUtils.buildAlias(
                    this.connection.driver,
                    { joiner: "__" },
                    alias,
                    joinAlias,
                )
                // console.log("joinAlias", joinAlias, joinAlias.length, this.connection.driver.maxAliasLength)
                // todo: use expressionMap.joinAttributes, and create a new one using
                //  const joinAttribute = new JoinAttribute(this.connection, this.expressionMap);

                const existJoin = this.joins.find(
                    (join) => join.alias === joinAlias,
                )
                if (!existJoin) {
                    this.joins.push({
                        type: "left",
                        select: false,
                        alias: joinAlias,
                        parentAlias: alias,
                        selection: undefined,
                        relationMetadata: relation,
                    })
                }
                this.buildOrder(
                    order[key] as FindOptionsOrder<any>,
                    relation.inverseEntityMetadata,
                    joinAlias,
                )
            }
        }
    }

    protected buildWhere(
        where: FindOptionsWhere<any>,
        metadata: EntityMetadata,
        alias: string,
        embedPrefix?: string,
    ) {
        let condition: string = ""
        // let parameterIndex = Object.keys(this.expressionMap.nativeParameters).length;
        if (Array.isArray(where)) {
            condition =
                "(" +
                where
                    .map((whereItem) => {
                        return this.buildWhere(
                            whereItem,
                            metadata,
                            alias,
                            embedPrefix,
                        )
                    })
                    .filter((condition) => !!condition)
                    .map((condition) => "(" + condition + ")")
                    .join(" OR ") +
                ")"
        } else {
            let andConditions: string[] = []
            for (let key in where) {
                if (where[key] === undefined || where[key] === null) continue

                const propertyPath = embedPrefix ? embedPrefix + "." + key : key
                const column =
                    metadata.findColumnWithPropertyPathStrict(propertyPath)
                const embed =
                    metadata.findEmbeddedWithPropertyPath(propertyPath)
                const relation =
                    metadata.findRelationWithPropertyPath(propertyPath)

                if (!embed && !column && !relation)
                    throw new EntityPropertyNotFoundError(
                        propertyPath,
                        metadata,
                    )

                if (column) {
                    const aliasPath = `${alias}.${propertyPath}`
                    // const parameterName = alias + "_" + propertyPath.split(".").join("_") + "_" + parameterIndex;

                    // todo: we need to handle other operators as well?
                    let parameterValue = where[key]
                    if (InstanceChecker.isEqualOperator(where[key])) {
                        parameterValue = where[key].value
                    }
                    if (column.transformer) {
                        parameterValue = ApplyValueTransformers.transformTo(
                            column.transformer,
                            parameterValue,
                        )
                    }

                    // if (parameterValue === null) {
                    //     andConditions.push(`${aliasPath} IS NULL`);
                    //
                    // } else if (parameterValue instanceof FindOperator) {
                    //     // let parameters: any[] = [];
                    //     // if (parameterValue.useParameter) {
                    //     //     const realParameterValues: any[] = parameterValue.multipleParameters ? parameterValue.value : [parameterValue.value];
                    //     //     realParameterValues.forEach((realParameterValue, realParameterValueIndex) => {
                    //     //
                    //     //         // don't create parameters for number to prevent max number of variables issues as much as possible
                    //     //         if (typeof realParameterValue === "number") {
                    //     //             parameters.push(realParameterValue);
                    //     //
                    //     //         } else {
                    //     //             this.expressionMap.nativeParameters[parameterName + realParameterValueIndex] = realParameterValue;
                    //     //             parameterIndex++;
                    //     //             parameters.push(this.connection.driver.createParameter(parameterName + realParameterValueIndex, parameterIndex - 1));
                    //     //         }
                    //     //     });
                    //     // }
                    //     andConditions.push(
                    //         this.createWhereConditionExpression(this.getWherePredicateCondition(aliasPath, parameterValue))
                    //         // parameterValue.toSql(this.connection, aliasPath, parameters));
                    //     )
                    //
                    // } else {
                    //     this.expressionMap.nativeParameters[parameterName] = parameterValue;
                    //     parameterIndex++;
                    //     const parameter = this.connection.driver.createParameter(parameterName, parameterIndex - 1);
                    //     andConditions.push(`${aliasPath} = ${parameter}`);
                    // }

                    andConditions.push(
                        this.createWhereConditionExpression(
                            this.getWherePredicateCondition(
                                aliasPath,
                                parameterValue,
                            ),
                        ),
                        // parameterValue.toSql(this.connection, aliasPath, parameters));
                    )

                    // this.conditions.push(`${alias}.${propertyPath} = :${paramName}`);
                    // this.expressionMap.parameters[paramName] = where[key]; // todo: handle functions and other edge cases
                } else if (embed) {
                    const condition = this.buildWhere(
                        where[key],
                        metadata,
                        alias,
                        propertyPath,
                    )
                    if (condition) andConditions.push(condition)
                } else if (relation) {
                    // if all properties of where are undefined we don't need to join anything
                    // this can happen when user defines map with conditional queries inside
                    if (typeof where[key] === "object") {
                        const allAllUndefined = Object.keys(where[key]).every(
                            (k) => where[key][k] === undefined,
                        )
                        if (allAllUndefined) {
                            continue
                        }
                    }

                    if (InstanceChecker.isFindOperator(where[key])) {
                        if (
                            where[key].type === "moreThan" ||
                            where[key].type === "lessThan"
                        ) {
                            const sqlOperator =
                                where[key].type === "moreThan" ? ">" : "<"
                            // basically relation count functionality
                            const qb: QueryBuilder<any> = this.subQuery()
                            if (relation.isManyToManyOwner) {
                                qb.select("COUNT(*)")
                                    .from(
                                        relation.joinTableName,
                                        relation.joinTableName,
                                    )
                                    .where(
                                        relation.joinColumns
                                            .map((column) => {
                                                return `${
                                                    relation.joinTableName
                                                }.${
                                                    column.propertyName
                                                } = ${alias}.${
                                                    column.referencedColumn!
                                                        .propertyName
                                                }`
                                            })
                                            .join(" AND "),
                                    )
                            } else if (relation.isManyToManyNotOwner) {
                                qb.select("COUNT(*)")
                                    .from(
                                        relation.inverseRelation!.joinTableName,
                                        relation.inverseRelation!.joinTableName,
                                    )
                                    .where(
                                        relation
                                            .inverseRelation!.inverseJoinColumns.map(
                                                (column) => {
                                                    return `${
                                                        relation.inverseRelation!
                                                            .joinTableName
                                                    }.${
                                                        column.propertyName
                                                    } = ${alias}.${
                                                        column.referencedColumn!
                                                            .propertyName
                                                    }`
                                                },
                                            )
                                            .join(" AND "),
                                    )
                            } else if (relation.isOneToMany) {
                                qb.select("COUNT(*)")
                                    .from(
                                        relation.inverseEntityMetadata.target,
                                        relation.inverseEntityMetadata
                                            .tableName,
                                    )
                                    .where(
                                        relation
                                            .inverseRelation!.joinColumns.map(
                                                (column) => {
                                                    return `${
                                                        relation
                                                            .inverseEntityMetadata
                                                            .tableName
                                                    }.${
                                                        column.propertyName
                                                    } = ${alias}.${
                                                        column.referencedColumn!
                                                            .propertyName
                                                    }`
                                                },
                                            )
                                            .join(" AND "),
                                    )
                            } else {
                                throw new Error(
                                    `This relation isn't supported by given find operator`,
                                )
                            }
                            // this
                            //     .addSelect(qb.getSql(), relation.propertyAliasName + "_cnt")
                            //     .andWhere(this.escape(relation.propertyAliasName + "_cnt") + " " + sqlOperator + " " + parseInt(where[key].value));
                            this.andWhere(
                                qb.getSql() +
                                    " " +
                                    sqlOperator +
                                    " " +
                                    parseInt(where[key].value),
                            )
                        }
                    } else {
                        // const joinAlias = alias + "_" + relation.propertyName;
                        let joinAlias =
                            alias +
                            "_" +
                            relation.propertyPath.replace(".", "_")
                        joinAlias = DriverUtils.buildAlias(
                            this.connection.driver,
                            { joiner: "__" },
                            alias,
                            joinAlias,
                        )

                        const existJoin = this.joins.find(
                            (join) => join.alias === joinAlias,
                        )
                        if (!existJoin) {
                            this.joins.push({
                                type: "inner",
                                select: false,
                                selection: undefined,
                                alias: joinAlias,
                                parentAlias: alias,
                                relationMetadata: relation,
                            })
                        } else {
                            if (existJoin.type === "left")
                                existJoin.type = "inner"
                        }

                        const condition = this.buildWhere(
                            where[key],
                            relation.inverseEntityMetadata,
                            joinAlias,
                        )
                        if (condition) {
                            andConditions.push(condition)
                            // parameterIndex = Object.keys(this.expressionMap.nativeParameters).length;
                        }
                    }
                }
            }
            condition = andConditions.join(" AND ")
        }
        return condition
    }
}