Basic Queries

Overview

Basic queries form the foundation of data retrieval in Q01 Core APIs. This page covers the three fundamental query operations:

1. Filtering - Select specific records with $filter
2. Field Selection - Choose which fields to return with $select
3. Sorting - Order results with $order

Simple Filtering with $filter

Equality Operator (eq)

Find products with specific value:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD01 eq 'Widget Pro'

Generated SQL:

SELECT * FROM TB_ANAG_PRD00
WHERE XPRD01 = 'Widget Pro'
  AND XPRD01_AMBIENTE IN (...) -- ambiente filter
  AND TREC != 'C'; -- default TREC filter

Response:

{
  "data": [
    {
      "PRD_ID": 123,
      "XPRD01": "Widget Pro",
      "XPRD02": 49.99,
      "XPRD03": "PRD-2025-001"
    }
  ]
}

Not Equal Operator (ne)

Find all products except specific category:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD05 ne 'Electronics'

Generated SQL:

WHERE XPRD05 != 'Electronics'

Comparison Operators (gt, lt, ge, le)

Find products with price greater than 100:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD02 gt 100

All comparison operators:

Operator	Meaning	Example
gt	Greater than	XPRD02 gt 100
lt	Less than	XPRD02 lt 50
ge	Greater or equal	XPRD02 ge 100
le	Less or equal	XPRD02 le 50

Boolean Values

Find active products:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD06 eq true

Find inactive products:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD06 eq false

Generated SQL:

WHERE XPRD06 = 1  -- true
WHERE XPRD06 = 0  -- false

Null Values

Find products without description:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD04 eq null

Find products with description:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD04 ne null

Generated SQL:

WHERE XPRD04 IS NULL     -- eq null
WHERE XPRD04 IS NOT NULL -- ne null

Field Selection with $select

Select Specific Fields

Request only ID, name, and price:

GET /api/v4/core/PRD?source=productList&\
  $select=PRD_ID,XPRD01,XPRD02

Response (reduced):

{
  "data": [
    {
      "PRD_ID": 123,
      "XPRD01": "Widget Pro",
      "XPRD02": 49.99
      // Other fields NOT included
    }
  ]
}

Benefits of $select

Performance:

• Reduces response size (faster network transfer)
• Reduces database I/O (fewer columns read)
• Reduces JSON serialization time

Example savings:

# Without $select (50 fields, 100 records) → ~500KB response
GET /api/v4/core/PRD?source=productList&$num_rows=100

# With $select (3 fields, 100 records) → ~50KB response (10x smaller)
GET /api/v4/core/PRD?source=productList&\
  $select=PRD_ID,XPRD01,XPRD02&\
  $num_rows=100

Always Include Primary Key

Best practice:

# ✅ Good - includes primary key
$select=PRD_ID,XPRD01,XPRD02

# ❌ Bad - missing primary key
$select=XPRD01,XPRD02

Why? Primary key needed for:

• Detail view navigation
• Update operations
• Delete operations
• Front-end state management

Sorting with $order

Single Field Sort

Sort by name (ascending):

GET /api/v4/core/PRD?source=productList&\
  $order=XPRD01 ASC

Sort by price (descending):

GET /api/v4/core/PRD?source=productList&\
  $order=XPRD02 DESC

Generated SQL:

ORDER BY XPRD01 ASC
ORDER BY XPRD02 DESC

Default Sort Order

If ASC/DESC omitted, defaults to ASC:

# These are equivalent:
$order=XPRD01
$order=XPRD01 ASC

Multi-Field Sort

Sort by category then name:

GET /api/v4/core/PRD?source=productList&\
  $order=XPRD05 ASC,XPRD01 ASC

Generated SQL:

ORDER BY XPRD05 ASC, XPRD01 ASC

Result: Products grouped by category, sorted by name within each category.

Mixed Sort Directions

Sort by price descending, then name ascending:

GET /api/v4/core/PRD?source=productList&\
  $order=XPRD02 DESC,XPRD01 ASC

Use case: Show highest-priced products first, with alphabetical names for same price.

Combining Query Parameters

Filter + Select + Sort

Complete query example:

GET /api/v4/core/PRD?source=productList&\
  $filter=XPRD06 eq true AND XPRD02 gt 50&\
  $select=PRD_ID,XPRD01,XPRD02&\
  $order=XPRD02 DESC

What it does:

1. Filter: Active products with price > 50
2. Select: Return only ID, name, price
3. Sort: Order by price (highest first)

Response:

{
  "data": [
    {"PRD_ID": 456, "XPRD01": "Premium Widget", "XPRD02": 199.99},
    {"PRD_ID": 123, "XPRD01": "Widget Pro", "XPRD02": 149.99},
    {"PRD_ID": 789, "XPRD01": "Standard Widget", "XPRD02": 99.99}
  ]
}

JavaScript Examples

Basic Query Client

class ProductQuery {
  constructor(apiBase, token) {
    this.apiBase = apiBase;
    this.token = token;
  }

  async query(filters = {}) {
    const url = new URL(`${this.apiBase}/api/v4/core/PRD`);
    url.searchParams.append('source', filters.source || 'productList');

    // Add $filter
    if (filters.$filter) {
      url.searchParams.append('$filter', filters.$filter);
    }

    // Add $select
    if (filters.$select) {
      url.searchParams.append('$select', filters.$select);
    }

    // Add $order
    if (filters.$order) {
      url.searchParams.append('$order', filters.$order);
    }

    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    if (!response.ok) {
      throw new Error('Query failed');
    }

    return response.json();
  }
}

// Usage examples
const client = new ProductQuery(apiBase, token);

// Simple filter
const activeProducts = await client.query({
  $filter: 'XPRD06 eq true'
});

// Filter + Select
const productList = await client.query({
  $filter: 'XPRD02 gt 100',
  $select: 'PRD_ID,XPRD01,XPRD02'
});

// Filter + Select + Sort
const sortedProducts = await client.query({
  $filter: 'XPRD05 eq "Electronics"',
  $select: 'PRD_ID,XPRD01,XPRD02',
  $order: 'XPRD02 DESC'
});

Best Practices

✅ DO:

Use $select to reduce response size:

# ✅ Good
$select=PRD_ID,XPRD01,XPRD02

Filter at database level:

# ✅ Good - filter in query
$filter=XPRD06 eq true

# ❌ Bad - fetch all, filter in JavaScript
# (fetches cancelled/inactive records unnecessarily)

Use appropriate operators:

# ✅ Good - exact match
$filter=XPRD01 eq 'Widget'

# ✅ Good - numeric comparison
$filter=XPRD02 gt 100

Sort by indexed fields when possible:

# ✅ Good - XPRD03 is counter field (indexed)
$order=XPRD03 ASC

❌ DON'T:

Don't fetch all fields when you need few:

# ❌ Bad - returns 50+ fields
GET /api/v4/core/PRD?source=productList

# ✅ Good - returns only 3 fields
GET /api/v4/core/PRD?source=productList&$select=PRD_ID,XPRD01,XPRD02

Don't use string comparison for numbers:

# ❌ Wrong - treats price as string
$filter=XPRD02 eq '100'

# ✅ Correct - numeric comparison
$filter=XPRD02 eq 100

Don't sort by non-indexed large text fields:

# ❌ Bad - slow on large tables
$order=XPRD31 ASC

# ✅ Better - sort by indexed field
$order=XPRD01 ASC

Common Mistakes

Mistake 1: Missing Quotes for Strings

# ❌ Wrong - syntax error
$filter=XPRD01 eq Widget

# ✅ Correct
$filter=XPRD01 eq 'Widget'

Mistake 2: Using = Instead of eq

# ❌ Wrong - not valid $filter syntax
$filter=XPRD01 = 'Widget'

# ✅ Correct
$filter=XPRD01 eq 'Widget'

Mistake 3: Forgetting URL Encoding

# ❌ Wrong - spaces not encoded
$filter=XPRD01 eq 'Widget Pro'

# ✅ Correct - URL encoded
$filter=XPRD01 eq 'Widget%20Pro'

# JavaScript handles this automatically:
const filter = "XPRD01 eq 'Widget Pro'";
url.searchParams.append('$filter', filter); // Auto-encodes

Summary

Basic queries provide three core capabilities:

1. $filter - Filter records using operators (eq, ne, gt, lt, ge, le)
2. $select - Select specific fields to reduce response size
3. $order - Sort results (ASC/DESC)

Key Takeaways:

• ✅ Always use $select to minimize response size
• ✅ Filter at database level with $filter
• ✅ Include primary key in $select
• ✅ Use appropriate data types (strings in quotes, numbers without)
• ✅ Sort by indexed fields when possible

Next: Complex Filters → to learn AND/OR combinations and nested conditions.

Related Concepts

• Read Operations - Core READ documentation
• Field Visibility - Understanding COD_ON_OFF
• Context Chain - How context affects queries