Filtering GraphQL Query using TypeScript and TypeORM
Tags: graphql, filter, query, typeorm
Published: 2020-04-09 23:20:38


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              |

&nbsp;

### TypeORM entity classes
[TypeORM](https://typeorm.io) 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:

```typescript
@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.

&nbsp;

### 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 [`_**
&nbsp;&nbsp; **_`    (order date >= '2016-08-27') OR (customers with ids 11, 12) OR `_**
&nbsp;&nbsp; **_`    (customers whose postal code contains '5NT')
]`_**

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

```javascript
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:

```javascript
a AND b AND (c OR d OR e)
```

This logical expression we can represent as expression tree:
![Tree representation of the logical expression](https://dev-to-uploads.s3.amazonaws.com/i/70x54qoq11ytdio28a11.png)

Take a look [here](https://en.wikipedia.org/wiki/Binary_expression_tree) for more information about expression trees.

&nbsp;

##GraphQL schema

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

```graphql
    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).

*&#42; 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:

```graphql
    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!]!
    }
```

&nbsp;

## 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:

```graphql
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
    }
  }
}

```  
&nbsp;

##Server Side

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

#####OrderResolvers.ts
```typescript
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
```typescript
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
```typescript
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
```typescript
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}`);
    }
  }
}
```

-----------

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

```sql
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%')
      )
```

&nbsp;

##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.
  
&nbsp;