Simple Entity Retrieval with Related Data

Description

This query demonstrates the fundamental pattern for retrieving a single entity by ID with its related data using Stack9's API connector. It showcases the Stack9 ORM-style query syntax that allows for declarative data fetching with relationship loading through the $withRelated parameter. The query uses a POST method to the search endpoint rather than a simple GET, which allows for complex field selection and relationship loading in a single request.

The pattern shown here is one of the most common in Stack9 applications - fetching detailed information about a specific record along with its associated entities. This approach minimizes the number of API calls needed by eagerly loading related data in a single request. The query also demonstrates proper soft-delete handling by checking the _is_deleted flag and using the notDeleted scope on relationships.

This query exemplifies Stack9's approach to data retrieval where complex object graphs can be fetched efficiently through a declarative JSON syntax rather than writing SQL JOINs manually. The extensive field selection list shows how specific columns can be cherry-picked from the main entity and its relationships, optimizing the payload size and reducing unnecessary data transfer.

Use Case

Used when displaying a detailed view of a subscription in the lottery/donation system, such as when a customer service representative needs to view all information about a customer's subscription including payment details, associated sales channel, club information, and any deferral reasons.

Key Features

• Uses POST method with search endpoint for flexible querying
• Selective field retrieval through $select array for optimized payloads
• Relationship loading via $withRelated parameter with soft-delete filtering
• Parameter interpolation using double curly brace syntax for dynamic queries
• Comprehensive field selection including nested relationship fields

Query Type

SELECT

JSON Definition

{
  "key": "getsubscriptiondetails",
  "name": "getSubscriptionDetails",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/subscription/search",
    "bodyParams": "{\n  \"$select\": [\n    \"subscription_number\",\n    \"customer_external_reference\",\n    \"total_dollar_amount\",\n    \"game_type\",\n    \"start_dt\",\n    \"payment_method\",\n    \"last_payment_status\",\n    \"last_payment_dt\",\n    \"next_debit_run_dt\",\n    \"last_debit_run_dt\",\n    \"transaction_reference\",\n    \"transaction_dt\",\n    \"frequency\",\n    \"day_of_month\",\n    \"utm_campaign\",\n    \"utm_medium\",\n    \"utm_source\",\n    \"card_type\",\n    \"card_token\",\n    \"card_name\",\n    \"card_mask\",\n    \"card_expiry_month\",\n    \"card_expiry_year\",\n    \"account_name\",\n    \"bsb_code\",\n    \"bank_account_number\",\n    \"qty\",\n    \"ticket_qty_per_unit\",\n    \"total_ticket_qty\",\n    \"total_dollar_amount\",\n    \"item_type\",\n    \"unit_amount\",\n    \"single_occurrence_per_game\",\n    \"ticketbook_preference_type\",\n    \"site_code\",\n    \"seller_code\",\n\n    \"customer_crn\",\n    \"customer_country_code\",\n    \"customer_state\",\n    \"customer_address_line_1\",\n    \"customer_address_line_2\",\n    \"customer_country\",\n    \"customer_country_code\",\n    \"customer_dob\",\n    \"customer_email\",\n    \"customer_gender\",\n    \"customer_id\",\n    \"customer_last_name\",\n    \"customer_middle_name\",\n    \"customer_name\",\n    \"customer_phone\",\n    \"customer_post_code\",\n    \"customer_preferred_name\",\n    \"customer_state\",\n    \"customer_suburb\",\n\n    \"sales_channel_id\",\n    \"sales_channel.name\",\n\n    \"club_id\",\n    \"club.name\",\n\n    \"deferral_start_dt\",\n    \"deferral_end_dt\",\n\n    \"deferral_reason_id\",\n    \"deferral_reason.name\",\n    \"deferral_reason.code\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"id\": {{id}}\n  },\n  \"$withRelated\": [\n    \"sales_channel(notDeleted)\",\n    \"club(notDeleted)\",\n    \"deferral_reason(notDeleted)\"\n  ]\n}",
    "queryParams": {}
  },
  "userParams": {
    "id": ""
  }
}

Notes

• The bodyParams field contains a JSON string that gets parsed at runtime, allowing for complex query structures
• The notDeleted scope in relationships ensures only active (non-soft-deleted) related records are returned
• Field names can include dot notation (e.g., "sales_channel.name") to access nested relationship properties
• The empty userParams.id will be populated at runtime with the actual subscription ID
• This pattern can be adapted for any entity type by changing the path and field selections
• Performance consideration: Be selective with fields in $select to minimize response size, especially for frequently-called queries