Entity Service CRUD reference

Table of Contents

1. Overview
2. Core Types
3. Entity Service Implementation
4. CRUD Operations
5. Best Practices
6. Analytics and Aggregation
7. Advanced Query Techniques

Overview

The Pikbase platform provides a framework for implementing CRUD (Create, Read, Update, Delete) operations for entities. This guide covers the implementation details and best practices for working with GSB.

Core Types

Request Types

// Query Parameters
interface QueryParams<T> {
  propertyName?: string; // Property name for reference when used as include
  entity?: T | null; // Entity object for reference or template
  id?: string; // ID for single entity retrieval
  entityId?: string; // Alternative ID for single entity retrieval
  entityDef?: GsbEntityDef; // Entity definition object
  selectCols?: SelectCol[]; // Columns to select/include in response
  includes?: IncludeQuery<any>[]; // Related entities to include
  filters?: Filter[]; // Query filters
  startIndex?: number; // Pagination start index
  count?: number; // Pagination count
  sortCols?: SortCol[]; // Sort columns
  searchText?: any; // Text to search across searchable columns
  calcTotalCount?: boolean; // Whether to calculate total count
  queryType?: QueryType; // Type of query to execute
  mapColName?: string; // Column name for mapping
  disableTransaction?: boolean; // Whether to disable transaction

  // Getters/Setters
  get refColName(): string; // Gets mapColName
  set refColName(v: string); // Sets mapColName

  get entDefId(): string; // Gets entity definition ID
  set entDefId(v: string); // Sets entity definition ID

  get entDefName(): string; // Gets entity definition name
  set entDefName(v: string); // Sets entity definition name

  // Methods
  type(queryType: QueryType): QueryParams<T>;
  take(count: number): QueryParams<T>;
  limit(count: number): QueryParams<T>;
  skip(count: number): QueryParams<T>;
  filter<P = any>(
    predicate: string | Filter | ((item: T) => P),
    value?: any,
    queryFunction?: QueryFunction,
    relation?: QueryRelation,
  ): QueryParams<T>;
  select(
    col: string | string[] | ((item: T) => any),
    options?: SelectCol,
  ): QueryParams<T>;
  include<R extends object = T>(
    ...colNames: (string | ((item: T) => any) | IncludeQuery<any>)[]
  ): { self: QueryParams<T>; inc: IncludeQuery<R> | null };
  sortBy(col: ((item: T) => any) | string, sortType: string): QueryParams<T>;
}

// Save Request
interface GsbSaveRequest {
  entDefName?: string; // Entity definition name
  entDefId?: string; // Entity definition ID
  entityDef?: Record<string, any>; // Entity definition object
  entity?: any; // The entity data to save
  filters?: any[]; // Filters for bulk operations
  entityId?: string; // Entity ID for update/delete
}

// Bulk Request
interface GsbBulkCallRequest {
  endPoint: string; // API endpoint to call
  request: any; // Request data
  reqId?: string; // Request ID for correlation
}

interface GsbBulkRequest {
  bulkCalls: GsbBulkCallRequest[]; // Array of calls to make in bulk
  variation?: {
    appCode?: string;
    langCode?: string;
    tenantCode: string;
    sessionId?: string;
    screen?: number;
    provider?: string;
  };
}

// Filter Support
interface Filter {
  col?: SelectCol; // Column to filter on
  val?: SelectCol; // Value to compare with
  relationLevel?: number; // Nesting level for grouping
  children?: Filter[]; // Nested conditions
  relation?: QueryRelation; // Logical operator (AND/OR) for children
  name?: string; // Name for the filter
  negate?: boolean; // Whether to negate the condition

  // Helper methods
  aggregate(func: AggregateFunction): Filter;
  groupBy(value?: boolean): Filter;
  isEqual(value: any): Filter;
  isLike(value: any): Filter;
  isGreater(value: any): Filter;
  isSmaller(value: any): Filter;
  contains(value: any): Filter;
  bitwiseAnd(value: any): Filter;
  bitwiseOr(value: any): Filter;
  bitwiseXor(value: any): Filter;
  in(value: any): Filter;
  fullTextSeach(value: any): Filter;
  is(value: any): Filter;
  not(): Filter;
  funcVal(queryFunction: QueryFunction, value: any): Filter;
}

Response Types

interface GsbQueryResponse {
  message?: string; // Response message
  status?: number; // Response status code
  entities?: any[]; // Array of entities
  entity?: any; // Single entity
  totalCount?: number; // Total count of matching entities
  success?: boolean; // Whether the operation was successful
}

interface GsbSaveResponse {
  message?: string; // Response message
  status?: number; // Response status code
  id?: string; // ID of the saved entity
  success?: boolean; // Whether the operation was successful
  isUpdate?: boolean; // Whether this was an update (true) or create (false) operation
}

interface GsbSaveMultiResponse {
  message?: string; // Response message
  status?: number; // Response status code
  ids?: string[]; // IDs of saved entities
}

interface GsbQueryOpResponse {
  message?: string; // Response message
  status?: number; // Response status code
  affectedRowCount?: number; // Number of affected rows
  deleteCount?: any; // Number of deleted rows
}

interface GsbBulkCallResult {
  reqId: string; // Request ID
  response: string; // Response data
}

interface GsbBulkResponse {
  results: GsbBulkCallResult[]; // Results of bulk calls
  status: number; // Overall status
  message?: string; // Response message
}

Entity Service Implementation

Basic Entity Service

class GsbEntityService {
  private apiService: GsbApiService;
  private useCache: boolean;

  constructor(useCache: boolean = true) {
    this.apiService = GsbApiService.getInstance();
    this.useCache = useCache;
  }

  // Static method to get the singleton instance
  static getInstance(useCache: boolean = true): GsbEntityService {
    if (!serviceInstance) {
      serviceInstance = new GsbEntityService(useCache);
    }
    return serviceInstance;
  }

  // Get entity by ID
  async getById<T extends object>(
    definitionType: (new () => T) | string,
    id: string,
    token?: string,
    tenantCode?: string,
  ): Promise<T | null> {
    const req = new QueryParams<any>(definitionType);
    req.entityId = id;
    const result = await this.get(req, token, tenantCode);
    return result.entity as T;
  }

  // Get entity copy (without ID)
  async getCopy<T extends object>(
    definitionType: (new () => T) | string,
    id: string,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryResponse | null> {
    const entity = await this.getById(definitionType, id, token, tenantCode);
    if (entity) {
      delete (entity as any).id;
    }
    return entity;
  }

  // Get a single entity
  async get(
    req: QueryParams<any>,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryResponse> {
    // Implementation
  }

  // Query entities
  async query(
    req: QueryParams<any>,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryResponse> {
    // Implementation
  }

  // Query with mapped results
  async queryMapped(
    req: QueryParams<any>,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryResponse> {
    // Implementation
  }

  // Save an entity directly
  async saveEnt(
    entity: any,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbSaveResponse> {
    // Implementation
  }

  // Save an entity with request
  async save(
    req: GsbSaveRequest,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbSaveResponse> {
    // Implementation
  }

  // Update via query
  async updateQuery(
    req: QueryParams<any>,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryOpResponse> {
    // Implementation
  }

  // Save multiple entities
  async saveMulti(
    req: GsbSaveMultiRequest,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbSaveMultiResponse> {
    // Implementation
  }

  // Execute bulk operations
  async executeBulk(
    bulkRequest: GsbBulkRequest,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbBulkResponse> {
    // Implementation
  }

  // Delete an entity
  async delete(
    req: GsbSaveRequest,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryOpResponse> {
    // Implementation
  }

  // Delete via query
  async deleteQuery(
    req: QueryParams<any>,
    token?: string,
    tenantCode?: string,
  ): Promise<GsbQueryOpResponse> {
    // Implementation
  }

  // Additional methods for entity definition and workflow
  async getDefinition(
    req: { entityDef: { id?: string; name?: string } },
    token?: string,
    tenantCode?: string,
  ): Promise<GsbDefinitionResponse> {
    // Implementation
  }

  // Workflow related methods
  async runWorkflow(
    req: any,
    token?: string,
    tenantCode?: string,
  ): Promise<any> {
    // Implementation
  }

  async startWorkflow(
    req: any,
    token?: string,
    tenantCode?: string,
  ): Promise<any> {
    // Implementation
  }

  async runWfFunction(
    req: any,
    token?: string,
    tenantCode?: string,
  ): Promise<any> {
    // Implementation
  }

  async iterateTask(
    req: any,
    token?: string,
    tenantCode?: string,
  ): Promise<any> {
    // Implementation
  }
}

CRUD Operations

Create

// Example: Creating a new entity
const saveRequest: GsbSaveRequest = {
  entDefName: "test", // Only one of entDefName, entDefId, or entityDef is required
  entity: {
    title: "New Entity",
  },
};

const response = await entityService.save(saveRequest, token, tenant);
// Response will contain:
// - message: string (optional)
// - status: number (optional)
// - id: string (optional) - The ID of the created entity
// - isUpdate: boolean (optional) - Whether this was an update (true) or create (false) operation

// Alternative: Using saveEnt for simpler saves
const entity = { _entDefName: "test", title: "New Entity" };
const response = await entityService.saveEnt(entity, token, tenant);

Save Operation Details

The save operation creates a new entity or updates an existing one in the database. When saving an entity without an ID, a new entity is created, and an ID is automatically generated. When saving an entity with an existing ID, the entity is updated.

The operation validates the entity against its definition before saving, ensuring data integrity. It also supports complex JSON structures with nested objects and arrays, automatically handling relationships between entities.

Save Input Parameters

Parameter	Type	Required	Description
entDefName	string	Yes*	Name of the entity definition. Required if entDefId is not provided.
entDefId	string	Yes*	ID of the entity definition. Required if entDefName is not provided.
entityDef	object	No	Optional entity definition object with id and/or name properties.
entity	object	Yes	The entity object to save, which can include nested objects and arrays for related entities.
token	string	No	Authentication token for your request.
tenantCode	string	No	Tenant code to specify which tenant's data to access.

Save Response

{
    "success": true,
    "id": "string",  // ID of the created or updated entity
    "isUpdate": boolean,  // Whether this was an update (true) or create (false) operation
    "message": "string",  // Optional success message
    "status": 200  // Optional status code
}

Complex Data Handling

The save operation intelligently processes nested objects and arrays:

• For each nested entity:
  • If an ID is provided and exists in the database, the entity will be updated.
  • If no ID is provided or the ID doesn't exist, a new entity will be created.
• Relationships between entities are automatically maintained.
• One-to-many and many-to-many relationships are handled through arrays of objects.
• One-to-one relationships are handled through nested objects.
• All operations are performed in a single transaction, ensuring data consistency.

Read

// Example: Querying entities with fluent API
const queryParams = new QueryParams<any>("test")
  .filter("title", "Test", QueryFunction.Like)
  .skip(0)
  .take(10)
  .select("id")
  .select("title")
  .sortBy("createdDate", QuerySortType.Descending);

const response = await entityService.query(queryParams, token, tenant);
// Response will contain:
// - message: string (optional)
// - status: number (optional)
// - entities: any[] (optional) - Array of matching entities
// - totalCount: number (optional) - If calcTotalCount was true

// Getting entity by ID
const entity = await entityService.getById("test", "entity-id", token, tenant);

// Getting entity by ID with constructor type
class TestEntity {
  _entDefName = "test";
  id?: string;
  title?: string;
}
const typedEntity = await entityService.getById(
  TestEntity,
  "entity-id",
  token,
  tenant,
);

Query Operation Details

The query operation fetches entities based on specified query parameters, allowing for complex filtering, sorting, pagination, and analytical queries. It provides a powerful and flexible way to search and retrieve entities based on various criteria.

Query Input Parameters

Parameter	Type	Required	Description
entDefName	string	Yes*	Name of the entity definition to query. Required if entDefId is not provided.
entDefId	string	Yes*	ID of the entity definition to query. Required if entDefName is not provided.
entityDef	object	No	Optional entity definition object with id and/or name properties.
selectCols	array	No	Columns to select in the query. If not provided, all columns are selected.
includes	array	No	Related entities to include in the results.
filters	array	No	Array of filter conditions to filter the results.
startIndex	number	No	Pagination start index (0-based).
count	number	No	Number of records to return.
sortCols	array	No	Sorting specifications for the results.
calcTotalCount	boolean	No	Whether to calculate the total count of matching records.
searchText	string	No	Search term for automatic searching across all searchable fields.

Query Filter Functions

The query system supports various functions for filtering entities:

Function	Description	Example
equals	Exact value match	{ col: { name: "status" }, val: "active", function: "equals" }
like	Pattern matching	{ col: { name: "name" }, val: "John%", function: "like" }
greater	Greater than comparison	{ col: { name: "price" }, val: 100, function: "greater" }
smaller	Smaller than comparison	{ col: { name: "quantity" }, val: 50, function: "smaller" }
contains	Array / multi-reference membership (not substring)	{ col: { name: "roles" }, val: ["role-id"], function: "contains" }
in	Value is in a set; on a reference use the dotted id path	{ col: { name: "roles.id" }, val: ["role-id"], function: "in" }
fullTextSearch	Full text search	{ col: { name: "description" }, val: "search terms", function: "fullTextSearch" }
is	Type checking	{ col: { name: "createDate" }, val: null, function: "is" }

Val Object Structure

The val property in filters can be either a direct value or an object with additional properties:

interface ValObject {
  value?: any; // The actual value to use in the comparison
  script?: string; // A script to execute to determine the value dynamically
  nameScript?: string; // A script that returns a display name for the value
  options?: object; // Additional options for the filter
}

Query Response

{
  "success": true,
  "entities": [
    // Array of matching entities
    {
      "id": "string",
      "property1": "value1",
      "property2": "value2"
      // ...
    }
  ],
  "totalCount": 42, // Present only if calcTotalCount is true
  "message": "string", // Optional message
  "status": 200 // Optional status code
}

For analytical queries, the response might include aggregated results:

{
  "success": true,
  "entities": [
    // Array of aggregated results
    {
      "groupByField": "value",
      "count": 42,
      "sum_amount": 1250.75,
      "avg_price": 29.99
      // Additional aggregates as requested
    }
  ],
  "totalCount": 5, // Number of result groups
  "message": "string", // Optional message
  "status": 200 // Optional status code
}

Update

// Example: Updating an existing entity
const updateRequest: GsbSaveRequest = {
  entDefName: "test",
  entity: {
    id: "existing-id",
    title: "Updated Title",
  },
};

const response = await entityService.save(updateRequest, token, tenant);
// Response will contain:
// - message: string (optional)
// - status: number (optional)
// - id: string (optional) - The ID of the updated entity

// Alternative: Using updateQuery for bulk updates
const updateQueryParams = new QueryParams<any>("test").filter(
  "status",
  "pending",
  QueryFunction.Equals,
);

updateQueryParams.entity = {
  status: "approved",
  updatedDate: new Date(),
};

const updateResponse = await entityService.updateQuery(
  updateQueryParams,
  token,
  tenant,
);
// updateResponse.affectedRowCount shows how many records were updated

Delete

// Example: Deleting an entity
const deleteRequest: GsbSaveRequest = {
  entDefName: "test",
  entityId: "entity-to-delete",
};

const response = await entityService.delete(deleteRequest, token, tenant);
// Response will contain:
// - message: string (optional)
// - status: number (optional)
// - affectedRowCount: number (optional) - Number of deleted records
// Single deletes execute cascade rules and workflow/serverless delete triggers.

// Direct bulk delete: does not execute cascades or workflow/serverless delete triggers
const deleteQueryParams = new QueryParams<any>("test").filter(
  "status",
  "cancelled",
  QueryFunction.Equals,
);

const deleteResponse = await entityService.deleteQuery(
  deleteQueryParams,
  token,
  tenant,
);
// deleteResponse.affectedRowCount shows how many records were deleted

Bulk Operations

// Example: Executing multiple operations in a single request
const bulkRequest: GsbBulkRequest = {
  bulkCalls: [
    {
      endPoint: "/api/entity/save",
      request: {
        entDefName: "test",
        entity: { title: "First Entity" },
      },
      reqId: "save1",
    },
    {
      endPoint: "/api/entity/save",
      request: {
        entDefName: "test",
        entity: { title: "Second Entity" },
      },
      reqId: "save2",
    },
    {
      endPoint: "/api/entity/query",
      request: {
        entDefName: "test",
        startIndex: 0,
        count: 5,
      },
      reqId: "query1",
    },
  ],
  variation: {
    tenantCode: "default",
  },
};

const bulkResponse = await entityService.executeBulk(
  bulkRequest,
  token,
  "default",
);
// bulkResponse.results contains individual responses keyed by reqId

Best Practices

1. Entity Definition Identification

   • Use entDefName when you know the entity definition name
   • Use entDefId when you have the entity definition ID
   • Use entityDef object only when it has either name or id property set
   • For type safety, prefer using class constructors when possible: new QueryParams(MyEntity)

2. Query Operations

   • Use the fluent API for readable, chainable query building
   • Always specify pagination parameters (skip() and take()/limit()) for large datasets
   • Use appropriate query functions for filtering
   • Include only necessary fields in the response using select()
   • For search operations, use the searchText parameter for automatic searching across searchable fields
   • For complex logic, use the Filter class with children and relations
   • Set calcTotalCount to true only when pagination controls require total count
   • Use col and val properties in filter objects rather than older property/value naming
   • For complex filters, leverage the ValObject structure with scripts for dynamic values

3. Save Operations

   • Always include the entity ID when updating existing entities
   • Use saveEnt() for simple entity saves when the entity contains _entDefName
   • Use saveMulti() for batch entity creations/updates
   • Use updateQuery() for bulk updates based on a query filter
   • Validate required fields before sending the request
   • Check the isUpdate flag in responses to determine if an entity was created or updated
   • For complex nested data structures, ensure proper relationships are defined
   • When saving related entities:
     • Provide IDs for existing entities to update them
     • Omit IDs for new entities to create them
   • All save operations are transactional, ensuring data consistency

4. Delete Operations

   • Verify entity existence before deletion

• Use deleteQuery() only when conditional bulk deletion does not require cascades or workflow/serverless delete triggers
• When lifecycle behavior is required, query the matching IDs and batch individual delete() requests
• Check affectedRowCount to confirm deletion

5. Error Handling

   • Always check response success flag
   • Handle error messages appropriately
   • Implement proper error recovery mechanisms
   • Validate inputs before sending requests to avoid validation errors

6. Security

   • Always include valid authentication token
   • Always include tenant information
   • Implement proper access control

7. Performance

   • Use pagination for large datasets (skip() and take())
   • Use the singleton instance of GsbEntityService for better resource utilization
   • Set appropriate disableTransaction for read-only operations
   • Consider using executeBulk() for multiple related operations
   • For analytical queries, apply filters before aggregations to reduce processing load
   • Use appropriate indices on frequently queried columns

8. Complex Data Handling

   • For nested object structures, understand the relationship types:
     • One-to-one: Represented as nested objects
     • One-to-many/Many-to-many: Represented as arrays of objects
   • GSB automatically processes relationships based on entity definitions
   • All operations within a complex save are performed in a single transaction
   • If any part of a complex save fails, the entire transaction is rolled back
   • For large nested structures, consider breaking operations into smaller transactions

Analytics and Aggregation

SelectCol Configuration for Analytics

interface SelectCol {
  name?: string; // Column name
  aggregateFunction?: AggregateFunction; // Aggregation function
  dateModifier?: DateModifier; // Date grouping modifier
  groupBy?: boolean; // Whether to group by this column
  script?: string; // Dynamic calculation script
  fullName?: string; // Full column name
  title?: string; // Display title
  value?: any; // Static value
  nameScript?: string; // Dynamic name script
  valScript?: string; // Dynamic value script
  selectAsTitle?: string; // Alias for result
}

// Available Aggregate Functions
enum AggregateFunction {
  None = 0,
  Sum = 1,
  Average = 2,
  Count = 3,
  Maximum = 4,
  Minimum = 5,
  Variance = 6,
}

// Available Date Modifiers
enum DateModifier {
  None = 0,
  Year = 1,
  Quarter = 2,
  Month = 3,
  DayOfYear = 4,
  DayOfMonth = 5,
  Week = 6,
  Weekday = 7,
  Hour = 8,
  Minute = 9,
  Second = 10,
  Millisecond = 11,
}

Column Scripts and Calculations

GSB supports dynamic column calculations and transformations using script expressions:

interface SelectCol {
  // ... other properties
  script?: string; // Dynamic calculation for column value
  nameScript?: string; // Dynamic calculation for column name
  valScript?: string; // Dynamic calculation for filter value
}

Using Script for Column Calculations

The script property allows you to define complex calculations and transformations:

// Calculate total price as quantity * unit price
const totalPriceCol = new SelectCol();
totalPriceCol.script =
  "[quantity] * [_COALESCE]([productVariant.product.unitPrice],[productVariant.unitPrice])";
totalPriceCol.selectAsTitle = "totalPrice";

Date Modifiers

Date modifiers allow grouping date fields by different time periods. When used, they append a suffix to the column name in the response:

• Year → columnName_Year (groups by year)
• Quarter → columnName_Quarter (groups by quarter)
• Month → columnName_Month (groups by month)
• DayOfYear → columnName_DayOfYear (groups by day of year)
• DayOfMonth → columnName_DayOfMonth (groups by day of month)
• Week → columnName_Week (groups by week)
• Weekday → columnName_Weekday (groups by day of week)
• Hour → columnName_Hour (groups by hour)
• Minute → columnName_Minute (groups by minute)
• Second → columnName_Second (groups by second)
• Millisecond → columnName_Millisecond (groups by millisecond)

Analytics Query Examples

Example 1: Count by Date and Type

// Query to count logs grouped by date and type
const query = new QueryParams<SystemLog>("GsbSystemLog");

// Configure date column for grouping by day
let dateCol = new SelectCol("createDate");
dateCol.groupBy = true;
dateCol.dateModifier = DateModifier.DayOfMonth;  // Results in createDate_Day in response

// Configure count aggregation
let countCol = new SelectCol("id");
countCol.aggregateFunction = AggregateFunction.Count;
countCol.selectAsTitle = "total";  // Renames the count column to 'total'

// Configure type column for grouping
let typeCol = new SelectCol("type");
typeCol.groupBy = true;

query.selectCols = [dateCol, countCol, typeCol];

// Example response:
{
  "entities": [
    {
      "createDate_Day": "2025-03-29T00:00:00",  // Note the _Day suffix
      "type": 2,
      "total": 2  // Count result with custom name from selectAsTitle
    },
    // ... more results
  ]
}

Example 2: Monthly Aggregation with Multiple Metrics

const query = new QueryParams<SystemLog>("GsbSystemLog");

// Group by month
let dateCol = new SelectCol("createDate");
dateCol.dateModifier = DateModifier.Month;
dateCol.groupBy = true;

// Count total occurrences
let countCol = new SelectCol("id");
countCol.aggregateFunction = AggregateFunction.Count;

// Average value of a numeric field
let avgCol = new SelectCol("value");
avgCol.aggregateFunction = AggregateFunction.Average;

query.selectCols = [dateCol, countCol, avgCol];

Advanced Query Techniques

Fluent Query Building

GSB provides a fluent API for building queries that is type-safe and readable:

// Type-safe query using a class constructor
class Product {
  _entDefName = "Product";
  id?: string;
  name?: string;
  price?: number;
  category?: string;
}

const query = new QueryParams<Product>(Product)
  .filter((p) => p.category, "Electronics")
  .filter((p) => p.price, 100, QueryFunction.Greater)
  .select((p) => p.id)
  .select((p) => p.name)
  .select((p) => p.price)
  .sortBy((p) => p.price, QuerySortType.Descending)
  .skip(0)
  .take(10);

const response = await entityService.query(query, token, tenant);

Including Related Entities

You can include related entities in a single query:

// Fetch orders with their related customer and items
const query = new QueryParams<Order>("Order")
  .filter("status", "processing")
  .take(10);

// Include customer data
const customerInclude = query.include("customer").inc;
customerInclude?.select("id").select("name").select("email");

// Include order items with their products
const itemsInclude = query.include("items").inc;
const productInclude = itemsInclude?.include("product").inc;
productInclude?.select("id").select("name").select("price");

const response = await entityService.query(query, token, tenant);

Complex Filtering

Filters form an unlimited AND/OR tree. Two rules:

1. relation says how a filter joins to the filter before it, and defaults to And. It belongs on the right-hand operand.
2. children acts as a parenthesis. A group's own relation joins the group to its preceding sibling — it does not combine the group's children.

// (status = active OR status = pending) AND (value > 1000 AND priority = high)
const activeFilter = new Filter("status", "active", QueryFunction.Equals);
const pendingFilter = new Filter("status", "pending", QueryFunction.Equals);
// The Or rides on the second operand, not on the group.
pendingFilter.relation = QueryRelation.Or;

const highValueFilter = new Filter("value", 1000, QueryFunction.Greater);
const priorityFilter = new Filter("priority", "high", QueryFunction.Equals);

const statusGroup = new Filter();
statusGroup.children = [activeFilter, pendingFilter];

const importanceGroup = new Filter();
importanceGroup.children = [highValueFilter, priorityFilter];
// Joins this group to statusGroup; children stay ANDed by default.
importanceGroup.relation = QueryRelation.And;

const query = new QueryParams<any>("Task");
query.filters = [statusGroup, importanceGroup];

Setting statusGroup.relation = QueryRelation.Or and expecting its two children to OR together is the most common mistake — they stay ANDed, and the query usually returns nothing.

negate inverts a single filter. It is ignored on a children group; to negate a group, invert the operators inside it.

Best Practices for Advanced Queries

1. Data Transformation

   • Use selectAsTitle to provide meaningful aliases for computed or aggregated columns
   • Consider data transformation for visualization needs by constructing the response in a format suitable for charts or UI components
   • For complex transformations, use scripts with column calculations

2. Performance Optimization

   • Apply query filters first to reduce the dataset before applying aggregations
   • Use appropriate indices on frequently queried columns
   • Consider denormalization for commonly accessed aggregated data
   • For large datasets, use pagination and incremental loading

3. Error Handling

   • Validate all filter values before sending queries
   • Handle null/undefined values in transformations
   • Provide appropriate fallbacks for missing data points

4. Bulk Operations

   • Use bulk operations for related entity updates
   • Consider transaction boundaries for operations that need to be atomic
   • Use save/update queries for performing mass updates efficiently

5. Security

   • Be aware that all column scripts and queries are subject to permission checks
   • Test security with users having different permission levels
   • Don't rely on client-side filtering for security - always enforce server-side