Query Helpers

PostgreSQL JSONB query helpers for TypeORM -- filter, search, and sort by translated values using SelectQueryBuilder.

@nestbolt/translatable provides a set of query helper functions for filtering, searching, and sorting by translated values in PostgreSQL jsonb columns. These functions work with TypeORM's SelectQueryBuilder and generate efficient PostgreSQL JSONB operator expressions.

Overview

All query helpers accept a SelectQueryBuilder instance and return it for chaining. They use the ->> PostgreSQL operator to extract text values from JSONB objects.

import {
  whereTranslation,
  whereTranslationLike,
  whereLocale,
  whereLocales,
  orderByTranslation,
} from "@nestbolt/translatable";

whereTranslation

Filter rows where a translatable field has an exact value in a specific locale.

const qb = repo.createQueryBuilder("product");

whereTranslation(qb, "name", "en", "Laptop");

const results = await qb.getMany();
// → Products where name->>'en' = 'Laptop'

Signature:

whereTranslation<T extends ObjectLiteral>(
  qb: SelectQueryBuilder<T>,
  column: string,
  locale: string,
  value: string,
  alias?: string,
): SelectQueryBuilder<T>

Parameter	Type	Default	Description
`qb`	`SelectQueryBuilder<T>`	--	The query builder instance.
`column`	`string`	--	The translatable column name (e.g., `"name"`).
`locale`	`string`	--	The locale to filter by (e.g., `"en"`).
`value`	`string`	--	The exact value to match.
`alias`	`string`	`qb.alias`	The table alias. Defaults to the query builder's primary alias.

Generated SQL:

"product"."name"->>'en' = :name_en_val

The parameter name is auto-generated from the column and locale to avoid collisions when combining multiple filters.

whereTranslationLike

Filter rows where a translatable field matches a pattern (case-insensitive) in a specific locale. Uses PostgreSQL's ILIKE operator.

const qb = repo.createQueryBuilder("product");

whereTranslationLike(qb, "name", "en", "%laptop%");

const results = await qb.getMany();
// → Products where name->>'en' ILIKE '%laptop%'

Signature:

whereTranslationLike<T extends ObjectLiteral>(
  qb: SelectQueryBuilder<T>,
  column: string,
  locale: string,
  pattern: string,
  alias?: string,
): SelectQueryBuilder<T>

Parameter	Type	Default	Description
`qb`	`SelectQueryBuilder<T>`	--	The query builder instance.
`column`	`string`	--	The translatable column name.
`locale`	`string`	--	The locale to search in.
`pattern`	`string`	--	The ILIKE pattern (use `%` as wildcard).
`alias`	`string`	`qb.alias`	The table alias.

Generated SQL:

"product"."name"->>'en' ILIKE :name_en_like

The ILIKE operator is case-insensitive, making this suitable for search functionality:

// Search for products containing "lap" in their English name
whereTranslationLike(qb, "name", "en", "%lap%");

// Search for products starting with "Wire"
whereTranslationLike(qb, "name", "en", "Wire%");

whereLocale

Filter rows that have a translation in a specific locale for a given field. This checks for the existence of a key in the JSONB object, regardless of the value.

const qb = repo.createQueryBuilder("product");

whereLocale(qb, "name", "ar");

const results = await qb.getMany();
// → Products that have an Arabic translation for name

Signature:

whereLocale<T extends ObjectLiteral>(
  qb: SelectQueryBuilder<T>,
  column: string,
  locale: string,
  alias?: string,
): SelectQueryBuilder<T>

Parameter	Type	Default	Description
`qb`	`SelectQueryBuilder<T>`	--	The query builder instance.
`column`	`string`	--	The translatable column name.
`locale`	`string`	--	The locale to check for existence.
`alias`	`string`	`qb.alias`	The table alias.

Generated SQL:

"product"."name"->>'ar' IS NOT NULL

whereLocales

Filter rows that have a translation in any of the specified locales for a given field. The conditions are joined with OR.

const qb = repo.createQueryBuilder("product");

whereLocales(qb, "name", ["en", "fr"]);

const results = await qb.getMany();
// → Products that have an English OR French translation for name

Signature:

whereLocales<T extends ObjectLiteral>(
  qb: SelectQueryBuilder<T>,
  column: string,
  locales: string[],
  alias?: string,
): SelectQueryBuilder<T>

Parameter	Type	Default	Description
`qb`	`SelectQueryBuilder<T>`	--	The query builder instance.
`column`	`string`	--	The translatable column name.
`locales`	`string[]`	--	The locales to check (any match).
`alias`	`string`	`qb.alias`	The table alias.

Generated SQL:

("product"."name"->>'en' IS NOT NULL OR "product"."name"->>'fr' IS NOT NULL)

orderByTranslation

Order results by the value of a translatable field in a specific locale.

const qb = repo.createQueryBuilder("product");

orderByTranslation(qb, "name", "en", "ASC");

const results = await qb.getMany();
// → Products sorted alphabetically by their English name

Signature:

orderByTranslation<T extends ObjectLiteral>(
  qb: SelectQueryBuilder<T>,
  column: string,
  locale: string,
  order?: "ASC" | "DESC",
  alias?: string,
): SelectQueryBuilder<T>

Parameter	Type	Default	Description
`qb`	`SelectQueryBuilder<T>`	--	The query builder instance.
`column`	`string`	--	The translatable column name.
`locale`	`string`	--	The locale to sort by.
`order`	`"ASC" \| "DESC"`	`"ASC"`	The sort direction.
`alias`	`string`	`qb.alias`	The table alias.

Generated SQL:

ORDER BY "product"."name"->>'en' ASC

Note that this uses addOrderBy internally, so it can be chained with other order clauses.

Combining Helpers

All helpers use andWhere (or addOrderBy), so they can be chained together to build complex queries:

const qb = repo.createQueryBuilder("product");

// Find products with an Arabic name containing "حاسوب",
// that also have a French translation,
// sorted by English name
whereTranslationLike(qb, "name", "ar", "%حاسوب%");
whereLocale(qb, "name", "fr");
orderByTranslation(qb, "name", "en", "ASC");

const results = await qb.getMany();

Using with Custom Aliases

When working with joins or subqueries, you may need to specify a custom table alias:

const qb = repo
  .createQueryBuilder("p")
  .leftJoinAndSelect("p.category", "cat");

// Use the "p" alias explicitly
whereTranslation(qb, "name", "en", "Laptop", "p");

// Filter on the joined table (if category is also translatable)
whereLocale(qb, "name", "ar", "cat");

const results = await qb.getMany();

If the alias parameter is omitted, the query builder's primary alias (qb.alias) is used.

Example: Locale-Aware Search Endpoint

A practical example combining query helpers with the middleware locale:

@Injectable()
export class ProductService {
  constructor(
    @InjectRepository(Product)
    private readonly repo: Repository<Product>,
    private readonly translatableService: TranslatableService,
  ) {}

  async search(query: string, locale?: string) {
    const searchLocale = locale ?? this.translatableService.getLocale();

    const qb = this.repo.createQueryBuilder("product");

    // Search by name in the current locale
    whereTranslationLike(qb, "name", searchLocale, `%${query}%`);

    // Order by name in the current locale
    orderByTranslation(qb, "name", searchLocale, "ASC");

    return qb.getMany();
  }

  async findByLocale(locale: string) {
    const qb = this.repo.createQueryBuilder("product");

    // Only return products that have translations in this locale
    whereLocale(qb, "name", locale);
    whereLocale(qb, "description", locale);

    orderByTranslation(qb, "name", locale, "ASC");

    return qb.getMany();
  }
}

On this page