Skip to main content

Filtered List Query with Pagination and Relationships

Description​

This query demonstrates a common pattern for retrieving filtered lists of records with pagination support and selective field loading. It showcases how Stack9 handles list-based queries where records need to be filtered by a foreign key relationship while also loading related data for display purposes. The query uses the POST method to a search endpoint, which provides more flexibility than GET for complex filtering scenarios.

The pattern shown here is essential for building user interfaces that display related records, such as showing all job applications for a specific applicant. The query efficiently retrieves only the necessary fields through the $select array, loads the related job posting information via $withRelated, and supports pagination through query parameters. This approach balances performance with functionality by fetching just enough data for list displays while avoiding over-fetching.

The combination of field selection, relationship loading, and pagination parameters demonstrates Stack9's approach to scalable data retrieval. By using page and limit query parameters, the API can handle large datasets efficiently, returning results in manageable chunks. The inclusion of related entity fields (like job_posting.title) in the select list shows how denormalized data can be retrieved for display without requiring additional API calls.

Use Case​

Used in applicant portals to display a paginated list of all job applications submitted by a specific user, showing the application status and related job posting information in a table or card view.

Key Features​

  • Filtered retrieval using WHERE clause with foreign key (applicant_id)
  • Pagination support through page and limit query parameters
  • Selective field loading with $select for optimized response size
  • Related entity loading via $withRelated for complete display data
  • Nested field selection using dot notation (job_posting.title)
  • RESTful search endpoint pattern using POST for complex queries

Query Type​

SELECT

JSON Definition​

{
  "key": "getjobapplicationsbyapplicantid",
  "name": "getJobApplicationsByApplicantId",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/job_application/search",
    "bodyParams": "{\n  \"$select\": [\n\t\t\"id\",\n    \"job_posting_id\",\n    \"job_posting.title\",\n    \"status\",\n\t\t\"applicant_id\"\n\t],\n  \"$withRelated\": [\"job_posting\"],\n\t\"$where\": {\n  \t\"applicant_id\": {{id}}\n  }\n}",
    "queryParams": {
      "page": "{{page}}",
      "limit": "{{limit}}"
    }
  },
  "userParams": {}
}

Notes​

  • The page and limit parameters enable cursor-based pagination for large result sets
  • The parameter name {{id}} actually refers to the applicant_id, showing how parameters can be aliased
  • Including both job_posting_id and job_posting.title allows for efficient rendering without additional lookups
  • The search endpoint pattern (POST to /search) is more flexible than GET for complex filtering
  • Default pagination values are typically handled by the API if page/limit aren't provided
  • This pattern can be extended with additional WHERE conditions for more complex filtering
  • Consider adding sorting parameters ($sort) for consistent result ordering across pages
  • The response will typically include metadata about total records and pages for pagination UI components