Skip to main content

Queries

The core of nestjs-query is the Query, it is used by @ptc-org/nestjs-query-graphql, @ptc-org/nestjs-query-typeorm @ptc-org/nestjs-query-sequelize, @ptc-org/nestjs-query-mongoose and @ptc-org/nestjs-query-typegoose.

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.

note

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.

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

When using filters on relations with typeorm in combination with paging, performance can be degraded on large result sets. For more info see this issue

In short two queries will be executed:

  • The first one fetching a distinct list of primary keys with paging applied.
  • The second uses primary keys from the first query to fetch the actual records.

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
// import { SortDirection } from '@ptc-org/nestjs-query-core';

const q: Query<MyClass> = {
  sorting: [{ field: 'title', direction: SortDirection.DESC }],
};

Filter Reference​

The filter option supports the following field comparisons.

note

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%';
      }
    }