Item Query Reference Guide

Note: Operator and parameter reference. For patterns and end-to-end examples, see Advanced Query Guide.

← Back to Documentation Home

This guide provides detailed information about all supported query parameters, wildcards, dynamic variables, and other options for querying items in BAASIX.

Table of Contents

1. Query Parameters
2. Filtering
3. Sorting
4. Pagination
5. Field Selection
6. Search
7. Aggregation
8. Grouping
9. Wildcards
10. Dynamic Variables
11. Relational Queries
12. Relational Conditions (relConditions)
13. Geospatial Queries

Query Parameters

Filtering

The filter parameter supports complex conditions using a JSON object.

Operators

• Comparison: eq, ne, gt, gte, lt, lte
• Logical: AND, OR
• Array: in, notIn, arraycontains, arraycontained
• String: like (auto-adds %), iLike (auto-adds %), startsWith, endsWith
• Null: isNull, isNotNull
• Range: between
• Geospatial: within, containsGEO, intersects, dwithin
• Column Comparisons: Use $COL(columnName) to compare against other fields

Column-to-Column Comparisons

Use $COL(columnName) to compare field values against other field values:

# Compare actual vs estimated cost
?filter={"actualCost":{"gt":"$COL(estimatedCost)"}}

# Compare with type casting
?filter={"startTime":{"gt":"$COL(endTime)","cast":"time"}}

# PostgreSQL casting syntax in column reference
?filter={"startTime":{"gt":"$COL(endTime::time)","cast":"time"}}

Examples

1. Simple equality:

   ?filter={"status":{"eq":"active"}}

2. Multiple conditions (AND):

   ?filter={"status":{"eq":"active"},"age":{"gt":30}}

3. Logical OR:

   ?filter={"OR":[{"status":{"eq":"active"}},{"age":{"gt":30}}]}

4. Nested AND/OR:

   ?filter={"AND":[{"OR":[{"status":{"eq":"active"}},{"status":{"eq":"pending"}}]},{"age":{"gt":30}}]}

5. In array:

   ?filter={"status":{"in":["active","pending"]}}

6. String matching (auto-adds % wildcards):

   ?filter={"name":{"like":"John"}}

7. Null check:

   ?filter={"deletedAt":{"isNull":true}}

8. Range:

   ?filter={"createdAt":{"between":["2023-01-01","2023-12-31"]}}

Sorting

The sort parameter accepts a JSON object specifying fields and directions.

Examples

1. Single field ascending:

   ?sort={"name":"asc"}

2. Multiple fields:

   ?sort={"status":"asc","createdAt":"desc"}

3. Sort by distance (for geospatial fields):

   ?sort={"_distance":{"target":[0,0],"column":"location","direction":"ASC"}}

Pagination

Use limit and page parameters for pagination.

Example:

?limit=20&page=2

Field Selection

Use the fields parameter to specify which fields to return. BAASIX supports powerful wildcard patterns for field selection.

Basic Field Selection

Select specific fields by name:

?fields=id,name,email

Wildcard Patterns

Examples

1. Select all direct fields:

   ?fields=*

   Returns: id, name, email, createdAt, etc. (all columns of the collection)

2. Select specific fields:

   ?fields=id,name,email

   Returns only the specified fields

3. Select all fields of a relation (one level):

   ?fields=id,name,author.*

   Returns: id, name, and all fields of the author relation (author.id, author.name, author.email, etc.)

4. Select specific fields of a relation:

   ?fields=id,name,author.id,author.name

   Returns only the specified fields from the main collection and the relation

5. Deep nesting with specific fields:

   ?fields=id,title,author.name,author.role.name

   Returns: id, title, author.name, and author.role.name

6. Select all nested fields recursively:

   ?fields=id,name,posts.*.*

   Returns: id, name, and ALL fields from posts including all its nested relations (e.g., posts.id, posts.title, posts.comments.id, posts.comments.author.name, etc.)

7. Select all direct fields plus all relation fields (one level):

   ?fields=*.*

   Returns: All direct fields + all fields from all direct relations

8. Full tree expansion:

   ?fields=*.*.*

   Returns: Everything - all fields at all nesting levels

Field Exclusion

You can exclude specific fields using the - prefix:

?fields=*,-password,-secretKey

Returns all fields except password and secretKey.

Search

Use search for full-text search and searchFields to specify which fields to search in.

Example:

?search=John&searchFields=name,email

Aggregation

The aggregate parameter allows for complex calculations.

Supported Functions

• count
• sum
• avg
• min
• max

Examples

1. Single aggregation:

   ?aggregate={"totalUsers":{"function":"count","field":"id"}}

2. Multiple aggregations:

   ?aggregate={"totalUsers":{"function":"count","field":"id"},"avgAge":{"function":"avg","field":"age"}}

Grouping

Use groupBy in combination with aggregate for grouped calculations.

Example:

?groupBy=status&aggregate={"userCount":{"function":"count","field":"id"}}

Wildcards

See Field Selection for comprehensive wildcard pattern documentation.

Quick Reference:

Dynamic Variables

Dynamic variables can be used in filters to reference properties of the authenticated user.

Available Variables

• $CURRENT_USER - Current user ID
• $CURRENT_TENANT - Current tenant ID
• $USER_ROLE - Current user role
• $NOW - Current timestamp

Examples

1. Filter by current user:

   ?filter={"authorId":{"eq":"$CURRENT_USER"}}

2. Filter by current tenant (multi-tenant mode):

   ?filter={"tenant_Id":{"eq":"$CURRENT_TENANT"}}

3. Filter by current timestamp:

   ?filter={"publishedAt":{"lte":"$NOW"}}

Relational Queries

You can query related data using dot notation in filters and field selection.

Examples

1. Filter by related field:

   ?filter={"author.name":{"eq":"John Doe"}}

2. Select fields from related entities:

   ?fields=id,title,author.name,author.email

3. Filter with nested relations:

   ?filter={"posts.comments.author.id":{"eq":"$CURRENT_USER"}}

4. Require related entities:

   ?filter={"comments":{"required":true}}

Relational Conditions (relConditions)

The relConditions parameter is a deep filter for filtering within relations that return arrays. When you query records with O2M (One-to-Many) or M2M (Many-to-Many) relations, those relations return as arrays. While regular filter filters the main records, relConditions filters what appears inside those arrays.

When to Use

Basic Syntax

?relConditions={"relationName":{"fieldName":{"operator":"value"}}}

Note: The parameter is always relConditions (plural).

How It Works

• filter: Filters the main records (e.g., which posts to return)
• relConditions: Filters within the relation arrays (e.g., which comments appear in each post's comments array)

Examples

1. Filter comments array within posts:

   ?fields=*,comments.*
   &relConditions={"comments":{"isApproved":{"eq":true}}}

   Returns all posts, but each post's comments array only contains approved comments.

2. Filter tags array within posts (M2M):

   ?fields=*,tags.*
   &relConditions={"tags":{"status":{"eq":"active"}}}

   Returns all posts, but each post's tags array only contains active tags.

3. Multiple conditions on array items:

   ?fields=*,orders.*
   &relConditions={"orders":{"status":{"eq":"completed"},"total":{"gte":100}}}

   Returns all users, but each user's orders array only contains completed orders over $100.

4. Nested relConditions (array within array):

   ?fields=*,posts.*,posts.comments.*
   &relConditions={"posts":{"status":{"eq":"published"},"comments":{"isApproved":{"eq":true}}}}

   Returns users, with each user's posts array containing only published posts, and each post's comments array containing only approved comments.

5. Using dynamic variables:

   ?fields=*,tasks.*
   &relConditions={"tasks":{"assignedTo":{"eq":"$CURRENT_USER"}}}

   Returns all projects, but each project's tasks array only contains tasks assigned to the current user.

6. Logical operators:

   ?fields=*,comments.*
   &relConditions={"comments":{"OR":[{"authorId":{"eq":"$CURRENT_USER"}},{"isPublic":{"eq":true}}]}}

   Each post's comments array contains only comments that are either by the current user OR public.

filter vs relConditions

GET /items/posts?fields=*,comments.*
  &filter={"status":{"eq":"published"}}
  &relConditions={"comments":{"isApproved":{"eq":true}}}

• filter={"status":{"eq":"published"}} → Only return published posts
• relConditions={"comments":{"isApproved":true}} → Within each post, only include approved comments in the array

Key Points

1. Regular filter can also be applied to array relations for filtering main records (e.g., "get posts that have at least one approved comment")
2. relConditions filters what items appear in the array that's returned with each record
3. Only applies to relations that return arrays (O2M and M2M)

Geospatial Queries

For fields of type Point, LineString, Polygon, or Geography, you can use geospatial operators.

Operators

• within
• containsGEO
• intersects
• dwithin

Examples

1. Points within a polygon:

   ?filter={"location":{"within":{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}}}

2. Polygon contains point:

   ?filter={"area":{"containsGEO":{"type":"Point","coordinates":[0,0]}}}

3. Intersecting geometries:

   ?filter={"route":{"intersects":{"type":"LineString","coordinates":[[0,0],[1,1]]}}}

4. Distance within:

   ?filter={"location":{"dwithin":{"geometry":{"type":"Point","coordinates":[0,0]},"distance":1000}}}

5. Sort by distance:

   ?sort={"_distance":{"target":[0,0],"column":"location","direction":"ASC"}}

This reference guide covers all the querying capabilities of the BAASIX Item Routes, including advanced filtering, sorting, pagination, field selection, search, aggregation, grouping, wildcards, dynamic variables, relational queries, and geospatial queries. Use these options in combination to create powerful and flexible queries for your data.

Related Documentation

• Item Routes - API endpoints for data operations
• Schema Reference Guide - Data model definitions
• API Routes Reference - Complete list of all API endpoints

← Back to Documentation Home