Skip to main content

Custom Queries

Learn how to create powerful, reusable queries in Stack9 to fetch and manipulate data efficiently. This guide covers the Query Library, query syntax, filtering, sorting, pagination, and advanced query patterns.

What You'll Learn​

  • ✅ Query Library structure and purpose
  • ✅ Query syntax and operators ($where, $select, $orderBy)
  • ✅ Loading relationships with $withRelated
  • ✅ Filtering, sorting, and pagination
  • ✅ Parameterized queries
  • ✅ Advanced query patterns
  • ✅ Query optimization techniques

Time Required: 30-40 minutes

Prerequisites​

  • Completed Creating Entities guide
  • Understanding of SQL or database queries (helpful but not required)
  • Entities created in your Stack9 instance

Understanding the Query Library​

The Query Library is Stack9's centralized repository for reusable data queries. Instead of writing database queries in multiple places, you define them once and reference them from:

  • Screens - List views, detail views
  • Actions - Workflows and automations
  • Custom code - Entity hooks, action types

Benefits:

  • ✅ Reusability - Define once, use everywhere
  • ✅ Maintainability - Update in one place
  • ✅ Performance - Optimized query execution
  • ✅ Type safety - Automatic TypeScript types
  • ✅ Testing - Easy to test queries independently

Query File Structure​

Queries are stored in src/query-library/:

src/
└── query-library/
    ├── getcustomerlist.json
    ├── getcustomer.json
    ├── getproductlist.json
    └── getorderdetails.json

Basic Query Definition​

Let's create a simple query to fetch a list of active customers:

File: src/query-library/getcustomerlist.json

{
  "key": "getcustomerlist",
  "name": "getCustomerList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/customer/search",
    "bodyParams": "{\n  \"$select\": [\"id\", \"name\", \"email\", \"phone\", \"status\"],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"status\": \"Active\"\n  },\n  \"$orderBy\": [\n    {\"column\": \"name\", \"order\": \"asc\"}\n  ]\n}"
  }
}

Query Properties​

PropertyRequiredDescription
keyYesUnique identifier (camelCase)
nameYesDisplay name (PascalCase)
connectorYesData source (stack9_api for internal entities)
queryTemplateYesQuery configuration

Query Template Properties​

PropertyRequiredDescription
methodYesHTTP method (get, post)
pathYesAPI endpoint path
bodyParamsNoRequest body (for POST)
queryParamsNoURL parameters (for GET)

Query Syntax Reference​

Stack9 uses a MongoDB-inspired query syntax:

$select - Choose Fields​

Select specific fields to return:

{
  "$select": ["id", "name", "email", "status"]
}

Select related entity fields:

{
  "$select": [
    "id",
    "order_number",
    "customer.name",
    "customer.email"
  ]
}

Select all fields:

{
  "$select": ["*"]
}

$where - Filter Records​

Filter records with conditions:

{
  "$where": {
    "status": "Active",
    "_is_deleted": false
  }
}

Operators:

OperatorSQL EquivalentExample
Direct value="status": "Active"
$ne!="status": {"$ne": "Cancelled"}
$gt>"price": {"$gt": 100}
$gte>="price": {"$gte": 100}
$lt<"price": {"$lt": 1000}
$lte<="price": {"$lte": 1000}
$inIN"status": {"$in": ["Active", "Pending"]}
$notInNOT IN"status": {"$notIn": ["Cancelled"]}
$likeLIKE"name": {"$like": "%John%"}
$betweenBETWEEN"price": {"$between": [10, 100]}
$isNullIS NULL"deleted_at": {"$isNull": true}

Complex conditions:

{
  "$where": {
    "$or": [
      {"status": "Active"},
      {"status": "Pending"}
    ],
    "price": {
      "$gte": 100,
      "$lte": 1000
    }
  }
}

$orderBy - Sort Results​

Sort by one or more fields:

{
  "$orderBy": [
    {"column": "created_at", "order": "desc"},
    {"column": "name", "order": "asc"}
  ]
}

$withRelated - Load Relationships​

Load related entities:

{
  "$withRelated": [
    "customer",
    "order_items",
    "order_items.product"
  ]
}

With scopes:

{
  "$withRelated": [
    "customer(notDeleted)",
    "order_items(notDeleted).product(active)"
  ]
}

$limit and $offset - Pagination​

Limit results and skip records:

{
  "$limit": 20,
  "$offset": 0
}

Complete Query Examples​

Example 1: Customer List Query​

File: src/query-library/getcustomerlist.json

{
  "key": "getcustomerlist",
  "name": "getCustomerList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/customer/search",
    "bodyParams": "{\n  \"$select\": [\n    \"id\",\n    \"crn\",\n    \"name\",\n    \"last_name\",\n    \"email\",\n    \"phone\",\n    \"status\",\n    \"_created_at\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false\n  },\n  \"$orderBy\": [\n    {\"column\": \"_created_at\", \"order\": \"desc\"}\n  ],\n  \"$limit\": 50\n}"
  }
}

Example 2: Single Record Query​

File: src/query-library/getcustomer.json

{
  "key": "getcustomer",
  "name": "getCustomer",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/customer/search",
    "bodyParams": "{\n  \"$select\": [\"*\"],\n  \"$where\": {\n    \"id\": {{id}}\n  }\n}",
    "queryParams": {}
  },
  "userParams": {
    "id": ""
  }
}

Key points:

  • Uses {{id}} parameter placeholder
  • userParams defines available parameters
  • Returns single record by ID

Example 3: Query with Relationships​

File: src/query-library/getorderdetails.json

{
  "key": "getorderdetails",
  "name": "getOrderDetails",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/order/search",
    "bodyParams": "{\n  \"$select\": [\n    \"*\",\n    \"customer.name\",\n    \"customer.email\",\n    \"customer.phone\",\n    \"order_items.product.name\",\n    \"order_items.quantity\",\n    \"order_items.unit_price\",\n    \"order_items.line_total\"\n  ],\n  \"$where\": {\n    \"id\": {{orderId}}\n  },\n  \"$withRelated\": [\n    \"customer(notDeleted)\",\n    \"order_items(notDeleted)\",\n    \"order_items(notDeleted).product(notDeleted)\"\n  ]\n}",
    "queryParams": {}
  },
  "userParams": {
    "orderId": ""
  }
}

File: src/query-library/searchproducts.json

{
  "key": "searchproducts",
  "name": "searchProducts",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/product/search",
    "bodyParams": "{\n  \"$select\": [\n    \"id\",\n    \"sku\",\n    \"name\",\n    \"price\",\n    \"stock_quantity\",\n    \"category.name\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"$or\": [\n      {\"name\": {\"$like\": \"%{{searchTerm}}%\"}},\n      {\"sku\": {\"$like\": \"%{{searchTerm}}%\"}}\n    ]\n  },\n  \"$withRelated\": [\"category(notDeleted)\"],\n  \"$orderBy\": [\n    {\"column\": \"name\", \"order\": \"asc\"}\n  ],\n  \"$limit\": {{limit}},\n  \"$offset\": {{offset}}\n}",
    "queryParams": {}
  },
  "userParams": {
    "searchTerm": "",
    "limit": "20",
    "offset": "0"
  }
}

Parameterized Queries​

Use parameters to make queries dynamic and reusable:

Defining Parameters​

{
  "userParams": {
    "status": "Active",
    "limit": "20",
    "offset": "0",
    "search": ""
  }
}

Using Parameters​

{
  "$where": {
    "status": "{{status}}",
    "name": {"$like": "%{{search}}%"}
  },
  "$limit": {{limit}},
  "$offset": {{offset}}
}

Important: Don't use quotes around numeric parameters:

"$limit": {{limit}}       // ✅ Correct
"$limit": "{{limit}}"     // ❌ Wrong

Query Folders​

Organize queries into folders:

File: src/query-folders.json

{
  "folders": [
    {
      "key": "customers",
      "name": "Customer Queries",
      "order": 1
    },
    {
      "key": "orders",
      "name": "Order Queries",
      "order": 2
    },
    {
      "key": "reports",
      "name": "Reports",
      "order": 3
    }
  ]
}

Then reference in query:

{
  "key": "getcustomerlist",
  "name": "getCustomerList",
  "folderKey": "customers",
  "connector": "stack9_api"
}

Advanced Query Patterns​

Pattern 1: Date Range Filtering​

{
  "$where": {
    "_created_at": {
      "$gte": "{{startDate}}",
      "$lte": "{{endDate}}"
    }
  }
}
{
  "$where": {
    "$or": [
      {"name": {"$like": "%{{search}}%"}},
      {"email": {"$like": "%{{search}}%"}},
      {"phone": {"$like": "%{{search}}%"}}
    ]
  }
}

Pattern 3: Conditional Filtering​

Skip filters when parameter is empty:

{
  "$where": {
    "status": "{{status}}",
    "category_id": {{categoryId}}
  }
}

Stack9 automatically ignores empty string parameters.

Pattern 4: Aggregations​

Count records:

{
  "method": "post",
  "path": "/order/count",
  "bodyParams": "{\n  \"$where\": {\n    \"status\": \"Completed\"\n  }\n}"
}

Pattern 5: Nested Relationship Loading​

{
  "$withRelated": [
    "order_items",
    "order_items.product",
    "order_items.product.category",
    "customer",
    "customer.customer_group"
  ]
}

Pattern 6: Complex AND/OR Logic​

{
  "$where": {
    "$and": [
      {
        "$or": [
          {"status": "Active"},
          {"status": "Pending"}
        ]
      },
      {
        "$or": [
          {"priority": "High"},
          {"priority": "Urgent"}
        ]
      },
      {"_is_deleted": false}
    ]
  }
}

Using Queries in Screens​

List View​

{
  "head": {
    "title": "Customers",
    "key": "customer_list",
    "route": "customer-list"
  },
  "screenType": "listView",
  "listQuery": "getcustomerlist",
  "entityKey": "customer"
}

Detail View​

{
  "head": {
    "title": "Customer Details",
    "key": "customer_detail",
    "route": "customer-detail/:id"
  },
  "screenType": "detailView",
  "detailQuery": "getcustomer",
  "entityKey": "customer"
}

Using Queries in Code​

In Action Types​

import { S9AutomationActionType } from '@april9/stack9-sdk';

export class ProcessOrders extends S9AutomationActionType {
  async exec(params: any) {
    const { db, services } = this.context;

    // Use query service
    const customers = await services.query.execute('getcustomerlist', {
      status: 'Active'
    });

    // Direct database query
    const orders = await db('order')
      .where({ status: 'Pending' })
      .where('_is_deleted', false)
      .orderBy('_created_at', 'desc')
      .limit(50);

    return { success: true, count: orders.length };
  }
}

In Entity Hooks​

import { CustomFunction, CustomFunctionResponse } from '@april9/stack9-sdk';

export class ValidateOrder extends CustomFunction {
  async exec(): Promise<CustomFunctionResponse> {
    const { entity, db } = this.context;

    // Query related customer
    const customer = await db('customer')
      .where({ id: entity.customer_id })
      .where({ _is_deleted: false })
      .first();

    if (!customer) {
      return {
        valid: false,
        errors: ['Customer not found']
      };
    }

    return { valid: true, entity };
  }
}

Query Performance Optimization​

1. Select Only Needed Fields​

// ✅ Good - Select specific fields
{
  "$select": ["id", "name", "email"]
}

// ❌ Bad - Select all fields when not needed
{
  "$select": ["*"]
}

2. Index Frequently Queried Fields​

In entity definition:

{
  "label": "Email",
  "key": "email",
  "type": "TextField",
  "index": true  // ✅ Creates database index
}

3. Use Pagination​

{
  "$limit": 50,
  "$offset": 0
}

4. Avoid N+1 Queries​

// ✅ Good - Load relationships in one query
{
  "$withRelated": ["customer", "order_items"]
}

// ❌ Bad - Loading relationships separately causes N+1

5. Use Specific WHERE Conditions​

// ✅ Good - Specific condition
{
  "$where": {
    "status": "Active",
    "category_id": 5
  }
}

// ❌ Bad - Too broad
{
  "$where": {
    "_is_deleted": false
  }
}

Testing Queries​

Test via API​

# Test query directly
curl -X POST http://localhost:3000/api/customer/search \
  -H "Content-Type: application/json" \
  -d '{
    "$select": ["id", "name", "email"],
    "$where": {"status": "Active"},
    "$orderBy": [{"column": "name", "order": "asc"}],
    "$limit": 10
  }'

Test with Parameters​

curl -X POST http://localhost:3000/api/query/getcustomer \
  -H "Content-Type: application/json" \
  -d '{
    "params": {
      "id": 123
    }
  }'

Common Query Patterns​

Pattern 1: Recent Records​

{
  "$orderBy": [{"column": "_created_at", "order": "desc"}],
  "$limit": 10
}

Pattern 2: Active Records Only​

{
  "$where": {
    "_is_deleted": false,
    "is_active": true
  }
}

Pattern 3: Search Across Multiple Fields​

{
  "$where": {
    "$or": [
      {"first_name": {"$like": "%{{search}}%"}},
      {"last_name": {"$like": "%{{search}}%"}},
      {"email": {"$like": "%{{search}}%"}}
    ]
  }
}

Pattern 4: Status-Based Filtering​

{
  "$where": {
    "status": {
      "$in": ["Active", "Pending", "Processing"]
    }
  }
}

Troubleshooting​

Query Returns No Results​

Problem: Query returns empty array

Solutions:

  1. Check $where conditions are correct
  2. Verify records exist in database
  3. Check _is_deleted filter isn't too restrictive
  4. Test query in API directly

Relationships Not Loading​

Problem: Related data is null or missing

Solutions:

  1. Add to $withRelated array
  2. Add related fields to $select
  3. Check foreign key values exist
  4. Verify relationship is defined in entity

Query Too Slow​

Problem: Query takes too long to execute

Solutions:

  1. Add indexes to filtered/sorted fields
  2. Reduce number of selected fields
  3. Add $limit to cap results
  4. Avoid loading too many relationships
  5. Check database query plan

Parameter Not Working​

Problem: Parameter {{param}} not being replaced

Solutions:

  1. Define parameter in userParams
  2. Remove quotes around numeric parameters
  3. Check parameter name matches exactly
  4. Verify parameter is passed when executing query

Best Practices​

1. Name Queries Descriptively​

✅ Good:
getcustomerlist
getactiveorders
searchproductsbycategory

❌ Bad:
query1
customers
list

2. Always Filter Deleted Records​

{
  "$where": {
    "_is_deleted": false  // ✅ Always include
  }
}

3. Use Consistent Ordering​

{
  "$orderBy": [
    {"column": "_created_at", "order": "desc"}  // ✅ Consistent default
  ]
}

4. Document Complex Queries​

{
  "key": "getcomplexreport",
  "name": "getComplexReport",
  "description": "Returns aggregated sales data grouped by month with customer details",
  "connector": "stack9_api"
}

5. Keep Queries Focused​

Create separate queries for different use cases rather than one complex query.

Next Steps​

Now that you've mastered custom queries:

Summary​

You now understand:

✅ Query Library structure and purpose ✅ Query syntax ($where, $select, $orderBy, $withRelated) ✅ Filtering and sorting data ✅ Parameterized queries ✅ Loading relationships efficiently ✅ Query optimization techniques ✅ Using queries in screens and code

Custom queries are essential for efficient data retrieval in Stack9 applications!