typeorm-query-builder-extended

TypeORM Query Builder Extended

This library allows you to automatically transform a Express.js req.query or a valid key/value object into a TypeORM SelectQueryBuilder that allows the option of making queries to a foreign relation/table through the query or key/value object parsed

Installation

yarn add typeorm-query-builder-extended

OR

npm install typeorm-query-builder-extended

How does it work?

A req.query string or valid key/value object is sent to the builder along with the initial SelectQueryBuilder that should have been previously created. The builder converts all props from the query object into a valid SelectQueryBuilder props and appends it to the afore added SelectQueryBuilder

Usage

Use ExtendedQueryBuilder export from package and pass your req.query or key/value object as arguments:

import { ExtendedQueryBuilder } from 'typeorm-query-builder-extended';

const queryBuilder = <YOUR_REPOSITORY>.createQueryBuilder(<YOUR_ALIAS>); // <YOUR_REPOSITORY> i.e: personRepository | <YOUR_ALIAS> i.e: person

const builder = new ExtendedQueryBuilder(queryBuilder, req.query); // The third parameter is an optional array of properties in the req.query that you do not want in the database query object.(i.e: page, limit, etc)
const builtQuery = builder.build();
// Now your query is built, use get the rows queried for
const results = builtQuery.getRawMany();

Structure of string query

One query is mainly of 4 parts:

• Table Alias The Alias denoting table in the query string
• Table column name The name of column of the table being queried on
• Query Lookup The operation done on the table
• Table Column Value The value of the query operation

Full Structure

foo/?<TABLE_ALIAS>_._<COLUMN_NAME>__<LOOKUP>=<COLUMN_VALUE>

The table alias

Denoted by _._ in the query string

i.e: person_._

/* Omitted... */.createQueryBuilder('person');

• Or when using joins:

.
.
.
queryBuilder.leftJoin('person.company', 'company');

The table's column name

Denoted by __ i.e:

• person_._firstName__ (IF YOU HAVE A LOOKUP YOU WANT TO ADD)
• person_._firstName (IF YOU DO NOT HAVE A LOOKUP YOU WANT TO ADD)

The query's lookup

Check the available lookups here

The table's column value

This is denoted by adding it after your query lookup

Example

• You can find a demo project of the package here

import { getRepository } from 'typeorm';
import { ExtendedQueryBuilder } from 'typeorm-query-builder-extended';
import { User } from './path-to-your-entity/User'; // Your typeORM entity class

.
.
.

app.get('/foo', (req, res) => {
  const userRepository = getRepository(User);
  const queryBuilder = userRepository.createQueryBuilder('user');
  const builder = new ExtendedQueryBuilder(queryBuilder, req.query);
  const builtQuery = builder.build();
  const results = builtQuery.getMany();
  return res.status(200).send(results); //returned results from the built query
});

Available Lookups

Lookup	Behaviour	Example	Types Working on
(none)	Return entries that match with value	foo=sam	String, Number, Enums
contains	Return entries that contains value	foo__contains=sa	String, Enums
startswith	Return entries that starts with value	foo__startswith=a	String
endswith	Return entries that ends with value	foo__endswith=am	String
lt	Return entries with value less than or equal to provided	foo__lt=18	Number, Date
lte	Return entries with value less than provided	foo__lte=18	Number, Date
gt	Returns entries with value greater than provided	foo__gt=18	Number, Date
gte	Return entries with value greater than or equal to provided	foo__gte=18	Number, Date
in	Return entries that match with values in list	foo__in=developer,tester	String, Enums, Number
between	Return entries in range	foo__between=1,27	String, Number
jsonarrcontain	Return entries that match a jsonb array column that contains value in the list	foo__jsonarrcontain=RED,BLUE,ORANGE	String, Number, Enums
not	Return entries that do not match with value	foo__not=ACTIVE	String, Number, Enums