@zola_do/collection-query

TypeORM query builder for filter, sort, paginate, and cursor-based operations on entity collections.

Overview

@zola_do/collection-query provides a powerful query builder that transforms URL query parameters into TypeORM SelectQueryBuilder calls. It supports:

• Filtering with 21 operators (equals, between, in, like, etc.)
• Sorting with multiple columns and null handling
• Pagination with both offset and cursor-based approaches
• Relation loading with includes, joins, and eager loading
• Fuzzy search with PostgreSQL similarity
• URL encoding for compact query strings

Installation

# Install individually
npm install @zola_do/collection-query

# Or via meta package
npm install @zola_do/nestjs-shared

Peer Dependencies

npm install typeorm class-transformer class-validator

Optional for Swagger documentation:

npm install @nestjs/swagger

Quick Start

import {
  decodeCollectionQuery,
  QueryConstructor,
  CollectionResult,
} from '@zola_do/collection-query';

@Controller('products')
export class ProductsController {
  @Get()
  async findAll(@Query('q') q?: string) {
    const query = decodeCollectionQuery(q);
    const repository = this.productRepository;

    const dataQuery = QueryConstructor.constructQuery<Product>(
      repository,
      query,
    );
    const result = await dataQuery.getManyAndCount();

    return {
      total: result[1],
      items: result[0],
    };
  }
}

Query String Format

The q parameter uses a compact encoding format with these separators:

| Part | Separator | Description | | ---------- | ----------------------------- | ----------------------------------------- | | Where Item | _:=_: | Separates column, operator, and value | | Where OR | _, | Separates items within a group (OR logic) | | Where AND | _\| | Separates filter groups (AND logic) | | Order | , between items, : inside | col:desc,col2:asc |

Query Parameters

| Key | Name | Description | Example | | ---- | ---------------- | ----------------------------------- | --------------------- | | s | Select | Columns to select (comma-separated) | s=id,name,price | | w | Where | Filter conditions | w=status_:=_:active | | t | Take | Number of records to return (limit) | t=20 | | sk | Skip | Records to skip (offset) | sk=0 | | ct | Cursor Token | Opaque cursor for pagination | ct=eyJway... | | cd | Cursor Direction | n (next) or p (prev) | cd=n | | o | Order | Sort column and direction | o=createdAt:desc | | i | Includes | Relations to eager-load | i=category,vendor | | c | Count | Return count only | c=true |

Example Queries

# Basic filter
GET /products?q=w=status_:=_:active

# With pagination
GET /products?q=w=status_:=_:active&t=10&sk=0

# With sorting
GET /products?q=w=status_:=_:active&t=10&o=createdAt:desc

# With includes
GET /products?q=i=category,vendor&o=createdAt:desc

# ILIKE search (OR within group) - use % for wildcards
GET /products?q=w=organizationName_:Ilike_:t_,organizationShortName_:Ilike_:t

# Multiple filters (AND between groups)
GET /products?q=w=status_:=_:active_|category_:=_:123

# Combined AND + OR
GET /products?q=w=status_:=_:active_,status_:=_:inactive_|name_:=_:test
# SQL: WHERE (status = 'active' OR status = 'inactive') AND name = 'test'

# Cursor pagination
GET /products?q=t=10&ct=eyJway...&cd=n

Filter Operators Reference

The w parameter uses these separators:

• _: separates column, operator, and value
• _, separates items within a group (OR logic)
• _| separates filter groups (AND logic)

Available Operators

| Operator | Symbol | Description | Example | | ------------------------ | --------------- | -------------------------------- | ------------------------------------- | | EqualTo | = | Exact match | w=id_:=_:123 | | NotEqualTo | != | Not equal | w=status_:=!:=inactive | | GreaterThan | > | Greater than | w=price__:>_100 | | GreaterThanOrEqualTo | >= | Greater than or equal | w=price__:>=_100 | | LessThan | < | Less than | w=price__:<_100 | | LessThanOrEqualTo | <= | Less than or equal | w=price__:<=_100 | | Like | LIKE | Pattern match (case-sensitive) | w=name_:LIKE_:pro% | | ILike | ILIKE | Pattern match (case-insensitive) | w=name_:ILIKE_:pro% | | In | IN | Value in list | w=id_:IN_:1,2,3 | | NotIn | NotIn | Value not in list | w=id_:NotIn_:1,2,3 | | Between | BETWEEN | Range (inclusive) | w=price_:BETWEEN_:10,100 | | IsNull | IsNull | Is NULL | w=deletedAt_:IsNull_:true | | IsNotNull | IsNotNull | Is not NULL | w=deletedAt_:IsNotNull_:true | | NotNull | NotNull | Is not NULL | w=deletedAt_:NotNull_:true | | Any | ANY | Array contains element | w=tags_:ANY_:featured | | ArrayContains | ArrayContains | Array contains value | w=permissions_:ArrayContains_:admin | | ArrayFilter | ArrayFilter | Filter array elements | w=items_:ArrayFilter_:active | | All | All | Array contains all | w=roles_:All_:admin,user |

Operator Examples

// Equal to
{ column: 'status', value: 'active', operator: FilterOperators.EqualTo }
// SQL: WHERE status = 'active'

// Between
{ column: 'price', value: [10, 100], operator: FilterOperators.Between }
// SQL: WHERE price BETWEEN 10 AND 100

// In
{ column: 'id', value: [1, 2, 3], operator: FilterOperators.In }
// SQL: WHERE id IN (1, 2, 3)

// Like
{ column: 'name', value: 'pro%', operator: FilterOperators.Like }
// SQL: WHERE name LIKE 'pro%'

// IsNull
{ column: 'deletedAt', value: true, operator: FilterOperators.IsNull }
// SQL: WHERE deletedAt IS NULL

// Array contains
{ column: 'permissions', value: 'admin', operator: FilterOperators.ArrayContains }
// SQL: WHERE permissions @> ARRAY['admin']

Complex Filters

// OR within group
where: [
  [
    { column: 'status', value: 'active', operator: FilterOperators.EqualTo },
    { column: 'featured', value: true, operator: FilterOperators.EqualTo },
  ],
];

// SQL: WHERE (status = 'active' OR featured = true)

// AND between groups
where: [
  [{ column: 'status', value: 'active', operator: FilterOperators.EqualTo }],
  [{ column: 'categoryId', value: '123', operator: FilterOperators.EqualTo }],
];

// SQL: WHERE (status = 'active') AND (categoryId = '123')

Pagination

Offset Pagination

const query = decodeCollectionQuery('t=10&sk=20&o=createdAt:desc');
// take: 10, skip: 20, orderBy: [{ column: 'createdAt', direction: 'DESC' }]

Cursor Pagination

Cursor pagination is recommended for:

• Live data (feeds, activity logs)
• Deep pagination
• Data that changes frequently

// First page
const query1 = decodeCollectionQuery('t=10&o=createdAt:DESC');
// Set stableSortColumn for best results
query1.stableSortColumn = 'id';

// Subsequent pages
const query2 = decodeCollectionQuery('t=10&ct=<cursor>&cd=n');

// Response includes cursors
interface CollectionResult<T> {
  total: number;
  items: T[];
  nextCursor?: string;
  prevCursor?: string;
  hasNextPage?: boolean;
}

Cursor token structure (opaque):

interface CursorTokenPayload {
  version: 1;
  orderColumn: string;
  orderDirection: 'ASC' | 'DESC';
  orderValue: any;
  stableColumn: string;
  stableValue: any;
}

Sorting

Basic Sorting

// Single column
query.orderBy = [{ column: 'createdAt', direction: 'DESC' }];

// Multiple columns
query.orderBy = [
  { column: 'category', direction: 'ASC' },
  { column: 'createdAt', direction: 'DESC' },
];

Null Handling

query.orderBy = [
  {
    column: 'deletedAt',
    direction: 'ASC',
    nulls: 'NULLS LAST', // or 'NULLS FIRST'
  },
];

Relation Loading

Includes

// Simple includes
query.includes = ['category', 'vendor'];

// Nested includes
query.includes = ['category.parent', 'vendor.address'];

IncludeAndSelect

query.includeAndSelect = [
  {
    name: 'category',
    select: ['id', 'name'], // Select specific fields
  },
];

Left Join And Map One

query.leftJoinAndMapOne = [
  {
    mapToProperty: 'vendor',
    property: 'vendors',
    condition: 'vendors.status = :status',
    parameters: { status: 'Approved' },
  },
];

Fuzzy Search

For PostgreSQL similarity search:

query.fuzzySearch = [
  {
    columns: ['name', 'description'],
    value: 'search term',
  },
];
// Requires: CREATE EXTENSION IF NOT EXISTS pg_trgm;

Building Queries

Basic Query Construction

import { QueryConstructor, CollectionQuery } from '@zola_do/collection-query';

const query = new CollectionQuery();
query.take = 10;
query.skip = 0;
query.where = [[{ column: 'status', value: 'active', operator: '=' }]];
query.orderBy = [{ column: 'createdAt', direction: 'DESC' }];

const dataQuery = QueryConstructor.constructQuery<Product>(repository, query);
const [items, total] = await dataQuery.getManyAndCount();

return { total, items };

With Relations

const query = decodeCollectionQuery(
  'w=status_:=_:active&i=category,vendor&o=name:asc',
);
const dataQuery = QueryConstructor.constructQuery<Product>(repository, query);

Programmatic Query Building

import { CollectionQuery, FilterOperators } from '@zola_do/collection-query';

const query = new CollectionQuery();

// Add filters
query.where = [
  // Group 1: status AND category
  [
    { column: 'status', value: 'active', operator: FilterOperators.EqualTo },
    {
      column: 'categoryId',
      value: categoryId,
      operator: FilterOperators.EqualTo,
    },
  ],
  // Group 2: OR price conditions
  [
    { column: 'price', value: [0, 50], operator: FilterOperators.Between },
    { column: 'featured', value: true, operator: FilterOperators.EqualTo },
  ],
];

// Add sorting
query.orderBy = [
  { column: 'featured', direction: 'DESC' },
  { column: 'createdAt', direction: 'DESC', nulls: 'NULLS LAST' },
];

// Add pagination
query.take = 20;
query.skip = 0;

// Add includes
query.includes = ['category', 'vendor'];

API Reference

Functions

decodeCollectionQuery(q?: string): CollectionQuery

Parses a URL query string into a CollectionQuery object.

const query = decodeCollectionQuery(
  'w=status_:=_:active&t=10&o=createdAt:desc',
);
// Returns: CollectionQuery instance

encodeCollectionQuery(query: CollectionQuery): string

Serializes a CollectionQuery back to a URL string.

const queryString = encodeCollectionQuery(query);
// Returns: 'w=status_:=_:active&t=10&o=createdAt:desc'

Classes

QueryConstructor

Static class for building TypeORM queries.

QueryConstructor.constructQuery<T>(
  repository: Repository<T>,
  query: CollectionQuery,
  alias?: string
): SelectQueryBuilder<T>

CollectionQuery

Query parameters container.

Properties:

| Property | Type | Description | | ---------------------- | -------------------------------- | --------------------------------- | | select | string[] | Columns to select | | where | Where[][] | Filter conditions | | take | number | Limit results | | skip | number | Offset | | cursor | string | Cursor token | | cursorDirection | 'n' \| 'p' \| 'next' \| 'prev' | Pagination direction | | orderBy | Order[] | Sort columns | | includes | string[] | Relations to load | | includeAndSelect | IncludeSelect[] | Specific relation fields | | leftJoinAndMapOne | any[] | Custom joins | | groupBy | string[] | Group by columns | | having | Where[][] | Having conditions | | count | boolean | Count only | | fuzzySearch | Search[] | Fuzzy search columns | | search | Search[] | Exact search columns | | allowedFilterColumns | string[] | Whitelist filter columns | | allowedSortColumns | string[] | Whitelist sort columns | | stableSortColumn | string | Stable sort for cursor pagination | | maxIncludeDepth | number | Max relation depth |

Types

interface CollectionResult<T> {
  total: number;
  items: T[];
  nextCursor?: string;
  prevCursor?: string;
  hasNextPage?: boolean;
}

interface Where {
  column: string;
  value: any;
  operator: string;
}

interface Order {
  column: string;
  direction?: 'ASC' | 'DESC';
  nulls?: 'NULLS FIRST' | 'NULLS LAST';
}

interface Search {
  columns: string[];
  value: string;
}

Recommended Imports

Subpath imports are recommended for tree-shaking:

import { FilterOperators } from '@zola_do/collection-query/filter-operators';
import { CollectionQuery } from '@zola_do/collection-query/query';
import { QueryConstructor } from '@zola_do/collection-query/query-constructor';
import {
  decodeCollectionQuery,
  encodeCollectionQuery,
} from '@zola_do/collection-query/query-mapper';

Root import is supported for backward compatibility:

import { FilterOperators, CollectionQuery } from '@zola_do/collection-query';

Troubleshooting

Common Issues

Q: Why are my null filters not working?

// ❌ Wrong
{ column: 'deletedAt', value: null, operator: FilterOperators.IsNull }

// ✅ Correct
{ column: 'deletedAt', value: true, operator: FilterOperators.IsNull }

Q: How do I handle empty values in IN clauses?

// Empty array returns no results (safe)
{ column: 'id', value: [], operator: FilterOperators.In }

Q: Cursor pagination is unstable?

// Always set a stable sort column
query.stableSortColumn = 'id';
query.orderBy = [{ column: 'createdAt', direction: 'DESC' }];

Related Packages

• @zola_do/crud — Uses collection-query for list endpoints
• @zola_do/typeorm — Database configuration

License

ISC

Community

• Contributing
• Code of Conduct
• Security Policy