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 { RelationIdLoader as QueryStrategyRelationIdLoader } from "./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 { OrmUtils } from "../util/OrmUtils" import { EntityPropertyNotFoundError } from "../error/EntityPropertyNotFoundError" import { AuroraMysqlDriver } from "../driver/aurora-mysql/AuroraMysqlDriver" import { InstanceChecker } from "../util/InstanceChecker" import { FindOperator } from "../find-options/FindOperator" import { ApplyValueTransformers } from "../util/ApplyValueTransformers" /** * Allows to build complex sql queries in a fashion way and execute those queries. */ export class SelectQueryBuilder extends QueryBuilder 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 | 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 this.replacePropertyNamesForTheWholeQuery(sql) } // ------------------------------------------------------------------------- // Public Methods // ------------------------------------------------------------------------- setFindOptions(findOptions: FindManyOptions) { this.findOptions = findOptions this.applyFindOptions() return this } /** * Creates a subquery - query that can be used inside other queries. */ subQuery(): SelectQueryBuilder { 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) => SelectQueryBuilder, 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) => SelectQueryBuilder), selectionAliasName?: string, ): SelectQueryBuilder { 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) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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 } fromDummy(): SelectQueryBuilder { return this.from( this.connection.driver.dummyTableName ?? "(SELECT 1 AS dummy_column)", "dummy_table", ) } /** * 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( entityTarget: (qb: SelectQueryBuilder) => SelectQueryBuilder, aliasName: string, ): SelectQueryBuilder /** * 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( entityTarget: EntityTarget, aliasName: string, ): SelectQueryBuilder /** * 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( entityTarget: | EntityTarget | ((qb: SelectQueryBuilder) => SelectQueryBuilder), aliasName: string, ): SelectQueryBuilder { const mainAlias = this.createFromAlias(entityTarget, aliasName) this.expressionMap.setMainAlias(mainAlias) return this as any as SelectQueryBuilder } /** * Specifies FROM which entity's table select/update/delete will be executed. * Also sets a main string alias of the selection data. */ addFrom( entityTarget: (qb: SelectQueryBuilder) => SelectQueryBuilder, aliasName: string, ): SelectQueryBuilder /** * Specifies FROM which entity's table select/update/delete will be executed. * Also sets a main string alias of the selection data. */ addFrom( entityTarget: EntityTarget, aliasName: string, ): SelectQueryBuilder /** * Specifies FROM which entity's table select/update/delete will be executed. * Also sets a main string alias of the selection data. */ addFrom( entityTarget: | EntityTarget | ((qb: SelectQueryBuilder) => SelectQueryBuilder), aliasName: string, ): SelectQueryBuilder { const alias = this.createFromAlias(entityTarget, aliasName) if (!this.expressionMap.mainAlias) this.expressionMap.setMainAlias(alias) return this as any as SelectQueryBuilder } /** * 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, ) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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, ) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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, ) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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, ) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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, ) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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, ) => SelectQueryBuilder, alias: string, condition?: string, parameters?: ObjectLiteral, mapAsEntity?: Function | string, ): 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) => SelectQueryBuilder), alias: string, condition?: string, parameters?: ObjectLiteral, mapAsEntity?: Function | string, ): this { this.addSelect(alias) this.join( "INNER", entityOrProperty, alias, condition, parameters, mapToProperty, false, mapAsEntity, ) 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, ) => SelectQueryBuilder, 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) => SelectQueryBuilder), 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, ) => SelectQueryBuilder, alias: string, condition?: string, parameters?: ObjectLiteral, mapAsEntity?: Function | string, ): 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) => SelectQueryBuilder), alias: string, condition?: string, parameters?: ObjectLiteral, mapAsEntity?: Function | string, ): this { this.addSelect(alias) this.join( "LEFT", entityOrProperty, alias, condition, parameters, mapToProperty, false, mapAsEntity, ) return this } /** */ // selectAndMap(mapToProperty: string, property: string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder) => SelectQueryBuilder)): this; /** */ // selectAndMap(mapToProperty: string, entity: Function|string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder) => SelectQueryBuilder)): this; /** */ // selectAndMap(mapToProperty: string, tableName: string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder) => SelectQueryBuilder)): this; /** */ // selectAndMap(mapToProperty: string, entityOrProperty: Function|string, aliasName: string, qbFactory: ((qb: SelectQueryBuilder) => SelectQueryBuilder)): 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, ) => SelectQueryBuilder, ): 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, ) => SelectQueryBuilder, ): 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, ) => SelectQueryBuilder, ): 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 } /** * Sets a new where EXISTS clause */ whereExists(subQuery: SelectQueryBuilder): this { return this.where(...this.getExistsCondition(subQuery)) } /** * Adds a new AND where EXISTS clause */ andWhereExists(subQuery: SelectQueryBuilder): this { return this.andWhere(...this.getExistsCondition(subQuery)) } /** * Adds a new OR where EXISTS clause */ orWhereExists(subQuery: SelectQueryBuilder): this { return this.orWhere(...this.getExistsCondition(subQuery)) } /** * 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 } /** * Enables time travelling for the current query (only supported by cockroach currently) */ timeTravelQuery(timeTravelFn?: string | boolean): this { if (this.connection.driver.options.type === "cockroachdb") { if (timeTravelFn === undefined) { this.expressionMap.timeTravel = "follower_read_timestamp()" } else { this.expressionMap.timeTravel = timeTravelFn } } 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" and "pessimistic_write_or_fail" are deprecated and will be removed in a future version. Use setOnLocked instead. */ | "pessimistic_partial_write" | "pessimistic_write_or_fail" | "for_no_key_update" | "for_key_share", lockVersion?: undefined, lockTables?: string[], ): this /** * Sets locking mode. */ setLock( lockMode: | "optimistic" | "pessimistic_read" | "pessimistic_write" | "dirty_read" /* "pessimistic_partial_write" and "pessimistic_write_or_fail" are deprecated and will be removed in a future version. Use setOnLocked instead. */ | "pessimistic_partial_write" | "pessimistic_write_or_fail" | "for_no_key_update" | "for_key_share", lockVersion?: number | Date, lockTables?: string[], ): this { this.expressionMap.lockMode = lockMode this.expressionMap.lockVersion = lockVersion this.expressionMap.lockTables = lockTables return this } /** * Sets lock handling by adding NO WAIT or SKIP LOCKED. */ setOnLocked(onLocked: "nowait" | "skip_locked"): this { this.expressionMap.onLocked = onLocked 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(): Promise { return (await this.getRawMany())[0] } /** * Gets all raw results returned by execution of generated query builder sql. */ async getRawMany(): Promise { 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(): 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 { 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 { const entity = await this.getOne() if (!entity) { throw new EntityNotFoundError( this.expressionMap.mainAlias!.target, this.expressionMap.parameters, ) } return entity } /** * Gets entities returned by execution of generated query builder sql. */ async getMany(): Promise { 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 offset, limit, skip, and take. */ async getCount(): Promise { 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() } } /** * Gets exists * Returns whether any rows exists matching current query. */ async getExists(): Promise { 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.executeExistsQuery(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 { 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) => SelectQueryBuilder), aliasName: string, condition?: string, parameters?: ObjectLiteral, mapToProperty?: string, isMappingMany?: boolean, mapAsEntity?: Function | string, ): void { if (parameters) { this.setParameters(parameters) } const joinAttribute = new JoinAttribute( this.connection, this.expressionMap, ) joinAttribute.direction = direction joinAttribute.mapAsEntity = mapAsEntity joinAttribute.mapToProperty = mapToProperty joinAttribute.isMappingMany = isMappingMany joinAttribute.entityOrProperty = entityOrProperty // relationName joinAttribute.condition = condition // joinInverseSideCondition // joinAttribute.junctionAlias = joinAttribute.relation.isOwning ? parentAlias + "_" + destinationTableAlias : destinationTableAlias + "_" + parentAlias; this.expressionMap.joinAttributes.push(joinAttribute) const joinAttributeMetadata = joinAttribute.metadata if (joinAttributeMetadata) { if ( joinAttributeMetadata.deleteDateColumn && !this.expressionMap.withDeleted ) { const conditionDeleteColumn = `${aliasName}.${joinAttributeMetadata.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: joinAttributeMetadata, }) 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 = ( entityOrProperty as any )((this as any as SelectQueryBuilder).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 ( DriverUtils.isPostgresFamily(driver) && 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 "" return ( " ORDER BY " + Object.keys(orderBys) .map((columnName) => { const orderValue = typeof orderBys[columnName] === "string" ? orderBys[columnName] : (orderBys[columnName] as any).order + " " + (orderBys[columnName] as any).nulls const selection = this.expressionMap.selects.find( (s) => s.selection === columnName, ) if ( selection && !selection.aliasName && columnName.indexOf(".") !== -1 ) { const criteriaParts = columnName.split(".") const aliasName = criteriaParts[0] const propertyPath = criteriaParts.slice(1).join(".") const alias = this.expressionMap.aliases.find( (alias) => alias.name === aliasName, ) if (alias) { const column = alias.metadata.findColumnWithPropertyPath( propertyPath, ) if (column) { const orderAlias = DriverUtils.buildAlias( this.connection.driver, undefined, aliasName, column.databaseName, ) return ( this.escape(orderAlias) + " " + orderValue ) } } } return ( this.replacePropertyNames(columnName) + " " + orderValue ) }) .join(", ") ) } /** * 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" || this.connection.driver.options.type === "spanner" ) { 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 ( !( DriverUtils.isPostgresFamily(driver) || 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(", ") } let onLockExpression = "" if (this.expressionMap.onLocked === "nowait") { onLockExpression = " NOWAIT" } else if (this.expressionMap.onLocked === "skip_locked") { onLockExpression = " SKIP LOCKED" } switch (this.expressionMap.lockMode) { case "pessimistic_read": if ( driver.options.type === "mysql" || driver.options.type === "aurora-mysql" ) { if ( DriverUtils.isReleaseVersionOrGreater(driver, "8.0.0") ) { return ( " FOR SHARE" + lockTablesClause + onLockExpression ) } else { return " LOCK IN SHARE MODE" } } else if (driver.options.type === "mariadb") { return " LOCK IN SHARE MODE" } else if (DriverUtils.isPostgresFamily(driver)) { return " FOR SHARE" + lockTablesClause + onLockExpression } 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" + onLockExpression } else if ( DriverUtils.isPostgresFamily(driver) || driver.options.type === "cockroachdb" ) { return " FOR UPDATE" + lockTablesClause + onLockExpression } else if (driver.options.type === "mssql") { return "" } else { throw new LockNotSupportedOnGivenDriverError() } case "pessimistic_partial_write": if (DriverUtils.isPostgresFamily(driver)) { 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 ( DriverUtils.isPostgresFamily(driver) || 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 ( DriverUtils.isPostgresFamily(driver) || driver.options.type === "cockroachdb" ) { return ( " FOR NO KEY UPDATE" + lockTablesClause + onLockExpression ) } else { throw new LockNotSupportedOnGivenDriverError() } case "for_key_share": if (DriverUtils.isPostgresFamily(driver)) { return ( " FOR KEY SHARE" + lockTablesClause + onLockExpression ) } 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[] = [] const escapedAliasName = this.escape(aliasName) allColumns.forEach((column) => { let selectionPath = escapedAliasName + "." + this.escape(column.databaseName) if (column.isVirtualProperty && column.query) { selectionPath = `(${column.query(escapedAliasName)})` } 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 (DriverUtils.isPostgresFamily(this.connection.driver)) 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, undefined, 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, undefined, 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" || DriverUtils.isPostgresFamily(this.connection.driver) ) { // 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 (this.connection.driver.options.type === "spanner") { // spanner also has gotta be different from everyone else. // they do not support concatenation of different column types without casting them to string if (primaryColumns.length === 1) { return `COUNT(DISTINCT(${distinctAlias}.${this.escape( primaryColumns[0].databaseName, )}))` } const columnsExpression = primaryColumns .map( (primaryColumn) => `CAST(${distinctAlias}.${this.escape( primaryColumn.databaseName, )} AS STRING)`, ) .join(", '|;|', ") 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 { 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 async executeExistsQuery( queryRunner: QueryRunner, ): Promise { const results = await this.connection .createQueryBuilder() .fromDummy() .select("1", "row_exists") .whereExists(this) .limit(1) .loadRawResults(queryRunner) return results.length > 0 } 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) : 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) : 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" || this.findOptions.lock.mode === "for_key_share" ) { 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.lock.onLocked) { this.setOnLocked(this.findOptions.lock.onLocked) } } } 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); // } } } public concatRelationMetadata(relationMetadata: RelationMetadata) { this.relationMetadatas.push(relationMetadata) } /** * 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" || this.expressionMap.lockMode === "for_key_share") && !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, undefined, 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, undefined, "ids_" + mainAliasName, primaryColumn.databaseName, ) return `${distinctAlias}.${columnAlias} AS ${this.escape( alias, )}` }, ) const originalQuery = this.clone() // preserve original timeTravel value since we set it to "false" in subquery const originalQueryTimeTravel = originalQuery.expressionMap.timeTravel rawResults = await new SelectQueryBuilder( this.connection, queryRunner, ) .select(`DISTINCT ${querySelects.join(", ")}`) .addSelect(selects) .from( `(${originalQuery .orderBy() .timeTravelQuery(false) // set it to "false" since time travel clause must appear at the very end and applies to the entire SELECT clause. .getQuery()})`, "distinctAlias", ) .timeTravelQuery(originalQueryTimeTravel) .offset(this.expressionMap.skip) .limit(this.expressionMap.take) .orderBy(orderBys) .cache( this.expressionMap.cache && this.expressionMap.cacheId ? `${this.expressionMap.cacheId}-pagination` : this.expressionMap.cache, 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}` const paramKeyResult = DriverUtils.buildAlias( this.connection.driver, undefined, "ids_" + mainAliasName, primaryColumn.databaseName, ) parameters[paramKey] = result[paramKeyResult] return `${mainAliasName}.${primaryColumn.propertyPath}=:${paramKey}` }) .join(" AND ") }) .join(" OR ") } else { const alias = DriverUtils.buildAlias( this.connection.driver, undefined, "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") { const queryStrategyRelationIdLoader = new QueryStrategyRelationIdLoader(this.connection, queryRunner) 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 queryStrategyRelationIdLoader.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, undefined, aliasName, column!.databaseName, ), ) ) } else { if ( this.expressionMap.selects.find( (select) => select.selection === orderCriteria || select.aliasName === orderCriteria, ) ) return ( this.escape(parentAlias) + "." + this.escape(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, undefined, aliasName, column!.databaseName, ), ) ] = orderBys[orderCriteria] } else { if ( this.expressionMap.selects.find( (select) => select.selection === orderCriteria || select.aliasName === orderCriteria, ) ) { orderByObject[ this.escape(parentAlias) + "." + this.escape(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 const isCachingEnabled = // Caching is enabled globally and isn't disabled locally. (cacheOptions.alwaysEnabled && this.expressionMap.cache) || // ...or it's enabled locally explicitly. this.expressionMap.cache let cacheError = false if (this.connection.queryResultCache && isCachingEnabled) { 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 && isCachingEnabled ) { 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, ): 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, 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, metadata, alias, propertyPath, ) // } 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, relation.inverseEntityMetadata, joinAlias); } } } protected buildRelations( relations: FindOptionsRelations, selection: FindOptionsSelect | 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.concatRelationMetadata(relation) } else { // join this.joins.push({ type: "left", select: true, selection: selection && typeof selection[relationName] === "object" ? (selection[ relationName ] as FindOptionsSelect) : undefined, alias: joinAlias, parentAlias: alias, relationMetadata: relation, }) if ( selection && typeof selection[relationName] === "object" ) { this.buildSelect( selection[ relationName ] as FindOptionsSelect, 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, selection: FindOptionsSelect | 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, 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, 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?.toLowerCase() === "first" ? "NULLS FIRST" : nulls?.toLowerCase() === "last" ? "NULLS LAST" : undefined let aliasPath = `${alias}.${propertyPath}` // const selection = this.expressionMap.selects.find( // (s) => s.selection === aliasPath, // ) // if (selection) { // // this is not building correctly now??? // aliasPath = this.escape( // DriverUtils.buildAlias( // this.connection.driver, // undefined, // alias, // column.databaseName, // ), // ) // // selection.aliasName = aliasPath // } else { // if (column.isVirtualProperty && column.query) { // aliasPath = `(${column.query(alias)})` // } // } // console.log("add sort", selection, aliasPath, direction, nulls) this.addOrderBy(aliasPath, direction, nulls) // this.orderBys.push({ alias: alias + "." + propertyPath, direction, nulls }); } else if (embed) { this.buildOrder( order[key] as FindOptionsOrder, 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, relation.inverseEntityMetadata, joinAlias, ) } } } protected buildWhere( where: FindOptionsWhere[] | FindOptionsWhere, metadata: EntityMetadata, alias: string, embedPrefix?: string, ) { let condition: string = "" // let parameterIndex = Object.keys(this.expressionMap.nativeParameters).length; if (Array.isArray(where) && where.length) { 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) { let aliasPath = `${alias}.${propertyPath}` if (column.isVirtualProperty && column.query) { aliasPath = `(${column.query(alias)})` } // 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 instanceof FindOperator ? parameterValue.transformValue(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" || where[key].type === "moreThanOrEqual" || where[key].type === "lessThanOrEqual" ) { let sqlOperator = "" if (where[key].type === "moreThan") { sqlOperator = ">" } else if (where[key].type === "lessThan") { sqlOperator = "<" } else if (where[key].type === "moreThanOrEqual") { sqlOperator = ">=" } else if (where[key].type === "lessThanOrEqual") { sqlOperator = "<=" } // basically relation count functionality const qb: QueryBuilder = 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 { if ( relation.isManyToOne || (relation.isOneToOne && relation.isOneToOneOwner) ) { const aliasPath = `${alias}.${propertyPath}` andConditions.push( this.createWhereConditionExpression( this.getWherePredicateCondition( aliasPath, where[key], ), ), ) } else { throw new Error( `This relation isn't supported by given find operator`, ) } } } 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: "left", select: false, selection: undefined, alias: joinAlias, parentAlias: alias, relationMetadata: relation, }) } 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 } }