Skip to content

Instantly share code, notes, and snippets.

@krzysztof-miemiec
Created June 11, 2017 14:22
Show Gist options
  • Save krzysztof-miemiec/a18b89adc6f5cc900465d198fa0facb0 to your computer and use it in GitHub Desktop.
Save krzysztof-miemiec/a18b89adc6f5cc900465d198fa0facb0 to your computer and use it in GitHub Desktop.
Simple wrapper for TypeORM's QueryBuilder.
import { EntityManager, ObjectType, QueryBuilder } from 'typeorm';
interface Params {
[key: string]: string | number | boolean | string[] | number[] | boolean[];
}
interface OrWhere {
or: Where[];
}
type Where = OrWhere | Params;
export class Query<T> {
public static from<T>(queryBuilder: QueryBuilder<T>): Query<T> {
return new Query<T>(queryBuilder);
}
public static forEntity<T>(manager: EntityManager, entityClass: ObjectType<T> | (() => T) | string, alias: string): Query<T> {
return new Query<T>(manager.createQueryBuilder(entityClass, alias));
}
private constructor(private queryBuilder: QueryBuilder<T>) {
}
public deepPopulate(name: string, fields: string[]): this {
for (const field of fields) {
const subfields = field.split('.');
if (subfields.length === 0) continue;
let parentName = name;
for (const subfield of subfields) {
const newParentName = parentName + '_' + subfield;
this.queryBuilder.leftJoinAndSelect(parentName + '.' + subfield, newParentName);
parentName = newParentName;
}
}
return this;
}
public and<A, B, C>(func: (query: Query<T>, a?: A, b?: B, c?: C) => any, a?: A, b?: B, c?: C): Query<T> {
func(this, a, b, c);
return this;
}
public where(where: Where, name?: string): this {
const { query, params } = this.getWhereQuery(where, name);
this.queryBuilder.where(query, params);
return this;
}
public andWhere(where: Where, name?: string): this {
const { query, params } = this.getWhereQuery(where, name);
this.queryBuilder.andWhere(query, params);
return this;
}
public builder(): QueryBuilder<T> {
return this.queryBuilder;
}
public getMany(): Promise<T[]> {
return this.queryBuilder.getMany();
}
public getOne(): Promise<T | undefined> {
return this.queryBuilder.getOne();
}
private getWhereQuery(where: Where, name?: string): { query: string, params: Params } {
const orWhere: Where[] = (where as any).or;
if (orWhere) {
const wheres = orWhere.map(w => this.getWhereQuery(w, name));
const query = '(' + wheres.map(w => w.query).join(' OR ') + ')';
const params = Object.assign({}, ...wheres.map(w => w.params));
return { query, params };
}
const whereParams: Params = where as Params;
let query = '(';
const params: Params = {};
const keys = Object.keys(whereParams).filter(k => whereParams.hasOwnProperty(k));
const prefix = name ? name + '.' : '';
for (let i = 0; i < keys.length; i++) {
const key = keys[i];
const flatKey = key.replace(/./g, '_');
const value = params[key];
const field = prefix + '"' + key + '"';
if (!Array.isArray(value)) {
if (value === null || value === undefined) {
query += field + ' IS NULL';
} else {
query += field + '=:' + flatKey;
params[flatKey] = value;
}
} else {
if (value.length === 0) continue;
query += field + ' IN (';
for (let j = 0; j < value.length; j++) {
const itemFlatKey = flatKey + '_' + j;
query += ':' + itemFlatKey;
params[itemFlatKey] = value[j];
if (j + 1 < value.length) {
query += ', ';
}
}
query += ')';
}
if (i + 1 < keys.length) {
query += ' AND ';
}
}
query += ')';
return { query, params };
}
}
// Not 100% sure if everything works, but you should be able to do something like this:
// Find guys named john, bob or mark, that work in Boring Company or have family members working there.
// Include their addresses, company and family members in results.
const results = await Query.forEntity(db.manager, Person, 'person')
.deepPopulate('person', [
'worksAt.company',
'address',
'family.worksAt.company'
])
.where({
or: [
{'person_worksAt_company.name': 'Boring Company'},
{'person_family_worksAt_company.name': 'Boring Company'},
{'name': ['john', 'bob', 'mark']}
]
}, 'person')
.getMany();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment