109366139

Build Type

github

Committed by GitHub

Commit Message

Merge 8716cfee7 into 9a180c829

Pull Request Pull Request #203: [Feat]: BETWEEN for Filters

Run Details

654 of 660 branches covered (99.09%)

Branch coverage included in aggregate %.

63 of 72 new or added lines in 11 files covered. (87.5%)

1 existing line in 1 file now uncovered.

2202 of 2213 relevant lines covered (99.5%)

171.75 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

94.05

/packages/query-typeorm/src/query/sql-comparison.builder.ts

import { ObjectLiteral } from 'typeorm';
import { FilterComparisonOperators } from '@nestjs-query/core';

/**
 * @internal
 */
type CmpSQLType = { sql: string; params: ObjectLiteral };

/**
 * @internal
 */
export type EntityComparisonField<Entity, F extends keyof Entity> = Entity[F] | Entity[F][] | true | false | null;

/**
 * @internal
 * Builder to create SQL Comparisons. (=, !=, \>, etc...)
 */
export class SQLComparisonBuilder<Entity> {
  paramCount = 0;

  static DEFAULT_COMPARISON_MAP: Record<string, string> = {
    eq: '=',
    neq: '!=',
    gt: '>',
    gte: '>=',
    lt: '<',
    lte: '<=',
    like: 'LIKE',
    notlike: 'NOT LIKE',
    ilike: 'ILIKE',
    notilike: 'NOT ILIKE',
  };

  constructor(readonly comparisonMap: Record<string, string> = SQLComparisonBuilder.DEFAULT_COMPARISON_MAP) {}

  private get paramName(): string {
    const param = `param${this.paramCount}`;
    this.paramCount += 1;
    return param;
  }

  /**
   * Creates a valid SQL fragment with parameters.
   *
   * @param field - the property in Entity to create the comparison for.
   * @param cmp - the FilterComparisonOperator (eq, neq, gt, etc...)
   * @param val - the value to compare to.
   */
  build<F extends keyof Entity>(
    field: F,
    cmp: FilterComparisonOperators<Entity[F]>,
    val: EntityComparisonField<Entity, F>,
    alias?: string,
  ): CmpSQLType {
    const col = alias ? `${alias}.${field}` : `${field}`;
    const normalizedCmp = (cmp as string).toLowerCase();
    if (this.comparisonMap[normalizedCmp]) {
      // comparison operator (e.b. =, !=, >, <)
      return this.createComparisonSQL(normalizedCmp, col, val);
    }
    if (normalizedCmp === 'is') {
      // is comparison (IS TRUE, IS FALSE, IS NULL)
      return this.isComparisonSQL(col, val);
    }
    if (normalizedCmp === 'isnot') {
      // is comparison (IS NOT TRUE, IS NOT FALSE, IS NOT NULL, etc...)
      return this.isNotComparisonSQL(col, val);
    }
    if (normalizedCmp === 'in') {
      // in comparison (field IN (1,2,3))
      return this.inComparisonSQL(col, val);
    }
    if (normalizedCmp === 'notin') {
      // in comparison (field IN (1,2,3))
      return this.notInComparisonSQL(col, val);
    }
    if (normalizedCmp === 'between') {
      // between comparison (field BETWEEN x AND y)
      return this.betweenComparisonSQL(col, val);
    }
    throw new Error(`unknown operator "${cmp}"`);
  }

  private createComparisonSQL<F extends keyof Entity>(
    cmp: string,
    col: string,
    val: EntityComparisonField<Entity, F>,
  ): CmpSQLType {
    const operator = this.comparisonMap[cmp];
    const { paramName } = this;
    return { sql: `${col} ${operator} :${paramName}`, params: { [paramName]: val } };
  }

  private isComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
    if (val === null) {
      return { sql: `${col} IS NULL`, params: {} };
    }
    if (val === true) {
      return { sql: `${col} IS TRUE`, params: {} };
    }
    if (val === false) {
      return { sql: `${col} IS FALSE`, params: {} };
    }
    throw new Error(`unexpected is operator param ${val}`);
  }

  private isNotComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
    if (val === null) {
      return { sql: `${col} IS NOT NULL`, params: {} };
    }
    if (val === true) {
      return { sql: `${col} IS NOT TRUE`, params: {} };
    }
    if (val === false) {
      return { sql: `${col} IS NOT FALSE`, params: {} };
    }
    throw new Error(`unexpected isNot operator param ${val}`);
  }

  private inComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
    this.checkNonEmptyArray(val);
    const { paramName } = this;
    return {
      sql: `${col} IN (:...${paramName})`,
      params: { [paramName]: val },
    };
  }

  private notInComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
    this.checkNonEmptyArray(val);
    const { paramName } = this;
    return {
      sql: `${col} NOT IN (:...${paramName})`,
      params: { [paramName]: val },
    };
  }

  private checkNonEmptyArray<F extends keyof Entity>(val: EntityComparisonField<Entity, F>): void {
    if (!Array.isArray(val)) {
      throw new Error(`Invalid in value expected an array got ${JSON.stringify(val)}`);
    }
    if (!val.length) {
      throw new Error(`Invalid in value expected a non-empty array got ${JSON.stringify(val)}`);
    }
  }

  private betweenComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
    const { paramName } = this;
    // TODO: this is not very good - can we better type this?!
    // eslint-disable-next-line
    const value = val as any;

    return {
      sql: `${col} BETWEEN :${paramName}_lower AND :${paramName}_upper`,
      params: {
        [`${paramName}_lower`]: value.lower,
        [`${paramName}_upper`]: value.upper,
      },
    };
  }
}

1	import { ObjectLiteral } from 'typeorm';
2	import { FilterComparisonOperators } from '@nestjs-query/core';
3
4	/**
5	* @internal
6	*/
7	type CmpSQLType = { sql: string; params: ObjectLiteral };
8
9	/**
10	* @internal
11	*/
12	export type EntityComparisonField<Entity, F extends keyof Entity> = Entity[F] \| Entity[F][] \| true \| false \| null;
13
14	/**
15	* @internal
16	* Builder to create SQL Comparisons. (=, !=, \>, etc...)
17	*/
18	export class SQLComparisonBuilder<Entity> {	51✔
19	paramCount = 0;	297✔
20
21	static DEFAULT_COMPARISON_MAP: Record<string, string> = {	51✔
22	eq: '=',
23	neq: '!=',
24	gt: '>',
25	gte: '>=',
26	lt: '<',
27	lte: '<=',
28	like: 'LIKE',
29	notlike: 'NOT LIKE',
30	ilike: 'ILIKE',
31	notilike: 'NOT ILIKE',
32	};
33
34	constructor(readonly comparisonMap: Record<string, string> = SQLComparisonBuilder.DEFAULT_COMPARISON_MAP) {}	297✔
35
36	private get paramName(): string {
37	const param = `param${this.paramCount}`;	222✔
38	this.paramCount += 1;	222✔
39	return param;	222✔
40	}
41
42	/**
43	* Creates a valid SQL fragment with parameters.
44	*
45	* @param field - the property in Entity to create the comparison for.
46	* @param cmp - the FilterComparisonOperator (eq, neq, gt, etc...)
47	* @param val - the value to compare to.
48	*/
49	build<F extends keyof Entity>(
50	field: F,
51	cmp: FilterComparisonOperators<Entity[F]>,
52	val: EntityComparisonField<Entity, F>,
53	alias?: string,
54	): CmpSQLType {
55	const col = alias ? `${alias}.${field}` : `${field}`;	264✔
56	const normalizedCmp = (cmp as string).toLowerCase();	264✔
57	if (this.comparisonMap[normalizedCmp]) {	264✔
58	// comparison operator (e.b. =, !=, >, <)
59	return this.createComparisonSQL(normalizedCmp, col, val);	126✔
60	}
61	if (normalizedCmp === 'is') {	138✔
62	// is comparison (IS TRUE, IS FALSE, IS NULL)
63	return this.isComparisonSQL(col, val);	15✔
64	}
65	if (normalizedCmp === 'isnot') {	123✔
66	// is comparison (IS NOT TRUE, IS NOT FALSE, IS NOT NULL, etc...)
67	return this.isNotComparisonSQL(col, val);	12✔
68	}
69	if (normalizedCmp === 'in') {	111✔
70	// in comparison (field IN (1,2,3))
71	return this.inComparisonSQL(col, val);	99✔
72	}
73	if (normalizedCmp === 'notin') {	12✔
74	// in comparison (field IN (1,2,3))
75	return this.notInComparisonSQL(col, val);	9✔
76	}
77	if (normalizedCmp === 'between') {	3!
78	// between comparison (field BETWEEN x AND y)
NEW 79	return this.betweenComparisonSQL(col, val);	×
80	}
81	throw new Error(`unknown operator "${cmp}"`);	3✔
82	}
83
84	private createComparisonSQL<F extends keyof Entity>(
85	cmp: string,
86	col: string,
87	val: EntityComparisonField<Entity, F>,
88	): CmpSQLType {
89	const operator = this.comparisonMap[cmp];	126✔
90	const { paramName } = this;	126✔
91	return { sql: `${col} ${operator} :${paramName}`, params: { [paramName]: val } };	126✔
92	}
93
94	private isComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
95	if (val === null) {	15✔
96	return { sql: `${col} IS NULL`, params: {} };	3✔
97	}
98	if (val === true) {	12✔
99	return { sql: `${col} IS TRUE`, params: {} };	6✔
100	}
101	if (val === false) {	6✔
102	return { sql: `${col} IS FALSE`, params: {} };	3✔
103	}
104	throw new Error(`unexpected is operator param ${val}`);	3✔
105	}
106
107	private isNotComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
108	if (val === null) {	12✔
109	return { sql: `${col} IS NOT NULL`, params: {} };	3✔
110	}
111	if (val === true) {	9✔
112	return { sql: `${col} IS NOT TRUE`, params: {} };	3✔
113	}
114	if (val === false) {	6✔
115	return { sql: `${col} IS NOT FALSE`, params: {} };	3✔
116	}
117	throw new Error(`unexpected isNot operator param ${val}`);	3✔
118	}
119
120	private inComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
121	this.checkNonEmptyArray(val);	99✔
122	const { paramName } = this;	93✔
123	return {	93✔
124	sql: `${col} IN (:...${paramName})`,
125	params: { [paramName]: val },
126	};
127	}
128
129	private notInComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
130	this.checkNonEmptyArray(val);	9✔
131	const { paramName } = this;	3✔
132	return {	3✔
133	sql: `${col} NOT IN (:...${paramName})`,
134	params: { [paramName]: val },
135	};
136	}
137
138	private checkNonEmptyArray<F extends keyof Entity>(val: EntityComparisonField<Entity, F>): void {
139	if (!Array.isArray(val)) {	108✔
140	throw new Error(`Invalid in value expected an array got ${JSON.stringify(val)}`);	6✔
141	}
142	if (!val.length) {	102✔
143	throw new Error(`Invalid in value expected a non-empty array got ${JSON.stringify(val)}`);	6✔
144	}
145	}
146
147	private betweenComparisonSQL<F extends keyof Entity>(col: string, val: EntityComparisonField<Entity, F>): CmpSQLType {
NEW UNCOV 148	const { paramName } = this;	×
149	// TODO: this is not very good - can we better type this?!
150	// eslint-disable-next-line
NEW 151	const value = val as any;	×
152
NEW 153	return {	×
154	sql: `${col} BETWEEN :${paramName}_lower AND :${paramName}_upper`,
155	params: {
156	[`${paramName}_lower`]: value.lower,
157	[`${paramName}_upper`]: value.upper,
158	},
159	};
160	}
161	}	51✔

doug-martin / nestjs-query / 109366139

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous