Queries

The query interface contains three optional fields.

filter
paging
sorting

All examples will be based on the following class.

interface MyClass {

  title: string
  completed: boolean
  age: number
}

Filtering

The filter field allows the filtering of fields based on the shape of the object the filter is used for. See the filter reference for a complete list of comparisons available.

The Filter interface is typesafe and the typescript compiler will complain if you include extra fields that are not present on the type you are creating the query for.

Lets create a simple filter that would allow us to filter for titles equal to 'Foo Bar'

Simple

import { Query } from '@ptc-org/nestjs-query-core'

const q: Query<MyClass> = {
  filter: {
    title: { eq: 'Foo Bar' }
  }
}

Multiple Fields

You can also filter on multiple fields.

import { Query } from '@ptc-org/nestjs-query-core'

const q: Query<MyClass> = {
  filter: {
    // title = 'Foo Bar' AND completed IS TRUE and age > 10
    title: { eq: 'Foo Bar' },
    completed: { is: true },
    age: { gt: 10 }
  }
}

Multiple Comparisons on a single field.

If you include multiple comparisons for a single field they will be ORed together.

import { Query } from '@ptc-org/nestjs-query-core'

const q: Query<MyClass> = {
  filter: {
    // title = 'Foo Bar' OR field LIKE '%foo%'
    title: { eq: 'Foo Bar', like: '%foo%' }
  }
}

And/Or

The filter also allows for more complex and and or filters. The and and or accept an array of filters allowing for nested complex queries. In this example we AND two filters for the same property together: age >= 10 AND age <= 20.

const q: Query<MyClass> = {
  filter: {
    and: [{ age: { gte: 10 } }, { age: { lte: 20 } }]
  }
}

In this example a simple OR condition is created: age >= 10 OR title NOT LIKE '%bar'

const q: Query<MyClass> = {
  filter: {
    or: [{ age: { gte: 10 } }, { title: { notLike: '%bar' } }]
  }
}

This example combines AND and OR filters: age >= 10 AND (title LIKE '%bar' OR title = 'foobar').

const q: Query<MyClass> = {
  filter: {
    and: [
      { age: { gte: 10 } },
      {
        or: [{ title: { like: '%bar' } }, { title: { eq: 'foobar' } }]
      }
    ]
  }
}

Paging

The core package defines a basic paging interface has two optional fields limit and offset.

Limit And Offset
Limit
Offset

const q: Query<MyClass> = {
  paging: {
    limit: 10,
    offset: 10,
  },
};

const q: Query<MyClass> = {
  paging: {
    limit: 20,
  },
};

const q: Query<MyClass> = {
  paging: {
    offset: 10,
  },
};

Sorting

The sorting field allows to specify the sort order for your query. The sorting field is an array of object containing:

field - the field to sort on
direction - ASC or DESC
nulls? - Optional nulls sort, NULLS_FIRST or NULLS_LAST

Single-Sort
Multi-Sort

// import { SortDirection } from '@ptc-org/nestjs-query-core';
const q: Query<MyClass> = {
  sorting: [{ field: 'title', direction: SortDirection.DESC }],
};

// import { SortDirection } from '@ptc-org/nestjs-query-core';
const q: Query<MyClass> = {
  sorting: [
    { field: 'title', direction: SortDirection.DESC },
    { field: 'age', direction: SortDirection.ASC },
  ],
};

Filter Reference

The filter option supports the following field comparisons.

The following examples show an approximation of the SQL that will be generated. The ORM will take care of handling the dialect specifics

Common Comparisons

All types support the following comparisons.

is - Check is a field is null, true or false.

// title IS NULL
{
  title: {
    is: null
  }
}
// completed IS TRUE
{
  completed: {
    is: true
  }
}
// completed IS false
{
  completed: {
    is: false
  }
}

isNot - Check is a field is not null, true or false.

// title IS NOT NULL
{
  title: {
    isNot: null
  }
}
// completed IS NOT TRUE
{
  completed: {
    isNot: true
  }
}
// completed IS NOT false
{
  completed: {
    isNot: false
  }
}

neq - field is not equal to a value.

// title != 'foo'
{
  title: {
    neq: 'foo'
  }
}

gt - field is greater than a value.

// title > 'foo'
{
  title: {
    gt: 'foo'
  }
}

gte - field is greater than or equal to a value.

// title >= 'foo'
{
  title: {
    gte: 'foo'
  }
}

lt - field is less than a value.

// title < 'foo'
{
  title: {
    lt: 'foo'
  }
}

lte - field is less than or equal to a value.

// title <= 'foo'
{
  title: {
    lte: 'foo'
  }
}

in - field is in a list of values.

// title IN ('foo', 'bar', 'baz')
{ title: { in: ['foo', 'bar', 'baz'] } }

notIn - field is not in a list of values.

// title NOT IN ('foo', 'bar', 'baz')
{
  title: {
    notIn: ['foo', 'bar', 'baz']
  }
}

String Comparisons

like - field is like a value (case sensitive).

// title LIKE 'Foo%'
{
  title: {
    like: 'Foo%'
  }
}

notLike - field is not like a value (case sensitive).

// title NOT LIKE 'Foo%'
{
  title: {
    notLike: 'Foo%'
  }
}

iLike - field is like a value (case insensitive).

// title ILIKE 'Foo%'
{
  title: {
    iLike: 'Foo%'
  }
}

notILike - field is not like a value (case insensitive).

// title NOT ILIKE 'Foo%'
{
  title: {
    notILike: 'Foo%'
  }
}

Edit this page

Introduction

Concepts

Persistence

Utilities

Project

Migration Guides

Filtering

Simple

Multiple Fields

Multiple Comparisons on a single field.

And/Or

Paging

Sorting

Filter Reference

Common Comparisons

String Comparisons

​Filtering​

​Simple​

​Multiple Fields​

​Multiple Comparisons on a single field.​

​And/Or​

​Paging​

​Sorting​

​Filter Reference​

​Common Comparisons​

​String Comparisons​

Filtering

Simple

Multiple Fields

Multiple Comparisons on a single field.

And/Or

Paging

Sorting

Filter Reference

Common Comparisons

String Comparisons