Relation Filtering

The @Relations decorator lets you delegate input keys to a related entity's filter. When those keys appear in the input, the runner creates a join via the adapter and applies the related filter's methods on the joined query.

Basic usage

import { Injectable } from '@nestjs/common';
import { Filterable, FilterFor, Relations } from '@dudousxd/nestjs-filter';
import { MikroOrmFilter } from '@dudousxd/nestjs-filter-mikro-orm';
import { User } from './user.entity.js';
import { PostFilter } from './post.filter.js';

@Injectable()
@Filterable({ entity: User })
@Relations({
  posts: { filter: PostFilter, keys: ['postTitle', 'postStatus'] },
})
export class UserFilter extends MikroOrmFilter<User> {
  @FilterFor('name')
  applyName(value: string) {
    this.whereLike('name', value);
  }

  // postTitle and postStatus are handled by PostFilter
  // via a join on the 'posts' relation
}

When the input { name: 'Al', postTitle: 'Hello' } arrives:

1. name is dispatched to UserFilter.applyName()
2. postTitle is recognized as belonging to the posts relation
3. The adapter creates a join on posts and delegates { postTitle: 'Hello' } to PostFilter

The related filter

The related filter is a normal filter class. It receives the relation-bound keys as input:

@Injectable()
@Filterable({ entity: Post })
export class PostFilter extends MikroOrmFilter<Post> {
  @FilterFor('postTitle')
  applyTitle(value: string) {
    this.whereLike('title', value);
  }

  @FilterFor('postStatus')
  applyStatus(value: string) {
    this.$query.andWhere({ status: value });
  }
}

@Relations decorator reference

@Relations(map: RelationsMap)

Where RelationsMap is:

type RelationsMap = Record<string, RelationConfig>;

interface RelationConfig {
  filter: Type<unknown>;      // The filter class for the related entity
  keys: readonly string[];    // Input keys that belong to this relation
}

Example with multiple relations:

@Relations({
  posts: { filter: PostFilter, keys: ['postTitle', 'postStatus'] },
  company: { filter: CompanyFilter, keys: ['companyName', 'companySize'] },
})

How the adapter handles relations

MikroORM

The MikroORM adapter uses joinAndSelect() to join the relation and applies the callback filter on the same query builder:

// Internally:
parentQb.joinAndSelect(relationName, relationName);
await callback(qb); // PostFilter methods run on the same QB

TypeORM

The TypeORM adapter uses leftJoinAndSelect():

// Internally:
parentQb.leftJoinAndSelect(`${alias}.${relationName}`, relationName);
await callback(parentQb); // PostFilter methods run on the same QB

Dot-notation (auto-join)

With auto-fields enabled (the default), you can filter by related entity fields using dot-notation. No @Relations decorator needed for simple cases:

GET /users?posts.title[contains]=Hello&posts.status=published

The adapter detects posts.title, verifies posts is a relation on User via entity metadata, auto-joins, and applies the WHERE clause on the related field.

This works with all operators:

• posts.title=Hello -- equals
• posts.title[contains]=Hello -- LIKE
• posts.createdAt[gte]=2024-01-01 -- >=
• posts.status[in]=draft,published -- IN

@Injectable()
@Filterable({ entity: User })
export class UserFilter extends MikroOrmFilter<User> {
  // No @FilterFor or @Relations needed for dot-notation.
  // GET /users?posts.title[contains]=Hello  →  auto-joins posts, WHERE posts.title LIKE '%Hello%'
}

Projecting a related column with distinct

distinct takes the same dot-notation, which is what a filter dropdown over a relation column needs -- "which authors appear in these posts?":

GET /posts?distinct=author.name

filterQuery<Post>().where('status', 'equals', 'published').distinct('author.name');

The relation is joined and its column projected, so the rows come back keyed by the path you asked for -- for a single-column projection the field name is the contract:

{ "data": [{ "author.name": "Ada" }, { "author.name": "Grace" }], "totalCount": 2 }

Every other clause still applies, so the values are the ones actually reachable under the current filters, not the whole related table. The distinct-tuple total counts the projection as a whole, and a relation path mixes freely with root columns (distinct=status,author.name).

Aggregating a to-many relation

Dot-notation sort (sort=posts.title) orders the list by a column of a to-one relation via an auto-join. To sort or filter by an aggregate of a to-many collection instead (e.g. how many posts a user has, or the sum of their posts' views), you don't need a join, and you don't need to hand-write a computed field either — the runtime auto-discovers virtual aggregate sub-paths straight from your ORM's relation metadata:

• <relation>.$count — number of related rows.
• <relation>.$sum.<column> / .$avg.<column> / .$min.<column> / .$max.<column> — one aggregate per numeric column on the related entity.

These work as sort keys and filter fields, exactly like a real column:

GET /users?sort=-posts.$count                # users ordered by how many posts they have, most first
GET /users?posts.$sum.views[gt]=100           # only users whose posts' views add up to more than 100

Or through the typed client:

filterQuery()
  .sortDesc('posts.$count')
  .where('posts.$sum.views', 'gt', 100)
  .build();

This works for both to-many cardinalities — @OneToMany and @ManyToMany:

GET /users?sort=-tags.$count                  # order users by how many tags they have (many-to-many)

No declaration is required beyond the relation itself: with autoFields: true (the default) and no allowed list, every @OneToMany/@ManyToMany relation on the entity gets .$count plus a .$sum/.$avg/.$min/.$max set for each of its numeric child columns. Exclude a relation from aggregation the same way you'd exclude any other field — add its name to @Filterable.blocked.

Empty collections

A row with no related children still participates in sort/filter — the aggregate just resolves to the identity value for that function, not NULL across the board:

So posts.$avg.views[isNull]=true matches users with zero posts, while posts.$count[equals]=0 is the equivalent check phrased as a count.

How it compiles

Every aggregate path — one-to-many or many-to-many — compiles to a single correlated scalar subquery, never a JOIN + GROUP BY on the outer query. That's what keeps pagination correct: root rows are never multiplied, no matter how many (or how few) children a row has. For a many-to-many relation, the subquery additionally joins through the pivot table to reach the child column, but that join lives entirely inside the parenthesized scalar expression and can't affect the outer row count either way. Implemented identically on both the MikroORM and TypeORM adapters.

Manual relation filtering

If you need more control, you can handle relation filtering manually in your filter methods instead of using @Relations:

@FilterFor('postTitle')
applyPostTitle(value: string) {
  // MikroORM example
  this.$query.joinAndSelect('posts', 'posts');
  this.$query.andWhere({ 'posts.title': { $like: `%${value}%` } });
}

Helper methods for LIKE queries

Both MikroOrmFilter and TypeOrmFilter provide convenience methods:

All values are escaped via escapeLike() to prevent LIKE wildcard injection.