Where filter

REST API

In the first form below, the condition is equivalence, that is, it checks whether property equals value. The second form below is for all other conditions.

filter[where][property]=value

filter[where][property][op]=value

Where:

• property is the name of a property (field) in the model being queried.
• value is a literal value.
• op is one of the operators listed below.

For example, if there is a Car model with an odo property, the following query finds instances where the odo is greater than 5000:

/cars?filter[where][odo][gt]=5000

For example, here is a query to find cars with odo is less than 30,000:

/cars?filter[where][odo][lt]=30000

You can also use stringified JSON format in a REST query. The above example can be written as:

/cars?filter={"where":{"odo":{"lt":30000}}}

Filter limit

You can encode the large filter object as “stringified JSON”:

http://localhost:3000/api/Books
?filter={"where":{"or":[{"id":1},{"id":2},...,{"id":20"},{"id":21}]}}

Node.js API

Where clause for queries

For query methods such as find() or findOne(), use the first form below to check equivalence, that is, whether property equals value. Use the second form below for all other conditions.

{
  where: {
    property: value;
  }
}

{
  where: {
    property: {
      op: value;
    }
  }
}

Where:

• property is the name of a property (field) in the model being queried.
• value is a literal value.
• op is one of the operators listed below.

await productRepository.find({where: {size: 'large'}});

/products?filter[where][size]=large

Where clause for other methods

In the first form below, the condition is equivalence, that is, it checks whether property equals value. The second form is for all other conditions.

{
  property: value;
}

{
  property: {
    op: value;
  }
}

Where:

• property is the name of a property (field) in the model being queried.
• value is a literal value.
• op is one of the operators listed below.

For example, a where clause in a call to a model’s updateAll() method. Note the lack of { where : ... } in the argument.

await orderRepository.updateAll({id: 123}, {customerId: null});

More examples, this time in a call to deleteAll():

await orderRepository.deleteAll({customerId: 99});

To delete all records where the cost property is greater than 100:

await productRepositor.deleteAll({cost: {gt: 100}});

Default scope with where filters

Adding a scope to a model definition) automatically adds a method to model. LoopBack will apply the filter whenever a model is created, updated, or queried.

Operators

This table describes the operators available in “where” filters. See Examples below.

AND and OR operators

Use the AND and OR operators to create compound logical filters based on simple where filter conditions, using the following syntax.

{where: {<and|or>: [condition1, condition2, ...]}}

[where][<and|or>][0]condition1&[where][<and|or>]condition2...

Where condition1 and condition2 are a filter conditions.

See examples below.

Regular expressions

You can use regular expressions in a where filter, with the following syntax. You can use a regular expression in a where clause for updates and deletes, as well as queries.

Essentially, regexp is just like an operator in which you provide a regular expression value as the comparison value.

{where: {property: {regexp: <expression>}}}

Where <expression> can be a:

• String defining a regular expression (for example, '^foo' ).
• Regular expression literal (for example, /^foo/ ).
• Regular expression object (for example, new RegExp(/John/)).

Or, in a simpler format:

{where: {property: <expression>}}}

Where <expression> can be a:

• Regular expression literal (for example, /^foo/ ).
• Regular expression object (for example, new RegExp(/John/)).

For more information on JavaScript regular expressions, see Regular Expressions (Mozilla Developer Network).

For example, this query returns all products for which the category starts with a capital “T”:

await productRepository.find({where: {category: {regexp: '^T'}}});

Or, using the simplified form:

await productRepository.find({where: {category: /^T/}});

filter[where][property][regexp]=expression

Where:

• property is the name of a property (field) in the model being queried.
• expression is the JavaScript regular expression string. See Regular Expressions (Mozilla Developer Network).

A regular expression value can also include one or more flags. For example, append /i to the regular expression to perform a case-insensitive match.

The following REST query returns all products for which the category starts with a capital “T”::

/api/products?filter[where][category][regexp]=^T

The following REST query returns products that start with either an uppercase “T” or lowercase “t”:

/api/products?filter[where][category][regexp]=/^t/i

Note that since the regular expression includes a flag, it is preceded by a slash (/).

Examples

Equivalence

Weapons with name M1911:

/weapons?filter[where][name]=M1911

Or stringified JSON format:

/weapons?filter={"where":{"name::"M1911"}}

Products where size is “large”:

/products?filter[where][size]=large /products?filter[where][size][eq]=large

await productRepository.find({where: {size: 'large'}});
await productRepository.find({where: {size: {eq: 'large'}}}); // full condition syntax

gt and lt

ONE_MONTH = 30 * 24 * 60 * 60 * 1000; // Month in milliseconds
await transactionRepository.find({
  where: {
    userId: user.id,
    time: {gt: Date.now() - ONE_MONTH},
  },
});

For example, the following query returns all instances of the order using a where filter that specifies a date property after (greater than) the specified date:

/orders?filter[where][date][gt]=2014-04-01T18:30:00.000Z

Or stringified JSON format:

/orders?filter={"where":{"date":{"gt":"2014-04-01T18:30:00.000Z"}}}

orderRepository.find({
  where: {
    date: {gt: new Date('2014-04-01T18:30:00.000Z')},
  },
});

The top three weapons with a range over 900 meters:

/weapons?filter[where][effectiveRange][gt]=900&filter[limit]=3

Weapons with audibleRange less than 10:

/weapons?filter[where][audibleRange][lt]=10

and / or

The following code is an example of using the “and” operator to find reviews where the title is “My Post” and content is “Hello”.

await reviewRepository.find({
  where: {and: [{title: 'My Post'}, {content: 'Hello'}]},
});

/reviews?filter[where][and][0][title]=My%20Post&filter[where][and][1][content]=Hello

Example using the “or” operator to finds reviews that either have title of “My Review” or content of “Hello”.

await reviewRepository.find({
  where: {or: [{title: 'My Review'}, {content: 'Hello'}]},
});

More complex example. The following expresses (field1= foo and field2=bar) OR field1=morefoo:

{
  or: [{and: [{field1: 'foo'}, {field2: 'bar'}]}, {field1: 'morefoo'}];
}

between

Example of between operator:

/shirts?filter[where][price][between][0]=0&filter[where][price][between][1]=7

await shirtRepository.find({where: {size: {between: [0, 7]}}});

near

The where.<field>.near filter is different from other where filters: most where filters limitthe number of records returned, whereas near orders them, making it more like a SQL order by clause. By combining it with limit, you can create a query to get, for example, the three records nearest to a given location.

For example:

/hotels?filter[where][geo][near]=153.536,-28.1&filter[limit]=3

GeoPoints can be expressed in any of the following ways:

location = new GeoPoint({lat: 42.266271, lng: -72.6700016}); // GeoPoint
location = '42.266271,-72.6700016'; // String
location = [42.266271, -72.6700016]; // Array
location = {lat: 42.266271, lng: -72.6700016}; // Object Literal

await hotelRepository.find({where: {geo: {near: location}}});

near (ordering and limiting by distance)

The near filter can take two additional properties:

• maxDistance
• unit

When maxDistance is included in the filter, near behaves more like a typical where filter, limiting results to those within a given distance to a location. By default, maxDistance measures distance in miles.

Example of finding the all hotels within two miles of a given GeoPoint:

const userLocation = new GeoPoint({
  lat: 42.266271,
  lng: -72.6700016,
});
const results = await hotelRepository.find({
  where: {
    location: {
      near: userLocation,
      maxDistance: 2,
    },
  },
});

To change the units of measurement, specify unit property to one of the following:

• kilometers
• meters
• miles
• feet
• radians
• degrees

For example, to change the query above to use kilometers instead of miles:

await hotelRepository.find({
  where: {
    location: {
      near: userLocation,
      maxDistance: 2,
      unit: 'kilometers',
    },
  },
});

like and nlike

The like and nlike (not like) operators enable you to match SQL regular expressions. The regular expression format depends on the backend data source.

Example of like operator:

await postRepository.find({where: {title: {like: 'M.-st'}}});

Example of nlike operator:

await postRepository.find({where: {title: {nlike: 'M.-XY'}}});

When using the memory connector:

await userRepository.find({where: {name: {like: '%St%'}}});
await userRepository.find({where: {name: {nlike: 'M%XY'}}});

like and nlike insensitive

const pattern = new RegExp(
  '.*' + query + '.*',
  'i',
); /* case-insensitive RegExp search */
await postRepository.find({where: {title: {like: pattern}}});

Stringified JSON format:

?filter={"where":{"title":{"like":"someth.*","options":"i"}}}

ilike and nilike

The ilike and nilike (not ilike) operators enable you to match case insensitive regular expressions. It is supported by the memory connector and Postgresql connectors.

Example of ilike operator:

await postRepository.find({where: {title: {ilike: 'm.-st'}}});

Example of nilike operator:

await postRepository.find({where: {title: {nilike: 'm.-xy'}}});

When using the memory connector:

await userRepository.find({where: {name: {ilike: '%st%'}}});
await userRepository.find({where: {name: {nilike: 's%xy'}}});

When using the PostgreSQL connector:

await userRepository.find({where: {name: {ilike: 'john%'}}});

inq

The inq operator checks whether the value of the specified property matches any of the values provided in an array. The general syntax is:

{where: { property: { inq: [val1, val2, ...]}}}

Where:

• property is the name of a property (field) in the model being queried.
• val1, val2, and so on, are literal values in an array.

Example of inq operator:

await postRepository.find({where: {id: {inq: [123, 234]}}});

/medias?filter[where][keywords][inq]=foo&filter[where][keywords][inq]=bar

Or stringified JSON format:

?filter={"where": {"keywords": {"inq": ["foo", "bar"]}}}

WhereBuilder

You can use the WhereBuilder to build and/or combine where clauses. You can build where clause with operators such as and/or, gt, etc.

For example,

import WhereBuilder from '@loopback/repository';
...
const whereBuilder = new WhereBuilder();
const where = whereBuilder
  .between('price', 99, 299)
  .and({brand: 'LoopBack'}, {discount: {lt: 20}})
  .or({instock: true})
  .build();

the filter will be built as

{
  price: {between: [99, 299]},
  and: [
    {and: [{brand: 'LoopBack'}, {discount: {lt: 20}]},
  ],
  or: [
    {instock: true},
  ]
}

Another common usage is to combine where clauses with WhereBuilder.impose. It adds a where object to the existing where filter by using the and operator. For example,

import WhereBuilder from '@loopback/repository';
...
const builder = new WhereBuilder<AnyObject>({brand: 'Toyota'});
const where = builder.impose({model: 'Prius', instock: true}).build();

the filter will be built as

{
  and: [
  { brand: 'Toyota' }, { model: 'Prius', instock: true }
  ],
}