Querying and Filtering Data

Learn how to retrieve, filter, sort, and paginate your data using powerful query capabilities.

Basic Querying

Get All Records

Retrieve all records from a table:

GET /api/apps/{app-slug}/datatables/{table-name}/data/

Example:

GET /api/apps/blog-app/datatables/users/data/

Response:

{
  "status": "success",
  "message": "Data retrieved successfully",
  "data": [
    {
      "id": 1,
      "username": "john_doe",
      "email": "john@example.com",
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "id": 2,
      "username": "jane_smith",
      "email": "jane@example.com",
      "created_at": "2024-01-16T14:20:00Z"
    }
  ],
  "total": 2
}

Get Single Record

Retrieve a specific record by ID:

GET /api/apps/{app-slug}/datatables/{table-name}/data/{id}/

Example:

GET /api/apps/blog-app/datatables/users/data/1/

Response:

{
  "status": "success",
  "message": "Data retrieved successfully",
  "data": {
    "id": 1,
    "username": "john_doe",
    "email": "john@example.com",
    "bio": "Software developer",
    "created_at": "2024-01-15T10:30:00Z"
  }
}

Filtering

Filter records using query parameters with powerful operators.

Exact Match

REST API

GET /api/apps/blog-app/datatables/posts/data/?status=published

Python

# Exact match filter
posts = auth_client.database.from_("posts").filter("status", "eq", "published").execute()

print(f"Found {len(posts['data'])} published posts")

JavaScript

import { Database } from '@taruvi/sdk'

const database = new Database(client)

// Exact match filter
const posts = await database.from("posts")
    .filter({ status: "published" })
    .execute()

console.log(`Found ${posts.data.length} published posts`)

Comparison Operators

Operator	Description	Example
_gt	Greater than	?views_gt=100
_gte	Greater than or equal	?score_gte=80
_lt	Less than	?age_lt=30
_lte	Less than or equal	?price_lte=50
_ne	Not equal	?status_ne=draft

REST API

Examples:

# Posts with more than 100 views
GET /api/apps/blog-app/datatables/posts/data/?views_gt=100

# Products under $50
GET /api/apps/shop-app/datatables/products/data/?price_lte=50

# Users not from USA
GET /api/apps/crm-app/datatables/users/data/?country_ne=USA

Python

# Greater than filter
popular_posts = auth_client.database.from_("posts").filter("views", "gt", 100).execute()

# Less than or equal filter
affordable_products = auth_client.database.from_("products").filter("price", "lte", 50).execute()

# Not equal filter
non_us_users = auth_client.database.from_("users").filter("country", "ne", "USA").execute()

print(f"Popular posts: {len(popular_posts['data'])}")
print(f"Affordable products: {len(affordable_products['data'])}")
print(f"Non-US users: {len(non_us_users['data'])}")

JavaScript

import { Database } from '@taruvi/sdk'

const database = new Database(client)

// Greater than filter
const popularPosts = await database.from("posts")
    .filter({ views: { _gt: 100 } })
    .execute()

// Less than or equal filter
const affordableProducts = await database.from("products")
    .filter({ price: { _lte: 50 } })
    .execute()

// Not equal filter
const nonUSUsers = await database.from("users")
    .filter({ country: { _ne: "USA" } })
    .execute()

console.log(`Popular posts: ${popularPosts.data.length}`)
console.log(`Affordable products: ${affordableProducts.data.length}`)
console.log(`Non-US users: ${nonUSUsers.data.length}`)

IN Operators

Operator	Description	Case Sensitivity
_in	Value in list	Case-sensitive
_nin	Value not in list	Case-sensitive
_ina	Value in list	Case-insensitive
_nina	Value not in list	Case-insensitive

REST API

# Posts with specific statuses
GET /api/apps/blog-app/datatables/posts/data/?status_in=published,featured

# Exclude certain categories
GET /api/apps/blog-app/datatables/posts/data/?category_nin=spam,archived

# Case-insensitive search
GET /api/apps/blog-app/datatables/posts/data/?tag_ina=javascript,PYTHON,Ruby

Python

# Posts with specific statuses (IN operator)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('status', 'in', ['published', 'featured']) \
    .get()

# Exclude certain categories (NOT IN operator)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('category', 'nin', ['spam', 'archived']) \
    .get()

# Note: Case-insensitive IN (_ina, _nina) not supported in SDK
# Workaround: Fetch and filter client-side or use REST API directly

SDK Limitation: Case-insensitive IN operators (_ina, _nina) are not directly supported. Use in/nin for case-sensitive matching.

JavaScript

// Posts with specific statuses (IN operator)
const posts = await client.database
    .from('posts')
    .filter('status', 'in', ['published', 'featured'])
    .execute()

// Exclude certain categories (NOT IN operator)
const posts = await client.database
    .from('posts')
    .filter('category', 'nin', ['spam', 'archived'])
    .execute()

// Note: Case-insensitive IN (_ina, _nina) not supported in SDK
// Workaround: Fetch and filter client-side or use REST API directly

SDK Limitation: Case-insensitive IN operators (_ina, _nina) are not directly supported. Use in/nin for case-sensitive matching.

String Matching

Contains

Operator	Description	Case Sensitivity
_contains	Contains substring	Case-sensitive
_ncontains	Does not contain	Case-sensitive
_icontains	Contains substring	Case-insensitive
_nicontains	Does not contain	Case-insensitive

REST API

# Titles containing "django"
GET /api/apps/blog-app/datatables/posts/data/?title_contains=django

# Emails not containing "spam"
GET /api/apps/crm-app/datatables/users/data/?email_ncontains=spam

Python

# Titles containing "django" (case-sensitive)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('title', 'contains', 'django') \
    .get()

# Titles containing "django" (case-insensitive)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('title', 'icontains', 'django') \
    .get()

# Emails not containing "spam"
# Note: Negative contains operators not directly supported
# Workaround: Fetch and filter client-side
users = auth_client.database.from_('users', app_slug='crm-app').execute()
filtered = [u for u in users if 'spam' not in u.get('email', '').lower()]

JavaScript

// Titles containing "django" (case-sensitive)
const posts = await client.database
    .from('posts')
    .filter('title', 'contains', 'django')
    .execute()

// Titles containing "django" (case-insensitive)
const posts = await client.database
    .from('posts')
    .filter('title', 'icontains', 'django')
    .execute()

// Emails not containing "spam"
// Note: Negative contains operators not directly supported
// Workaround: Fetch and filter client-side
const users = await client.database.from_('users').execute()
const filtered = users.data.filter(u => !u.email?.toLowerCase().includes('spam'))

Starts With

Operator	Description	Case Sensitivity
_startswith	Starts with	Case-sensitive
_istartswith	Starts with	Case-insensitive

REST API

# Usernames starting with "admin"
GET /api/apps/blog-app/datatables/users/data/?username_startswith=admin

# Products not starting with "test"
GET /api/apps/shop-app/datatables/products/data/?sku_nstartswith=test

Python

# Usernames starting with "admin" (case-sensitive)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('username', 'startswith', 'admin') \
    .get()

# Usernames starting with "admin" (case-insensitive)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('username', 'istartswith', 'admin') \
    .get()

JavaScript

// Usernames starting with "admin" (case-sensitive)
const users = await client.database
    .from('users')
    .filter('username', 'startswith', 'admin')
    .execute()

// Usernames starting with "admin" (case-insensitive)
const users = await client.database
    .from('users')
    .filter('username', 'istartswith', 'admin')
    .execute()

Ends With

Operator	Description	Case Sensitivity
_endswith	Ends with	Case-sensitive
_iendswith	Ends with	Case-insensitive

REST API

# Emails ending with specific domain
GET /api/apps/crm-app/datatables/users/data/?email_endswith=company.com

# Files not ending with .tmp
GET /api/apps/docs-app/datatables/files/data/?filename_nendswith=.tmp

Python

# Emails ending with specific domain (case-sensitive)
users = auth_client.database.from_('users', app_slug='crm-app') \
    .filter('email', 'endswith', 'company.com') \
    .get()

# Emails ending with domain (case-insensitive)
users = auth_client.database.from_('users', app_slug='crm-app') \
    .filter('email', 'iendswith', 'company.com') \
    .get()

JavaScript

// Emails ending with specific domain (case-sensitive)
const users = await client.database
    .from('users')
    .filter('email', 'endswith', 'company.com')
    .execute()

// Emails ending with domain (case-insensitive)
const users = await client.database
    .from('users')
    .filter('email', 'iendswith', 'company.com')
    .execute()

Range Queries

Operator	Description	Format
_between	Value between range	?field_between=min,max
_nbetween	Value not in range	?field_nbetween=min,max

REST API

# Posts created in January 2024
GET /api/apps/blog-app/datatables/posts/data/?created_at_between=2024-01-01,2024-01-31

# Products outside price range
GET /api/apps/shop-app/datatables/products/data/?price_nbetween=10,20

Python

# Posts created in January 2024
# Note: _between not directly supported - use two filters (gte + lte)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('created_at', 'gte', '2024-01-01') \
    .filter('created_at', 'lte', '2024-01-31') \
    .get()

# Products within price range ($10 - $20)
products = auth_client.database.from_('products', app_slug='shop-app') \
    .filter('price', 'gte', 10) \
    .filter('price', 'lte', 20) \
    .get()

SDK Note: _between operator not directly supported. Chain two filters (gte + lte) for range queries.

JavaScript

// Posts created in January 2024
// Note: _between not directly supported - use two filters (gte + lte)
const posts = await client.database
    .from('posts')
    .filter('created_at', 'gte', '2024-01-01')
    .filter('created_at', 'lte', '2024-01-31')
    .execute()

// Products within price range ($10 - $20)
const products = await client.database
    .from('products')
    .filter('price', 'gte', 10)
    .filter('price', 'lte', 20)
    .execute()

SDK Note: _between operator not directly supported. Chain two filters (gte + lte) for range queries.

Null Checks

Operator	Description	Value
_null	Is null/not null	true or false
_nnull	Is not null/null	true or false

REST API

# Users without bio
GET /api/apps/blog-app/datatables/users/data/?bio_null=true

# Posts with published date
GET /api/apps/blog-app/datatables/posts/data/?published_at_nnull=true

Python

# Users without bio (null check)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('bio', 'isnull', True) \
    .get()

# Users with bio (not null)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('bio', 'isnull', False) \
    .get()

# Posts with published date
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('published_at', 'isnull', False) \
    .get()

SDK Note: Use isnull operator with True (is null) or False (is not null).

JavaScript

// Users without bio (null check)
const users = await client.database
    .from('users')
    .filter('bio', 'isnull', true)
    .execute()

// Users with bio (not null)
const users = await client.database
    .from('users')
    .filter('bio', 'isnull', false)
    .execute()

// Posts with published date
const posts = await client.database
    .from('posts')
    .filter('published_at', 'isnull', false)
    .execute()

SDK Note: Use isnull operator with true (is null) or false (is not null).

JSONB Field Querying

For tables using the JSONB provider, query nested fields using JSON path syntax:

REST API

# Query JSONB field properties
GET /api/apps/shop-app/datatables/products/data/?metadata__color=red

# Query nested properties
GET /api/apps/shop-app/datatables/users/data/?settings__notifications__email=true

# JSONB contains (PostgreSQL @> operator)
GET /api/apps/blog-app/datatables/posts/data/?tags__contains=["python","django"]

Python

# Query JSONB field properties (use dot notation for nested fields)
products = auth_client.database.from_('products', app_slug='shop-app') \
    .filter('metadata.color', 'eq', 'red') \
    .get()

# Query deeply nested properties
users = auth_client.database.from_('users', app_slug='shop-app') \
    .filter('settings.notifications.email', 'eq', True) \
    .get()

# Note: Advanced JSONB operators (@>, ?, etc.) may not be fully supported
# For complex JSONB queries, use REST API directly

SDK Note: Use dot notation (field.nested.property) for nested JSONB queries. Advanced PostgreSQL JSONB operators may require REST API.

JavaScript

// Query JSONB field properties (use dot notation for nested fields)
const products = await client.database
    .from('products')
    .filter('metadata.color', 'eq', 'red')
    .execute()

// Query deeply nested properties
const users = await client.database
    .from('users')
    .filter('settings.notifications.email', 'eq', true)
    .execute()

// Note: Advanced JSONB operators (@>, ?, etc.) may not be fully supported
// For complex JSONB queries, use REST API directly

SDK Note: Use dot notation (field.nested.property) for nested JSONB queries. Advanced PostgreSQL JSONB operators may require REST API.

JSONB Operators:

• field__property: Access nested property
• field__contains: JSONB contains (exact match)
• field__contained_by: JSONB is contained by
• field__has_key: JSONB has top-level key
• field__has_any_keys: JSONB has any of keys
• field__has_all_keys: JSONB has all keys

Example with nested data:

{
  "id": 1,
  "name": "Premium Widget",
  "metadata": {
    "color": "red",
    "specs": {
      "weight": 150,
      "dimensions": {"width": 10, "height": 20}
    },
    "tags": ["electronics", "featured"]
  }
}

# Query nested specs
GET /data/?metadata__specs__weight_gt=100

# Query array elements
GET /data/?metadata__tags__contains=["featured"]

Case-Sensitive Filtering

Most string operators have both case-insensitive (default) and case-sensitive variants:

Pattern: Add s suffix for case-sensitive matching

Operation	Case-Insensitive	Case-Sensitive
Contains	_contains	_containss
Starts with	_startswith	_startswiths
Ends with	_endswith	_endswiths
In list	_in	_ins
In array	_ina	(case-insensitive only)

Examples:

# Case-insensitive: matches "Python", "python", "PYTHON"
GET /data/?title_contains=python

# Case-sensitive: matches only "Python" (exact case)
GET /data/?title_containss=Python

# Case-insensitive list matching
GET /data/?tag_ina=JavaScript,python,Ruby

When to use case-sensitive:

• Matching code identifiers (className, functionName)
• Exact acronyms (API, REST, HTTP)
• File extensions (.PDF vs .pdf)
• Programming language keywords

When to use case-insensitive (default):

• User search queries
• Email addresses
• Natural language content
• Most user-facing filters

Full-Text Search

For advanced text search across multiple fields:

# Search across indexed fields
GET /api/apps/blog-app/datatables/posts/data/?search=django tutorial

# Combine with filters
GET /api/apps/blog-app/datatables/posts/data/?search=python&status=published

Requirements:

• Table must have search_vector field configured
• GIN index recommended for performance
• See Indexes Guide for setup

Search Features:

• Multi-word queries
• Relevance ranking
• Stemming support (e.g., "running" matches "run", "runs")
• Stop word filtering
• AND/OR logic

Example with ranking:

# Returns results sorted by relevance
GET /api/apps/docs-app/datatables/articles/data/?search=django rest api

Response includes relevance score:

{
  "data": [
    {
      "id": 1,
      "title": "REST API Tutorial",
      "rank": 0.95,
      "excerpt": "Learn how to build REST APIs with Django..."
    },
    {
      "id": 2,
      "title": "Building APIs with Django",
      "rank": 0.72,
      "excerpt": "A comprehensive guide to Django API development..."
    }
  ]
}

Advanced search syntax:

# Phrase search (exact match)
GET /data/?search="django rest framework"

# Exclude terms with minus
GET /data/?search=python -flask

# OR operator
GET /data/?search=django OR flask

Filtering on Related Records

Filter rows by fields on a foreign-keyed (related) table in a single request, using dot-notation in the filter key.

This avoids the two-step pattern of fetching IDs first and then filtering by __in, and works correctly with combined filter + sort + pagination flows.

Key guarantees:

• The related row is not added to the response unless you also pass populate=. Filtering on a related field does not bloat the payload.
• All existing operators (__contains, __gt, __in, __between, __null, __search, etc.) work on related fields.
• Existing literal-FK filters (?deal_id__contains=aaa) are unchanged — they still match against the FK column itself.

Forward Foreign Key (Many-to-One)

For a belongsTo relationship (e.g., activities.deal_id → deals.id), filter by any field on the related record:

REST API

# Activities whose related deal's name contains "Acme"
GET /api/apps/crm/datatables/activities/data/?deal_id.name__contains=Acme

# Activities whose deal is in the "closed_won" stage (implicit equality)
GET /api/apps/crm/datatables/activities/data/?deal_id.stage=closed_won

# Multi-hop: activities whose deal's company name starts with "Acme"
GET /api/apps/crm/datatables/activities/data/?deal_id.company_id.name__startswith=Acme

# Combined with populate (one shared join, related row included in payload)
GET /api/apps/crm/datatables/activities/data/?deal_id.name__contains=Acme&populate=deal_id

Python

# Activities whose related deal's name contains "Acme"
acme_activities = (
    auth_client.database.from_("activities")
    .filter("deal_id.name", "contains", "Acme")
    .execute()
)

# Multi-hop: activities whose deal's company name starts with "Acme"
results = (
    auth_client.database.from_("activities")
    .filter("deal_id.company_id.name", "startswith", "Acme")
    .execute()
)

SQL behavior: an INNER JOIN to the related table with the predicate placed in the ON clause. Rows whose foreign key is NULL (no related record) are excluded.

Multiple Filters on the Same Related Table

Multiple traversal filters that share the same FK path are combined into one join with both predicates ANDed:

# One INNER JOIN to deals, two ANDed predicates in the ON clause
GET /api/apps/crm/datatables/activities/data/?deal_id.name__contains=Acme&deal_id.stage=open

Reverse Foreign Key (One-to-Many)

For a hasMany relationship (e.g., a deal has many activities), filter parent rows by a property of any matching child:

# Deals that have at least one activity whose subject contains "follow up"
GET /api/apps/crm/datatables/deals/data/?activities.subject__contains=follow up

# Deals with at least one completed activity in the "demo" status
GET /api/apps/crm/datatables/deals/data/?activities.status=completed

Semantics: "at least one matching child" (default ANY). Implemented as EXISTS (SELECT 1 FROM activities WHERE activities.deal_id = deals.id AND ...) so parent rows are not duplicated and pagination/sort remain stable.

Many-to-Many

For an M2M relationship (e.g., users ↔ roles via user_roles junction):

# Users who have at least one role named "admin"
GET /api/apps/cloud/datatables/users/data/?roles.name__contains=admin

The query traverses the junction table inside an EXISTS subquery automatically — no need to know about the junction.

Limits and Validation

Constraint	Default	Setting
Max distinct traversal filters per request	5	DATA_SERVICE_MAX_FILTER_TRAVERSALS
Max chain depth (hops)	3	DATA_SERVICE_MAX_POPULATE_DEPTH

Errors you may see:

• '<segment>' is not a relationship on table '<table>' — the FK column or relationship name doesn't exist on the schema.
• field '<leaf>' does not exist on related table '<table>' — leaf field doesn't exist on the related table's schema.
• multi-hop traversal across a 'hasMany' relationship is not supported — only single-hop reverse-FK / M2M is supported. For chains across forward + reverse, restructure as separate forward-FK chains.
• Malformed filter field path '<path>' — the path has empty segments (.foo, foo., foo..bar).

Phase Compatibility

• flat_table provider: fully supported.
• jsonb provider: rejected with a clear error — full parity is on the roadmap.
• Aggregation queries (_aggregate=): traversal filters are not supported alongside aggregations.

Sorting

Sort results by one or more fields.

Single Field

REST API

# Sort by username (ascending)
GET /api/apps/blog-app/datatables/users/data/?_sort=username

# Sort by created date (descending)
GET /api/apps/blog-app/datatables/posts/data/?_sort=created_at&_order=desc

Python

# Sort by username (ascending)
users = auth_client.database.from_("users").sort("username", "asc").execute()

# Sort by created_at (descending)
posts = auth_client.database.from_("posts").sort("created_at", "desc").execute()

print(f"First user: {users['data'][0]['username']}")
print(f"Latest post: {posts['data'][0]['title']}")

JavaScript

import { Database } from '@taruvi/sdk'

const database = new Database(client)

// Sort by username (ascending)
const users = await database.from("users")
    .sort("username", "asc")
    .execute()

// Sort by created_at (descending)
const posts = await database.from("posts")
    .sort("created_at", "desc")
    .execute()

console.log(`First user: ${users.data[0].username}`)
console.log(`Latest post: ${posts.data[0].title}`)

Multiple Fields

REST API

# Sort by status, then by created_at descending
GET /api/apps/blog-app/datatables/posts/data/?_sort=status,created_at&_order=asc,desc

Python

# Sort by multiple fields
posts = (auth_client.database.from_("posts")
    .sort("status", "asc")
    .sort("created_at", "desc")
    .get())

for post in posts['data'][:5]:
    print(f"{post['status']}: {post['title']} ({post['created_at']})")

JavaScript

import { Database } from '@taruvi/sdk'

const database = new Database(client)

// Sort by multiple fields
const posts = await database.from("posts")
    .sort("status", "asc")
    .sort("created_at", "desc")
    .execute()

posts.data.slice(0, 5).forEach(post => {
    console.log(`${post.status}: ${post.title} (${post.created_at})`)
})

Parameters:

• _sort: Comma-separated list of field names
• _order: Comma-separated list of asc or desc (default: asc)

Sorting on Related Fields

Sort by a field on a foreign-keyed table using the same dot notation as filters:

REST API

# Sort activities by their related deal's name (descending)
GET /api/apps/crm/datatables/activities/data/?_sort=deal_id.name&_order=desc

# Multi-hop sort: by company name through deal
GET /api/apps/crm/datatables/activities/data/?_sort=deal_id.company_id.name

# Mixed root + traversal sort
GET /api/apps/crm/datatables/activities/data/?_sort=updated_at,deal_id.name&_order=desc,asc

Python

# Sort activities by their related deal's name
activities = (
    auth_client.database.from_("activities")
    .sort("deal_id.name", "desc")
    .execute()
)

SQL behavior:

• Sort-only traversal uses LEFT JOIN, so rows with NULL foreign keys are preserved (their sort key is NULL, placed at the end by PostgreSQL default).
• If a filter is also active on the same path (e.g., ?deal_id.stage=open&_sort=deal_id.name), the join is shared and promoted to INNER JOIN.

Limits: same as filter traversal — DATA_SERVICE_MAX_POPULATE_DEPTH hops, belongsTo relationships only.

Pagination

Control the number of results returned.

Basic Pagination

REST API

# Get first 10 records
GET /api/apps/blog-app/datatables/posts/data/?_start=0&_end=10

# Get records 10-20
GET /api/apps/blog-app/datatables/posts/data/?_start=10&_end=20

# Get 50 records starting from 100
GET /api/apps/blog-app/datatables/posts/data/?_start=100&_end=150

Parameters:

• _start: Starting index (0-based)
• _end: Ending index (exclusive)

Response Format: The response includes total count and meta with pagination information:

{
  "data": [...],
  "total": 250,
  "meta": {
    "offset": 0,
    "limit": 10,
    "count": 10,
    "has_more": true
  }
}

Python

# Get first page (10 records)
page1 = auth_client.database.from_("posts").page_size(10).page(1).execute()

print(f"Total posts: {page1.get('total', 0)}")
print(f"Page 1 has {len(page1['data'])} records")

# Get second page
page2 = auth_client.database.from_("posts").page_size(10).page(2).execute()

print(f"Page 2 has {len(page2['data'])} records")

# Iterate through pages
current_page = 1
while True:
    results = auth_client.database.from_("posts").page_size(20).page(current_page).execute()

    for post in results['data']:
        print(f"- {post['title']}")

    if len(results['data']) < 20:
        break  # Last page

    current_page += 1

JavaScript

import { Database } from '@taruvi/sdk'

const database = new Database(client)

// Get first page (10 records)
const page1 = await database.from("posts")
    .limit(10)
    .offset(0)
    .execute()

console.log(`Total posts: ${page1.total || 0}`)
console.log(`Page 1 has ${page1.data.length} records`)

// Get second page
const page2 = await database.from("posts")
    .limit(10)
    .offset(10)
    .execute()

console.log(`Page 2 has ${page2.data.length} records`)

// Iterate through pages
let currentPage = 0
const pageSize = 20

while (true) {
    const results = await database.from("posts")
        .limit(pageSize)
        .offset(currentPage * pageSize)
        .execute()

    results.data.forEach(post => {
        console.log(`- ${post.title}`)
    })

    if (results.data.length < pageSize) {
        break  // Last page
    }

    currentPage++
}

Calculating Pages

const pageSize = 20;
const page = 1; // First page

const start = (page - 1) * pageSize;
const end = page * pageSize;

const url = `/api/apps/blog-app/datatables/posts/data/?_start=${start}&_end=${end}`;

Combining Filters

You can combine multiple filters, sorting, and pagination:

GET /api/apps/blog-app/datatables/posts/data/?\
  status=published&\
  views_gt=100&\
  category_in=tech,programming&\
  title_contains=python&\
  _sort=views,created_at&\
  _order=desc,desc&\
  _start=0&\
  _end=20

This query:

1. ✅ Filters published posts
2. ✅ With more than 100 views
3. ✅ In tech or programming categories
4. ✅ Title containing "python"
5. ✅ Sorted by views (high to low), then by date (newest first)
6. ✅ Returns first 20 results

Response Format

List Response

All list endpoints return a standardized response with data, total, and meta:

{
  "data": [
    {
      "id": 1,
      "title": "Getting Started with Python",
      "status": "published",
      "views": 1523,
      "category": "programming",
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "id": 5,
      "title": "Advanced Python Techniques",
      "status": "published",
      "views": 856,
      "category": "tech",
      "created_at": "2024-01-20T14:20:00Z"
    }
  ],
  "total": 42,
  "meta": {
    "offset": 0,
    "limit": 20,
    "count": 2,
    "has_more": true
  }
}

Response Fields

• data: Array of records matching the query
• total: Total count of records matching filters (not just current page)
• meta: Pagination metadata
  • offset: Starting position of current page
  • limit: Maximum records per page
  • count: Number of records in current response
  • has_more: Whether there are more records available

Use total and meta to implement pagination UI.

Aggregations

Perform SQL-like aggregations directly in the database for analytics and reporting.

Basic Aggregates

Count all records:

GET /api/apps/blog-app/datatables/posts/data/?_aggregate=count(*)

Response:

{
  "data": [],
  "aggregates": {
    "count": 150
  }
}

Sum, Average, Min, Max:

# Total revenue
GET /api/apps/shop-app/datatables/orders/data/?_aggregate=sum(total)

# Average rating
GET /api/apps/blog-app/datatables/posts/data/?_aggregate=avg(rating)

# Price range
GET /api/apps/shop-app/datatables/products/data/?_aggregate=min(price),max(price)

GROUP BY

Group results and aggregate per group:

# Count posts by category
GET /api/apps/blog-app/datatables/posts/data/?_aggregate=count(*)&_group_by=category

# Total sales by product
GET /api/apps/shop-app/datatables/orders/data/?_aggregate=sum(amount)&_group_by=product_id

Response:

{
  "data": [
    {"category": "tutorial", "count": 25},
    {"category": "news", "count": 15},
    {"category": "review", "count": 10}
  ],
  "total": 3
}

HAVING Clause

Filter aggregated results:

# Categories with more than 10 posts
GET /data/?_aggregate=count(*)&_group_by=category&_having=count__gt=10

# Products with high sales
GET /data/?_aggregate=sum(amount)&_group_by=product_id&_having=sum_amount__gte=10000

Multiple Aggregates

Combine multiple aggregate functions:

GET /data/?_aggregate=count(*),sum(price),avg(price),min(price),max(price)

Response:

{
  "aggregates": {
    "count": 50,
    "sum_price": 5000.00,
    "avg_price": 100.00,
    "min_price": 10.00,
    "max_price": 500.00
  }
}

Combining with Filters

Apply WHERE filters before aggregation:

# Published posts only, grouped by author
GET /data/?status=published&_aggregate=count(*)&_group_by=author_id

# Sales this month
GET /data/?created_at__gte=2024-03-01&_aggregate=sum(amount)

For complete aggregation documentation, see Aggregations Guide which covers:

• All aggregate functions (count, sum, avg, min, max, array_agg, string_agg, json_agg, stddev, variance)
• Advanced GROUP BY with multiple fields
• Date/time grouping with date_trunc
• HAVING clause operators
• Complex analytics queries
• Performance optimization

Examples by Use Case

Search Functionality

# Search users by name or email
GET /api/apps/crm-app/datatables/users/data/?\
  name_contains=john&\
  email_contains=@company.com

Recent Items

# Last 10 posts
GET /api/apps/blog-app/datatables/posts/data/?\
  _sort=created_at&\
  _order=desc&\
  _start=0&\
  _end=10

Popular Content

# Most viewed posts this month
GET /api/apps/blog-app/datatables/posts/data/?\
  created_at_between=2024-01-01,2024-01-31&\
  _sort=views&\
  _order=desc&\
  _start=0&\
  _end=10

Active Users

# Users who logged in recently
GET /api/apps/crm-app/datatables/users/data/?\
  last_login_nnull=true&\
  _sort=last_login&\
  _order=desc

Status Filtering

# Pending orders
GET /api/apps/shop-app/datatables/orders/data/?\
  status=pending&\
  _sort=created_at&\
  _order=asc

Client Libraries

JavaScript/TypeScript

const fetchUsers = async (page = 1, pageSize = 20) => {
  const start = (page - 1) * pageSize;
  const end = page * pageSize;

  const params = new URLSearchParams({
    status: 'active',
    _sort: 'created_at',
    _order: 'desc',
    _start: start.toString(),
    _end: end.toString(),
  });

  const response = await fetch(
    `/api/apps/blog-app/datatables/users/data/?${params}`,
    {
      headers: {
        'Authorization': `Bearer ${token}`,
      },
    }
  );

  const result = await response.json();

  return {
    data: result.data,
    total: result.total,
    totalPages: Math.ceil(result.total / pageSize),
    meta: result.meta,
    hasMore: result.meta.has_more,
  };
};

Python

import requests

def fetch_posts(
    app_slug='blog-app',
    status='published',
    page=1,
    page_size=20,
    sort_by='created_at',
    order='desc'
):
    start = (page - 1) * page_size
    end = page * page_size

    params = {
        'status': status,
        '_sort': sort_by,
        '_order': order,
        '_start': start,
        '_end': end,
    }

    response = requests.get(
        f'https://api.yourapp.com/api/apps/{app_slug}/datatables/posts/data/',
        params=params,
        headers={'Authorization': f'Bearer {token}'}
    )

    result = response.json()

    return {
        'data': result['data'],
        'total': result['total'],
        'total_pages': -(-result['total'] // page_size),
        'meta': result.get('meta', {}),
        'has_more': result.get('meta', {}).get('has_more', False),
    }

Performance Tips

1. Use Specific Fields

Only select the fields you need (future feature):

# Coming soon: field selection
GET /api/apps/blog-app/datatables/posts/data/?fields=id,title,created_at

2. Limit Page Size

Don't request too many records at once:

# ✅ Good: Reasonable page size
?_start=0&_end=50

# ❌ Bad: Too many records
?_start=0&_end=10000

3. Use Indexes

Ensure your filtered and sorted fields have database indexes. Contact your administrator if queries are slow.

4. Cache Results

Cache frequently accessed data on the client side:

const cache = new Map();

async function fetchWithCache(url) {
  if (cache.has(url)) {
    return cache.get(url);
  }

  const response = await fetch(url);
  const data = await response.json();

  cache.set(url, data);
  setTimeout(() => cache.delete(url), 60000); // Cache for 1 minute

  return data;
}

Next Steps

• CRUD Operations: Complete guide to creating, reading, updating, and deleting data
• Aggregations: SQL-like GROUP BY, HAVING, and aggregate functions
• Relationships: Query related data with populate
• Indexes: Create indexes for better query performance
• API Endpoints Reference: Complete endpoint listing

Common Patterns

Search with Autocomplete

# Search as user types
GET /api/apps/blog-app/datatables/users/data/?\
  username_startswith=joh&\
  _start=0&\
  _end=10

Infinite Scroll

let page = 1;
const pageSize = 20;

async function loadMore() {
  const start = (page - 1) * pageSize;
  const end = page * pageSize;

  const data = await fetchData({ _start: start, _end: end });
  appendToList(data);
  page++;
}

Faceted Search

# Get counts for each category
GET /api/apps/shop-app/datatables/products/data/?status=active

# Then filter by category
GET /api/apps/shop-app/datatables/products/data/?\
  status=active&\
  category=electronics