Filtering

The filter query parameters can be used to add where clauses to your Model query. Out of the box we support filtering results by partial attribute value, exact attribute value or even if an attribute value exists in a given array of values. For anything more advanced, custom filters can be used.

By default, all filters have to be explicitly allowed using allowedFilters. This method takes an array of strings or AllowedFilter instances. An allowed filter can be partial, beginsWithStrict, endsWithStrict, exact or custom. By default, any string values passed to allowedFilters() will automatically be converted to AllowedFilter.partial() filters.

Basic Usage

// GET /users?filter[name]=john&filter[email]=gmail

const users = await User.query().allowedFilters('name', 'email');

// users will contain all users with "john" in their name AND "gmail" in their email address

You can specify multiple matching filter values by passing a comma separated list of values:

// GET /users?filter[name]=seb,freek

const users = await User.query().allowedFilters('name');

// users will contain all users that contain "seb" or "freek" in their name

By passing column name strings to allowedFilters, partial filters are automatically applied.

Disallowed filters

Finally, when trying to filter on properties than have not been explicitly allowed using allowedFilters, an InvalidFilterQuery exception will be thrown along with a list of allowed filters.

Disable InvalidFilterQuery exception

You can set in configuration file to not thrown an InvalidFilterQuery exception when a filter is not set in allowedFilter method. This does not allow using any filter, it just disables the exception.

disableInvalidFilterQueryException: true;

By default this is set to false.

Partial, beginsWithStrict and endsWithStrict filters

By default, all values passed to allowedFilters are converted to partial filters. The underlying query will be modified to use a LOWER(%value%) LIKE statement. Because this can cause missed indexes, it’s often worth considering a beginsWithStrict filter for the beginning of the value, or an endsWithStrict filter for the end of the value. These filters will use a LIKE value% statement and a LIKE %value statement respectively, instead of the default partial filter.

Exact filters

When filtering IDs, boolean values or a literal string, you’ll want to use exact filters. This way /users?filter[id]=1 won’t match all users having the digit 1 in their ID.

Exact filters can be added using AllowedFilter.exact('propertyName') in the allowedFilters() method.

import { AllowedFilter } from '@eienjs/adonisjs-api-query';

// GET /users?filter[name]=John%20Doe

const users = await User.query().allowedFilters(AllowedFilter.exact('name'));

// only users with the exact name "John Doe"

The query builder will automatically map 1, 0, true and false as boolean values and a comma separated list of values as an array:

import { AllowedFilter } from '@eienjs/adonisjs-api-query';

// GET /users?filter[id]=1,2,3,4,5&filter[admin]=true

const users = await User.query().allowedFilters(
  AllowedFilter.exact('id'),
  AllowedFilter.exact('admin'),
);

// users will contain all admin users with id 1, 2, 3, 4 or 5

Operator filters

Operator filters allow you to filter results based on different operators such as Equal, NotEqual, GreaterThan, LessThan, GreaterThanOrEqual, LessThanOrEqual and Dynamic. You can use the AllowedFilter.operator method to create operators filters.

import { AllowedFilter, FilterOperator } from '@eienjs/adonisjs-api-query';

// GET /users?filter[salary]=3000

const users = await User.query().allowedFilters(
  AllowedFilter.operator('salary', FilterOperator.GreaterThan),
);

// users will contain all users with a salary greater than 3000

You can also use dynamic operator filters, which allow you to specify the operator in the filter value:

import { AllowedFilter, FilterOperator } from '@eienjs/adonisjs-api-query';

// GET /users?filter[salary]=>3000

const users = await User.query().allowedFilters(
  AllowedFilter.operator('salary', FilterOperator.Dynamic),
);

// users will contain all users with a salary greater than 3000

Trashed filters

When using adonis-lucid-soft-deletes you can use the AllowedFilter.trashed() filter to query these models.

The FiltersTrashed filter responds to particular values:

• with: include soft-deleted records to the result set
• only: return ony ‘trashed’ records at the result set
• any other value: return only records without than are not soft-deleted in the result set

For example:

import { AllowedFilter } from '@eienjs/adonisjs-api-query';

// GET /bookings?filter[trashed]=only

const result = await Booking.query().allowedFilters(AllowedFilter.trashed());

// result will contain only soft-deleted bookings

Callback filters

If you want to define a tiny custom filter, you can use a callback filter. Using AllowedFilter.callback you can specify a callable that will be executed when the filter is requested.

The filter callback will receive the following parameters: query: ModelQueryBuilder, value: unknown, property: string. You can modify the ModelQueryBuilder object to add your own query constraints.

For example:

import { AllowedFilter } from '@eienjs/adonisjs-api-query';

const users = await User.query().allowedFilters(
  AllowedFilter.callback('hasPosts', (query) => {
    void query.has('posts');
  }),
);

Custom filters

You can specify custom filters using the AllowedFilter.custom method. Custom filters are instances of invokable classes that implement the Filter interface. The handle method will receive the current query builder instance and the filter name/value. This way you can build any query your heart desires.

For example:

import { AllowedFilter } from '@eienjs/adonisjs-api-query';
import { Filter } from '@eienjs/adonisjs-api-query/types';
import User from '#models/user';

export default class FiltersUserPermission implements Filter<typeof User> {
  public handle(
    query: ModelQueryBuilderContract<typeof User>,
    value: StrictValuesWithoutRaw,
    _property: string,
  ): void {
    void query.whereHas('permissions', (subQuery) => {
      void subQuery.where('name', value);
    });
  }
}

// In your controller for the following request:
// GET /users?filter[permission]=createPosts

const users = await User.query().allowedFilters(
  AllowedFilter.custom('permission', new FiltersUserPermission()),
);

// users will contain all users that have the permission named "createPosts"

Filter aliases

It can be useful to specify an alias for a filter to avoid exposing database column names. For example, your users table might have a userPassportFullName column, which is a horrible name for a filter. Using aliases you can specify a new, shorter name for this filter:

// GET /users?filter[name]=John

await User.query().allowedFilters(AllowedFilter.exact('user', 'userPassportFullName'));

Ignored filters values

You can specify a set of ignored values for every filter. This allows you to not apply a filter when these values are submitted.

await User.query().allowedFilters(AllowedFilter.exact('name').ignore(null));

The ignore() method takes one or more values, where each may be an array of ignored values. Each of the following calls are valid:

• ignore('shouldBeIgnored')
• ignore(null, '-1')
• ignore([null, 'ignoreMe', 'alsoIgnored'])

Given an array of values to filter for, only the subset of non-ignored values get passed to the filter. If all values are ignored, the filter does not get applied.

// GET /users?filter[name]=forbidden,John%20Doe

await User.query().allowedFilters(AllowedFilter.exact('name').ignore('forbidden'));
// Retuns only users where name matches "John Doe"

// GET /users?filter[name]=ignored,ignoredToo

await User.query().allowedFilters(AllowedFilter.exact('name').ignore('ignored', 'ignoredToo'));
// Filter does not get applied because all requested values are ignored.

Default Filter Values

You can specify a default value for a filter if a value for ther filter was not present on the request. This is especially useful for boolean filters.

await User.query().allowedFilters(
  AllowedFilter.exact('name').setDefault('Joe'),
  AllowedFilter.partial('email').setDefault(null),
);

Nullable Filter

You can mark a filter nullable if you want to retrieve entries whose filtered value is null. This way you can apply the filter with an empty value, as shown in the example.

// GET /users?filter[name]=&filter[permission]=

await User.query().allowedFilters(AllowedFilter.exact('name').nullable());