Filtering GraphQL Query using TypeScript and TypeORM

This article explores filtering capabilities we can add to the GraphQL API. In some cases we need to filter data by applying one or many matching rules or composition of various rules.

The idea is to expose an ORM-like query-ability through our GraphQL API where we can have flexibility on the client-side to ask for complex pieces of data.

Let's have the following example of tables in the relational database:

Customers

id	name	city	postal_code
11	Harry Ashworth	London	EC2 5NT
12	Patricio Simpson	Buenos Aires	51010
13	Victoria Chang	London	N6 4AL

Orders

id	customer_id	order_date	quantity	product
10289	11	2016-08-26	30	Toys
10290	11	2016-08-27	25	Programming books
10410	12	2017-01-10	49	Programming books
10411	12	2017-03-15	34	Fiction books
10259	13	2016-07-18	10	Toys

 

TypeORM entity classes

TypeORM is an ORM that can run in various JavaScript platforms (e.g. Node.js) and can be used with TypeScript. These are the TypeORM entity classes matching the tables introduced above:

@Entity("Customers")
export class CustomerEntity {
  @PrimaryGeneratedColumn({ type: "int", name: "id" })
  id: number;

  @Column("varchar", { name: "name", length: 255 })
  name: string;

  @Column("varchar", { name: "city", length: 255 })
  city: string;

  @Column("varchar", { name: "postal_code", length: 255 })
  postalCode: string;

  @OneToMany(() => OrderEntity, order => order.customer)
  orders: OrderEntity[];
}


@Entity("Orders")
export class OrderEntity {
  @PrimaryGeneratedColumn({ type: "int", name: "id" })
  id: number;

  @Column("datetime", { name: "order_date" })
  orderDate: Date;

  @Column("int", { name: "quantity" })
  quantity: number;

  @Column("varchar", { name: "product", length: 255 })
  product: string;

  @ManyToOne(() => CustomerEntity, customer => customer.orders)
  @JoinColumn({name: 'customer_id', referencedColumnName: 'id'})
  customer: CustomerEntity;
}

Note the additional relation field at the end of each class. They describe the relation between these two tables. In our case it is one-to-many relation between Customers and Orders. So one customer can have many orders.

 

Query Filters

Now let's say that our client-side needs to ask a complex query over the data that was presented above. For example something like this:

Get all orders that:
(have quantity >= 20) AND (ordered books) AND [
   (order date >= '2016-08-27') OR (customers with ids 11, 12) OR
   (customers whose postal code contains '5NT')
]

We can break this complex query to atomic filters in SQL format:

a = (OrderEntity.quantity >= 20)
b = (OrderEntity.product LIKE '%books%')
c = (OrderEntity.orderDate >= '2016-08-27')
d = (CustomerEntity.id IN (11, 12))
e = (CustomerEntity.postalCode LIKE '%5NT%')

So the logical expression will be:

a AND b AND (c OR d OR e)

This logical expression we can represent as expression tree:

Take a look here for more information about expression trees.

 

GraphQL schema

Now let's create a GraphQL schema that will allow us to make such a query.

    enum Operator {
      AND
      OR
    }

    enum Operation {
      EQ
      IN
      LIKE
      GE
    }

    input Filter {
      op: Operation!
      values: [String!]!
      field: String!
      relationField: String
    }

    input FiltersExpression {
      operator: Operator!
      filters: [Filter!]
      childExpressions: [FiltersExpression!]
    }

The main type here is FiltersExpression which corresponds to a non-leaf node in the expression tree. These are logical operation nodes like 'AND' or 'OR'.
The leaf nodes are represented by Filter type. These are the atomic filters that we named with the lowercase letters (a, b, c, d, e).

FiltersExpression

• operator - logical operator ('AND' or 'OR').
• filters - descendant atomic filters (leaf nodes) of this node.
• childExpressions - descendant sub expressions (sub trees).

* When building the logical expression of the given FiltersExpression node, operator is put between all the descendants of this node.

Filter

GraphQL type for atomic filter (leaf node of the expression tree). For example: CustomerEntity.id IN (11, 12).

• op - conditional operation of the atomic filter (op: IN).
• values - the values of the filter (values: 11, 12).
• field - table field name (field: CustomerEntity.id).
• relationField - this optional parameter represents a foreign key in the main table that takes us to the table of the field. In TypeORM we add such a field to the entity class to have a relation between the tables. relationField will be used to join these tables in SQL query. (in this example: relationField: OrderEntity.customer).

---

Now let's look at the rest of the server side GraphQL schema. Here we have Customer and Order types in accordance with the TypeORM entities, and getOrders query that receives the filters expression and returns all the suitable orders:

    type Customer {
      id: Int!
      name: String!
      city: String
      postalCode: String!
    }

    type Order {
      id: Int!
      customer: Customer!
      orderDate: String!
      quantity: Int!
      product: String!
    }

    extend type Query {
      getOrders(filters: FiltersExpression): [Order!]!
    }

 

Client Side

On the client side we would like to call the getOrders query using that complex filter expression from our main example. This is the body of the GraphQL query sent by the client:

query getOrders {
  getOrders(filters: {
    operator: AND
    filters: [
      {
        field: "OrderEntity.quantity"
        op: GE
        values: ["20"]
      },
      {
        field: "OrderEntity.product"
        op: LIKE
        values: ["books"]
      }
    ]
    childExpressions: [
      {
        operator: OR        
        filters: [
          {
            field: "OrderEntity.orderDate"
            op: GE
            values: ["2016-08-27"]
          },
          {
            field: "CustomerEntity.id"
            relationField: "OrderEntity.customer"
            op: IN
            values: ["11", "12"]
          },
          {
            field: "CustomerEntity.postalCode"
            relationField: "OrderEntity.customer"
            op: LIKE
            values: ["5NT"]
          }
        ]
      }
    ]
  }) {
    id
    orderDate
    quantity
    product
    customer {
      name
      city
    }
  }
}

 

Server Side

Now we are ready to implement this filtered query. Please take a look at the graphql resolver of getOrders:

OrderResolvers.ts

import {getRepository} from 'typeorm';
import {SelectQueryBuilder} from 'typeorm/query-builder/SelectQueryBuilder';
import {OrderEntity} from './OrderEntity';

export const resolvers = {
  Query: {
    getOrders: (parent, {filters}): Promise<OrderEntity[]> => {
        const ordersRepo = getRepository(OrderEntity);
        const fqb = new FilterQueryBuilder<OrderEntity>(ordersRepo, filters);
        const qb: SelectQueryBuilder = fqb.build();

        return qb.getMany();
    }
}

---

The resolver uses FilterQueryBuilder to build the TypeORM query. Let's create it.

FilterQueryBuilder.ts

import {Repository} from 'typeorm';
import {SelectQueryBuilder} from 'typeorm/query-builder/SelectQueryBuilder';

export default class FilterQueryBuilder<Entity> {
  private readonly qb: SelectQueryBuilder<Entity>;

  constructor(entityRepository: Repository<Entity>,
              private filtersExpression?: FiltersExpression) {
    this.qb = entityRepository.createQueryBuilder();
  }

  build() {
    const jb = new JoinBuilder<Entity>(this.qb, this.filtersExpression);
    jb.build();

    const wb = new WhereBuilder<Entity>(this.qb, this.filtersExpression);
    wb.build();

    return this.qb;
  }
}

---

JoinBuilder recursively traverses the FiltersExpression and adds a LEFT JOIN for each relationField.

JoinBuilder.ts

import {forEach} from 'lodash';
import {SelectQueryBuilder} from 'typeorm/query-builder/SelectQueryBuilder';

class JoinBuilder<Entity> {
  private joinedEntities = new Set<string>();

  constructor(private readonly qb: SelectQueryBuilder<Entity>,
              private filtersExpression?: FiltersExpression) {
  };

  build() {
    if (this.filtersExpression)
      this.buildJoinEntitiesRec(this.filtersExpression);
  }

  private buildJoinEntitiesRec(fe: FiltersExpression) {
    forEach(fe.filters, f => this.addJoinEntity(f.field, f.relationField));
    forEach(fe.childExpressions, child => this.buildJoinEntitiesRec(child));
  }

  private addJoinEntity(field: string, relationField?: string) {
    const entityName = field.split('.')[0];

    if (relationField && !this.joinedEntities.has(entityName)) {
      this.qb.leftJoinAndSelect(relationField, entityName);
      this.joinedEntities.add(entityName);
    }
  }
}

---

WhereBuilder recursively goes over the filters expression tree and builds the WHERE clause of the SQL query.

WhereBuilder.ts

import { isEmpty, map } from 'lodash';
import {SelectQueryBuilder} from 'typeorm/query-builder/SelectQueryBuilder';

type ParamValue = string | number | Array<string|number>;


export default class WhereBuilder<Entity> {
  private params: Record<string, ParamValue> = {};
  private paramsCount = 0;

  constructor(private readonly qb: SelectQueryBuilder<Entity>,
              private filtersExpression?: FiltersExpression) {
  };

  build() {
    if (!this.filtersExpression)
      return;

    const whereSql = this.buildExpressionRec(this.filtersExpression);
    this.qb.where(whereSql, this.params);
  }

  private buildExpressionRec(fe: FiltersExpression): string {
    const filters = map(fe.filters, f => this.buildFilter(f));
    const children = map(fe.childExpressions, child => this.buildExpressionRec(child));

    const allSqlBlocks = [...filters, ...children];
    const sqLExpr = allSqlBlocks.join(` ${fe.operator} `);
    return isEmpty(sqLExpr) ? '' : `(${sqLExpr})`;
  }

  private buildFilter(filter: Filter): string {
    const paramName = `${filter.field}_${++this.paramsCount}`;

    switch (filter.op) {
      case 'EQ':
        this.params[paramName] = filter.values[0];
        return `${filter.field} = :${paramName}`;
      case 'IN':
        this.params[paramName] = filter.values;
        return `${filter.field} IN (:${paramName})`;
      case 'LIKE':
        this.params[paramName] = `%${filter.values[0]}%`;
        return `${filter.field} LIKE :${paramName}`;
      case 'GE':
        this.params[paramName] = filter.values[0];
        return `${filter.field} >= :${paramName}`;
      default:
        throw new Error(`Unknown filter operation: ${filter.op}`);
    }
  }
}

---

 
Finally, this is the SQL query that was built by FilterQueryBuilder for our example:

SELECT *
FROM Orders o, 
LEFT JOIN Customers c ON o.customer_id = c.id
WHERE (o.quantity >= 20) AND (o.product LIKE '%books%')
      AND 
      (
        (o.order_date >= '2016-08-27') OR 
        (c.id IN (11, 12)) OR 
        (c.postal_code LIKE '%5NT%')
      )

 

Conclusion

This kind of GraphQL API offers great flexibility and control for client applications. All this while keeping the type safety and run-time validation that we get out-of-the-box from GraphQL.
With different combinations of rules to filter your data, you can exactly express the data you are interested in and let the backend to fetch it for you.

In a similar way we can also add sorting and pagination to our query enhancing it even more.

 

Comments

[James]

Superb! Utilizing the TypeORM with complex query of GraphQL.

[Abdur Rehman]

Very helpful article. I think, need some update also.
Use following code for IN
case 'IN':
this.params[paramName] = filter.values;
return${filter.field} IN (:...${paramName});

[Alex Panfilkin]

You can also try this library, which is complete solution for the filters and not only npmjs.com/package/nestjs-graphql-t...

[astra]

Hello, I followed your implementation on my project. Just wondering how to create a unit test on this?