Filter by relation in Drizzle ORM

To filter by relation with Drizzle in this package, use the relation name as a filter key: { state: { country: { code: "IN" } } } follows a path through relations to any depth, and { states: { none: { name: "Kerala" } } } quantifies a collection with some, none or every. Only the top-level belongs-to is joined. Everything else compiles to an IN, EXISTS or NOT EXISTS subquery, so parent rows are never multiplied and total stays right.

// Cities in India: city -> state -> country
await this.cities.findAll({ state: { country: { code: "IN" } } });

// Countries with no state called Kerala
await this.countries.findAll({ states: { none: { name: "Kerala" } } });

How do I filter through more than one relation?Copy Anchor Link

Nest the relation names, as declared in each relation's own relations config. A belongs-to chain from cities to countries looks like this:

relations: {
  state: {
    table: states,
    localKey: "state_id",
    relations: {
      country: { table: countries, localKey: "country_id" },
    },
  },
}

await this.cities.findAll({ state: { country: { code: "IN" } } });
await this.cities.count({ state: { name: "Kerala", country: { code: "IN" } } });

The top-level belongs-to (state) is joined with a LEFT JOIN. Each deeper level is a correlated EXISTS on its own table alias:

select ... from cities
left join states on cities.state_id = states.id
where exists (
  select 1 from countries crud_sq1
  where crud_sq1.id = states.country_id and lower(crud_sq1.code) = lower($1)
)

Because each subquery gets its own alias, a relation back to a table already in the query (a self relation) still resolves. With soft delete on, a soft-deleted related row never matches, at any level.

How do I use some, none and every?Copy Anchor Link

On a has-many or many-to-many relation, the value is either a filter (meaning some) or an object whose keys are all quantifiers:

Rules worth knowing:

• Several quantifiers in one object are ANDed: { states: { some: { name: "Kerala" }, none: { code: "XX" } } }.
• An empty plain filter, { states: {} }, adds nothing. Write { states: { some: {} } } to mean "has at least one".
• The object counts as quantifiers only when every key is some, none or every and the related table has no column by that name. Mixing a quantifier with a column ({ some: {...}, name: "x" }) is read as a plain filter, so some is an unknown column and the call is a 400.
• A quantifier value must be an object of column filters, otherwise 400.

What does every mean for a parent with no rows?Copy Anchor Link

It matches. every means "no related row fails the filter", which is true when there are none, as in Prisma. Add some: {} when you need at least one:

await this.countries.findAll({
  states: { some: {}, every: { name: { startsWith: "Ba" } } },
});

A related row whose filter evaluates to NULL (a NULL column, say) counts as failing, which is what the coalesce(..., false) is for. Soft-deleted related rows are ignored by all three quantifiers. An every with an empty filter adds nothing.

Do relation filters work in groups and counts?Copy Anchor Link

Yes. A relation filter, at any depth and with any quantifier, can sit inside and, or and not, and findAll, findByCursor, count and countBy all build their WHERE with the same compiler:

await this.countries.count({
  or: [{ states: { every: { name: { startsWith: "Ba" } } } }, { code: "IN" }],
});
await this.cities.count({ not: { state: { country: { code: "IN" } } } });
await this.cities.count({ state: { cities: { some: { name: "Mysuru" } } } }); // a collection under a belongs-to
await this.countries.count({ states: { cities: { some: { name: "Mysuru" } } } }); // a collection under a collection

How do I send relation filters in a query string?Copy Anchor Link

Use brackets or dots, the same as for one level. parseQuery builds the filter object for you:

GET /cities?state[country][code]=IN
GET /cities?state.country.code=IN
GET /countries?states[none][name]=Kerala
GET /countries?states[every][name][startsWith]=Ba
GET /countries?states[some]=

states[some]= with no column means "has at least one".

How do allowlists treat a relation path?Copy Anchor Link

filterableColumns names the full dotted path to the column. "state.country.code" allows the city filter above and nothing else on country. Quantifiers do not appear in the path, so "states.name" allows states[none][name] as well as states[name]. See allowlists.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link