Filtering

Basics

You can add filter criteria to your query by calling the where method.

Actor.findMany().where({
firstName: { equal: 'Will' },
});

Filtering by multiple fields is just as simple

Actor.findMany().where({
firstName: { equal: 'Will' },
lastName: { equal: 'Smith' },
});

Operators

Sqlmancer exposes a number of type-specific operators. When using custom scalars, the type you map your custom scalar to will determine the available operators.

Equality operators

Operators: equal and notEqual Available on the following types: Boolean, Int, Float, String, ID, Enum, Date, JSON, List<Any>

Actor.findMany().where({
firstName: { notEqual: 'Joaquin' },
});
tip

Passing null to either equal or notEqual will result in the underlying predicate being "IS NULL" or "IS NOT NULL", respectively, as opposed to "= NULL" or "!= NULL".

Greater/less than operators

Operators: greaterThan, greaterThanOrEqual, lessThan, and lessThanOrEqual Available on the following types: Int, Float, String, ID, Date

Film.findMany().where({
stars: { greaterThanOrEqual: 3.5 },
});

Pattern matching operators

Operators: like, notLike, iLike, and notILike Available on the following types: String

Film.findMany().where({
title: { iLike: '%Dark Knight%' },
});

Note: iLike and notILike are only available in PostgreSQL.

List membership operators

Operators: in and notIn Available on the following types: Int, Float, String, ID, Enum, Date

Actor.findMany().where({
firstName: { in: ['Hailey', 'Aubrey', 'Julie'] },
});

List operators

Operators: contains, containedBy, and overlaps Available on the following types: List<Any>

Actor.findMany().where({
awards: { containedBy: ['Golden Globe', 'Oscar'] },
});

Note: List operators are only available in PostgreSQL because only PostgreSQL has an array type.

JSON operators

Operators: contains, containedBy, hasKey, hasAnyKeys, and hasAllKeys Available on the following types: JSON

Actor.findMany().where({
otherDetails: { hasKey: 'discography' },
});

Note: JSON operators are only available in PostgreSQL, MySQL and MariaDB because only those dialects have a JSON type.

Logical operators

There are three logical operators -- and, or and not. Both and and or accept an array of objects, while not accepts a single object. These operators can be combined to craft complex where conditions like this:

Film.findMany().where({
or: [
{
{ title: { equal: 'Monty Python and the Holy Grail' } },
},
{
and: [
{ stars: { greaterThan: 3 } },
{ stars: { lessThanOrEqual: 4.5 } },
{
not: {
title: { like: '%Brian%' },
},
},
],
},
]
});

Filtering by related model fields

One-to-one relationships

You can filter by any of the related model's fields.

Film.findMany().where({
language: {
name: { equal: 'French' },
},
});

One-to-many and many-to-many relationships

You can filter by any of the related model's fields. A row will be returned if any of the related models satisfy the conditon.

Film.findMany().where({
actors: {
lastName: { equal: 'Glover' },
},
});

In order to get results only when all of the related models satisfy the condition, we need to use the inverse of the condition and use the not operator:

Film.findMany().where({
not: {
actors: {
lastName: { notEqual: 'Glover' },
},
},
});

In order to get results only when none of the related models satisfy the condition, we also use the not operator but we don't inverse the condition:

Film.findMany().where({
not: {
actors: {
lastName: { equal: 'Glover' },
},
},
});

In addition to related model fields, it's also possible to filter by aggregate values. For example, by count:

Language.findMany().where({
films: { count: { greaterThan: 100 } },
});

or another aggregate value like sum, avg, min or max:

Language.findMany().where({
films: { avg: { rentalCost: { greaterThan: 5.5 } } },
});

The @where directive

If you're writing a resolver, it's feasible to just transform whatever arguments you expose for the field into an object that can then be passed to the where method. For example:

const where = { firstName: { equals: args.firstName } }
return Actor.findMany().resolveInfo(info).where(where)

However, you may want to support all of the functionality shown above. In this case, you can just use the @where directive to create the appropriate where argument on your field for you.

type Query {
films: [Film!]! @where
}
tip

If you use the resolveInfo method, you don't have to call the where method at all. Sqlmancer will look for an argument named where and modify your query builder appropriately.

If the type of your field is not the model you want to use to generate the where argument, you can specify the model to be used.

type Mutation {
deleteFilm: DeleteFilmPayload! @where(model: "Film")
}
tip

You can also use the @many directive instead of applying the @where, @orderBy, @limit and @offset directives separately.

Adding on to a query builder

Using the resolveInfo method will modify your query builder based on the provided where argument. However, you may want to constrain your query even further. For example, you may want to limit the results to only match some value in your context, like the logged-in user's ID. You can utilize the mergeWhere method to add additional conditions to your query.

Rental.findMany()
.resolveInfo(info)
.mergeWhere({ userId: { equal: context.user.id } })

Unlike the where method, which replaces any previously set conditions, mergeWhere will merge the new conditions into the existing ones. Note that the order matters here -- resolveInfo should be called first, otherwise it will override the values added with mergeWhere.