Query Presets & URL Optimization

The Problem: URL Length Limits

Complex queries with multiple filters, sorts, and field selections can result in extremely long URLs:

❌ BAD: Long, unmanageable URL
GET /resources?fields=id,reference_number,first_name,last_name,birth_date,status,category,email,phone_number&filter=status||$eq||active&filter=category||$in||premium,enterprise&filter=age||$gte||18&filter=age||$lte||65&sort=created_at:desc,last_name:asc&limit=50&page=1

Issues:

URLs exceeding 2048 characters may be truncated by browsers
Difficult to read and maintain
Error-prone when manually constructing
Poor user experience for bookmarking or sharing
Challenging to version control in frontend code

The Solution: Query Presets (Database Views)

Instead of passing long query strings, use query presets stored in the database. These presets define reusable query configurations that can be referenced by a simple name.

✅ GOOD: Clean, manageable URL
GET /resources?view=active-premium

Database Schema: `query_config_views` Table

Create a configuration table to store query presets:

CREATE TABLE query_config_views (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    view_name VARCHAR(100) UNIQUE NOT NULL,
    view_type VARCHAR(50) NOT NULL,
    description TEXT,
    entity_name VARCHAR(100) NOT NULL,
    config JSONB NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    updated_by UUID,

    CONSTRAINT chk_view_type CHECK (view_type IN ('fields', 'sort', 's', 'filter', 'complete'))
);

CREATE INDEX idx_query_config_views_entity ON query_config_views(entity_name);
CREATE INDEX idx_query_config_views_view_name ON query_config_views(view_name);
CREATE INDEX idx_query_config_views_view_type ON query_config_views(view_type);

COMMENT ON TABLE query_config_views IS 'Stores predefined query configurations for reusable views';
COMMENT ON COLUMN query_config_views.view_name IS 'Unique identifier for the view (e.g., "active-resources")';
COMMENT ON COLUMN query_config_views.view_type IS 'Type of configuration: fields, sort, s, filter, or complete';
COMMENT ON COLUMN query_config_views.entity_name IS 'Entity this view applies to (e.g., "resources", "orders")';
COMMENT ON COLUMN query_config_views.config IS 'JSON configuration for the query preset';

View Types and Examples

1. Field Selection View (`view_type: 'fields'`)

Define commonly used field selections:

{
    "view_name": "resource-summary",
    "view_type": "fields",
    "entity_name": "resources",
    "description": "Basic resource summary fields",
    "config": {
        "fields": ["id", "reference_number", "first_name", "last_name", "birth_date", "status"]
    }
}

Usage:

GET /resources?view=resource-summary

Equivalent to:

GET /resources?fields=id,reference_number,first_name,last_name,birth_date,status

2. Sorting View (`view_type: 'sort'`)

Define common sorting patterns:

{
    "view_name": "newest-first",
    "view_type": "sort",
    "entity_name": "resources",
    "description": "Sort by newest records first",
    "config": {
        "sort": "created_at:desc,reference_number:asc"
    }
}

Usage:

GET /resources?view=newest-first

3. Filter View (`view_type: 'filter'`)

Define reusable filter sets:

{
    "view_name": "active-adults",
    "view_type": "filter",
    "entity_name": "resources",
    "description": "Active resources in the standard age bracket",
    "config": {
        "filter": ["status||$eq||active", "age||$gte||18", "age||$lte||65"]
    }
}

4. JSON Search View (`view_type: 's'`)

Define complex search criteria:

{
    "view_name": "premium-enterprise",
    "view_type": "s",
    "entity_name": "resources",
    "description": "Premium and enterprise tier records",
    "config": {
        "s": {
            "category": {
                "in": ["premium", "enterprise"]
            },
            "is_active": true
        }
    }
}

5. Complete View (`view_type: 'complete'`)

Combine all query parameters into a single preset:

{
    "view_name": "active-premium",
    "view_type": "complete",
    "entity_name": "resources",
    "description": "Active premium resources with contact info",
    "config": {
        "fields": [
            "id",
            "reference_number",
            "first_name",
            "last_name",
            "category",
            "email",
            "phone_number"
        ],
        "sort": "created_at:desc",
        "s": {
            "category": "premium",
            "is_active": true
        },
        "limit": 50,
        "page": 1
    }
}

Usage:

GET /resources?view=active-premium

Equivalent to:

GET /resources?fields=id,reference_number,first_name,last_name,category,email,phone_number&sort=created_at:desc&s={"category":"premium","is_active":true}&limit=50&page=1

Implementation

Entity Definition

import {
    Column,
    CreateDateColumn,
    Entity,
    Index,
    PrimaryGeneratedColumn,
    UpdateDateColumn,
} from 'typeorm';

import { AppDatabases } from '@lib/common/enum/app-databases.enum';

export type ViewType = 'fields' | 'sort' | 's' | 'filter' | 'complete';

export interface QueryConfigValue {
    fields?: string[];
    sort?: string;
    s?: Record<string, any>;
    filter?: string[];
    limit?: number;
    page?: number;
    offset?: number;
}

@Entity({ name: 'query_config_views', database: AppDatabases.APP_CORE })
@Index('idx_query_config_views_entity', ['entity_name'])
@Index('idx_query_config_views_view_name', ['view_name'])
@Index('idx_query_config_views_view_type', ['view_type'])
export class QueryConfigView {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column({ type: 'varchar', length: 100, unique: true, comment: 'Unique view name identifier' })
    view_name: string;

    @Column({
        type: 'varchar',
        length: 50,
        comment: 'Type: fields, sort, s, filter, or complete',
    })
    view_type: ViewType;

    @Column({ type: 'text', nullable: true, comment: 'Description of the view purpose' })
    description: string | null;

    @Column({ type: 'varchar', length: 100, comment: 'Entity name (e.g., resources, orders)' })
    entity_name: string;

    @Column({ type: 'jsonb', comment: 'Query configuration as JSON' })
    config: QueryConfigValue;

    @Column({ type: 'boolean', default: true, comment: 'Whether the view is active' })
    is_active: boolean;

    @CreateDateColumn({ type: 'timestamptz' })
    created_at: Date;

    @UpdateDateColumn({ type: 'timestamptz' })
    updated_at: Date;

    @Column({ type: 'uuid', nullable: true })
    created_by: string | null;

    @Column({ type: 'uuid', nullable: true })
    updated_by: string | null;
}

Service Implementation

import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';

import { Repository } from 'typeorm';

import { QueryParamsDTO } from '@lib/common/dto/query-params.dto';
import { AppDatabases } from '@lib/common/enum/app-databases.enum';

import { QueryConfigValue, QueryConfigView } from '../entities/query-config-view.entity';

@Injectable()
export class QueryConfigViewsService {
    constructor(
        @InjectRepository(QueryConfigView, AppDatabases.APP_CORE)
        private readonly viewRepository: Repository<QueryConfigView>,
    ) {}

    /**
     * Resolve a view name to its query configuration
     */
    async resolveView(viewName: string, entityName: string): Promise<QueryConfigValue> {
        const view = await this.viewRepository.findOne({
            where: {
                view_name: viewName,
                entity_name: entityName,
                is_active: true,
            },
        });

        if (!view) {
            throw new NotFoundException(`View '${viewName}' not found for entity '${entityName}'`);
        }

        return view.config;
    }

    /**
     * Apply view configuration to QueryParamsDTO.
     * User-supplied parameters always take precedence over view defaults.
     */
    applyViewToQuery(queryDTO: QueryParamsDTO, viewConfig: QueryConfigValue): QueryParamsDTO {
        const mergedQuery = { ...queryDTO };

        // Apply fields (if not overridden by user)
        if (viewConfig.fields && !queryDTO.fields) {
            mergedQuery.fields = viewConfig.fields.join(',');
        }

        // Apply sort (if not overridden by user)
        if (viewConfig.sort && !queryDTO.sort) {
            mergedQuery.sort = viewConfig.sort;
        }

        // Apply JSON search (merge with user's search)
        if (viewConfig.s) {
            const existingSearch = queryDTO.s ? JSON.parse(queryDTO.s) : {};
            mergedQuery.s = JSON.stringify({ ...viewConfig.s, ...existingSearch });
        }

        // Apply filters (merge with user's filters)
        if (viewConfig.filter) {
            const existingFilters = Array.isArray(queryDTO.filter)
                ? queryDTO.filter
                : queryDTO.filter
                  ? [queryDTO.filter]
                  : [];
            mergedQuery.filter = [...viewConfig.filter, ...existingFilters];
        }

        // Apply pagination (if not overridden by user)
        if (viewConfig.limit && !queryDTO.limit) {
            mergedQuery.limit = viewConfig.limit;
        }
        if (viewConfig.page && !queryDTO.page) {
            mergedQuery.page = viewConfig.page;
        }
        if (viewConfig.offset !== undefined && queryDTO.offset === undefined) {
            mergedQuery.offset = viewConfig.offset;
        }

        return mergedQuery;
    }
}

Controller Integration

import { ValidatedQuery } from '@lib/common/decorators/validated-query.decorator';

@Controller('resources')
export class ResourcesController {
    constructor(
        private readonly resourcesService: ResourcesService,
        private readonly queryConfigService: QueryConfigViewsService,
    ) {}

    @Get()
    @RequirePermission('resource:view')
    async findPaginated(@ValidatedQuery(QueryParamsDTO) query: QueryParamsDTO) {
        // If 'view' parameter is provided, resolve it from the database
        if (query.view) {
            const viewConfig = await this.queryConfigService.resolveView(query.view, 'resources');
            query = this.queryConfigService.applyViewToQuery(query, viewConfig);
        }

        return this.resourcesService.findPaginated(query);
    }
}

Usage Examples

Example 1: Simple Field View

Create View:

INSERT INTO query_config_views (view_name, view_type, entity_name, description, config)
VALUES (
    'resource-list',
    'fields',
    'resources',
    'Basic resource list columns',
    '{"fields": ["id", "reference_number", "first_name", "last_name", "status"]}'
);

Usage:

# Without view (long URL)
GET /resources?fields=id,reference_number,first_name,last_name,status

# With view (short URL)
GET /resources?view=resource-list

Example 2: Combined View with Overrides

Create View:

INSERT INTO query_config_views (view_name, view_type, entity_name, description, config)
VALUES (
    'contact-list',
    'complete',
    'resources',
    'Contact list with phone and email',
    '{
        "fields": ["id", "reference_number", "first_name", "last_name", "phone_number", "email"],
        "sort": "last_name:asc",
        "s": {"is_contact_verified": true},
        "limit": 100
    }'
);

Usage:

# Use preset with page override
GET /resources?view=contact-list&page=2

# Override fields while keeping filters from the preset
GET /resources?view=contact-list&fields=id,reference_number,phone_number

Benefits

Benefit	Description
URL Simplification	Complex queries become simple: `?view=preset-name`
Consistency	Standardized queries across frontend applications
Maintainability	Update query logic in database, not in code
Performance	Pre-optimized queries with proper field selection
Versioning	Track query changes via database migrations
User Experience	Bookmarkable, shareable clean URLs
Security	Validate and sanitize queries in one place
Analytics	Track which presets are most used

Best Practices

1. Naming Conventions

✅ GOOD
- active-resources
- newest-orders
- premium-accounts
- resource-summary-fields

❌ BAD
- view1
- temp
- test-view

2. Documentation

Always provide a clear description:

{
    "view_name": "high-value-accounts",
    "description": "Enterprise accounts requiring priority support queues",
    "config": { "..." : "..." }
}

3. Entity Scoping

Always scope views to specific entities to avoid name collisions:

-- Separate views for different entities, same view_name is safe
INSERT INTO query_config_views (view_name, entity_name, ...)
VALUES ('summary', 'resources', ...);

INSERT INTO query_config_views (view_name, entity_name, ...)
VALUES ('summary', 'orders', ...);

4. Override Support

Design views to allow user overrides — view values are defaults, not locks:

GET /resources?view=active-resources&limit=200   -- Override limit
GET /resources?view=active-resources&sort=reference_number:asc  -- Override sort

5. Testing

Create views for common test scenarios:

{
    "view_name": "test-data-sample",
    "view_type": "complete",
    "description": "Sample data for frontend integration testing",
    "config": {
        "limit": 10,
        "sort": "created_at:desc",
        "s": { "is_test_data": true }
    }
}

Migration Example

import { MigrationInterface, QueryRunner, Table, TableIndex } from 'typeorm';

export class AddQueryConfigViews1234567890 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(
            new Table({
                name: 'query_config_views',
                columns: [
                    { name: 'id', type: 'uuid', isPrimary: true, default: 'uuid_generate_v4()' },
                    { name: 'view_name', type: 'varchar', length: '100', isUnique: true },
                    { name: 'view_type', type: 'varchar', length: '50' },
                    { name: 'description', type: 'text', isNullable: true },
                    { name: 'entity_name', type: 'varchar', length: '100' },
                    { name: 'config', type: 'jsonb' },
                    { name: 'is_active', type: 'boolean', default: true },
                    { name: 'created_at', type: 'timestamptz', default: 'NOW()' },
                    { name: 'updated_at', type: 'timestamptz', default: 'NOW()' },
                    { name: 'created_by', type: 'uuid', isNullable: true },
                    { name: 'updated_by', type: 'uuid', isNullable: true },
                ],
            }),
        );

        await queryRunner.createIndex(
            'query_config_views',
            new TableIndex({ name: 'idx_query_config_views_entity', columnNames: ['entity_name'] }),
        );

        // Insert default views
        await queryRunner.query(`
            INSERT INTO query_config_views (view_name, view_type, entity_name, description, config)
            VALUES
            ('resource-summary', 'fields', 'resources', 'Basic resource summary',
             '{"fields": ["id", "reference_number", "first_name", "last_name", "birth_date"]}'),
            ('newest-first', 'sort', 'resources', 'Newest records first',
             '{"sort": "created_at:desc"}'),
            ('active-resources', 'filter', 'resources', 'Active resources only',
             '{"filter": ["status||$eq||active"]}');
        `);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.dropTable('query_config_views');
    }
}

Summary

Query presets via query_config_views table provide:

✅ Shorter URLs: Replace long query strings with simple view names
✅ Reusability: Define once, use everywhere
✅ Flexibility: Support overrides and combinations
✅ Type Safety: Validate configurations at database level
✅ Maintainability: Update queries without code changes
✅ Performance: Pre-optimized field selections and filters
✅ User Experience: Clean, shareable, bookmarkable URLs

Query Presets & URL Optimization

The Problem: URL Length Limits

The Solution: Query Presets (Database Views)

Database Schema: query_config_views Table

View Types and Examples

1. Field Selection View (view_type: 'fields')

2. Sorting View (view_type: 'sort')

3. Filter View (view_type: 'filter')

4. JSON Search View (view_type: 's')

5. Complete View (view_type: 'complete')

Implementation

Entity Definition

Service Implementation

Controller Integration

Usage Examples

Example 1: Simple Field View

Example 2: Combined View with Overrides

Benefits

Best Practices

1. Naming Conventions

2. Documentation

3. Entity Scoping

4. Override Support

5. Testing

Migration Example

Summary

Database Schema: `query_config_views` Table

1. Field Selection View (`view_type: 'fields'`)

2. Sorting View (`view_type: 'sort'`)

3. Filter View (`view_type: 'filter'`)

4. JSON Search View (`view_type: 's'`)

5. Complete View (`view_type: 'complete'`)