Query Patterns

This section covers advanced query patterns and techniques for retrieving data from Q01 Core APIs.

What You'll Learn

Core APIs provide powerful querying capabilities through URL parameters:

• Basic Queries - Simple filtering, field selection, and sorting
• Complex Filters - AND/OR combinations and nested conditions
• Field Projection - Selecting specific fields with $select
• Pagination - Efficient data pagination with $num_rows and $offset
• Sorting - Single and multi-field sorting patterns
• TREC Filtering - Querying by record lifecycle state
• Relational Queries - Joining data across dimensions
• Document Queries - Multi-table aggregation with pre-defined and on-the-fly documents
• AC/VARS Queries - Foreign key field autocomplete and select list queries
• Aggregations - COUNT, SUM, AVG, and GROUP BY operations
• Full-Text Search - text search functions patterns and search optimization
• Performance - Query optimization and index usage

Quick Example

# Basic query with filter, select, sort, and pagination
GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD06 eq true AND XPRD02 gt 100&\
  $select=PRD_ID,XPRD01,XPRD02&\
  $order=XPRD01 ASC&\
  $num_rows=25&\
  $offset=50

Result: Returns 25 active products with price > 100, showing only ID/name/price, sorted by name, starting from record 51.

Query Parameters Reference

Parameter	Purpose	Example
$filter	Filter records	XPRD06 eq true
$select	Select specific fields	PRD_ID,XPRD01,XPRD02
$order	Sort results	XPRD01 ASC,XPRD02 DESC
$num_rows	Limit number of results	25
$offset	Skip first N results	50
cond_trec	Filter by TREC state	N,M (exclude cancelled)

Query Execution Flow

1. JWT Validation
   └─ Verify token signature and claims

2. Permission Check (Grant Level)
   └─ Validate source permission in TB_MENU

3. Load Metadata
   ├─ TB_DIM (dimension definition)
   ├─ TB_COST (field definitions)
   └─ TB_OBJECT (table/view mapping)

4. Apply Context Filters
   ├─ peso filtering (COD_UTENTE)
   ├─ COD_ON_OFF visibility
   └─ ambiente isolation (XDIM01)

5. Parse Query Parameters
   ├─ $filter → SQL WHERE clause
   ├─ $select → SQL SELECT columns
   ├─ $order → SQL ORDER BY
   ├─ $num_rows → SQL LIMIT
   └─ $offset → SQL OFFSET

6. Execute Query
   └─ Run SQL against physical table

7. Filter Response
   └─ Remove fields based on COD_ON_OFF and peso

8. Return JSON

Core Concepts to Understand

Before diving into query patterns, ensure you understand:

• Dimensions - What dimensions are and how they work
• Field Visibility - How COD_ON_OFF controls visible fields
• Context Chain - How context affects queries
• Permissions - Multi-layer permission validation

Query Pattern Categories

Read Operations

• Basic Queries - Simple filters and sorting
• Complex Filters - Advanced filter combinations
• Field Projection - Selecting specific fields
• Pagination - Efficient data pagination

Advanced Queries

• Sorting Patterns - Multi-field sorting strategies
• TREC Filtering - Filtering by record state
• Relational Queries - Joining across dimensions
• Document Queries - Multi-table aggregation queries
• AC/VARS Queries - Foreign key autocomplete/select queries
• Aggregations - COUNT, SUM, AVG operations

Search & Performance

• Full-Text Search - text search functions patterns
• Query Performance - Optimization techniques

Best Practices

✅ DO:

• Use $select to reduce response size
• Apply $filter to limit results at database level
• Use cond_trec to exclude cancelled records
• Leverage indexes for frequently filtered fields
• Use $num_rows to limit large result sets
• Combine filters with AND for better performance

❌ DON'T:

• Fetch all fields when you only need a few
• Skip pagination on large datasets
• Use $filter with non-indexed fields for large tables
• Ignore TREC state in queries (always filter cancelled)
• Use complex nested OR conditions without indexes
• Fetch large datasets without $num_rows limits

Common Query Patterns

Pattern 1: List View with Pagination

GET /api/v4/core/PRD?source=productList&\
  center_dett=visualizza&\
  $order=XPRD01 ASC&\
  $num_rows=25&\
  $offset=0

Pattern 2: Search with Filter

GET /api/v4/core/PRD?source=productList&\
  $filter=contains(XPRD01,'Widget') AND XPRD06 eq true&\
  $select=PRD_ID,XPRD01,XPRD02

Pattern 3: Detail View with Joins

GET /api/v4/core/PRD/123?source=productDetail&\
  center_dett=dettaglio&\
  include=CATEGORY,SUPPLIER

Pattern 4: Aggregation Query

GET /api/v4/core/PRD?source=productReport&\
  aggregate=COUNT&\
  groupBy=XPRD05

Query Limitations

Size Limits

• Default $num_rows: 100 records
• Maximum $num_rows: 1000 records
• Response timeout: 30 seconds

Filter Complexity

• Maximum filter depth: 5 levels
• Maximum conditions: 20 per query
• String length: 2000 characters

Performance Considerations

• Queries without indexes may timeout on large tables (>100k records)
• Complex OR conditions can be slow (use indexes or split queries)
• Joins across many dimensions increase response time

Error Handling

Common Query Errors

Invalid $filter syntax:

{
  "code": 400,
  "message": "Invalid $filter syntax",
  "errors": [
    {
      "field": "$filter",
      "message": "Unexpected token 'XPRD_INVALID'"
    }
  ]
}

Field not found:

{
  "code": 400,
  "message": "Field not found in dimension PRD",
  "errors": [
    {
      "field": "XPRD_NONEXISTENT",
      "message": "Field not defined in TB_COST"
    }
  ]
}

Permission denied:

{
  "code": 403,
  "message": "Insufficient permissions for query",
  "required_grant": 1
}

Next Steps

Start with Basic Queries → to learn fundamental query patterns.

Related Concepts

• Read Operations - Core READ operation documentation
• Field Metadata - Getting field metadata for queries
• Context Chain - Understanding context in queries