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:

{
  "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:

{
  "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

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

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`

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

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

Examples:

# 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

String Matching

Contains

Operator	Description	Case Sensitivity
`_contains`	Contains substring	Case-insensitive
`_ncontains`	Does not contain	Case-insensitive
`_containss`	Contains substring	Case-sensitive
`_ncontainss`	Does not contain	Case-sensitive

Examples:

# 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

# Case-sensitive search
GET /api/apps/docs-app/datatables/articles/data/?content_containss=API

Starts With

Operator	Description	Case Sensitivity
`_startswith`	Starts with	Case-insensitive
`_nstartswith`	Does not start with	Case-insensitive
`_startswiths`	Starts with	Case-sensitive
`_nstartswiths`	Does not start with	Case-sensitive

Examples:

# 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

Ends With

Operator	Description	Case Sensitivity
`_endswith`	Ends with	Case-insensitive
`_nendswith`	Does not end with	Case-insensitive
`_endswiths`	Ends with	Case-sensitive
`_nendswiths`	Does not end with	Case-sensitive

Examples:

# 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

Range Queries

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

Examples:

# 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

Null Checks

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

Examples:

# 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

Sorting

Sort results by one or more fields.

Single Field

# 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

Multiple Fields

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

Parameters:

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

Pagination

Control the number of results returned.

Basic Pagination

# 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
  }
}

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:

✅ Filters published posts
✅ With more than 100 views
✅ In tech or programming categories
✅ Title containing "python"
✅ Sorted by views (high to low), then by date (newest first)
✅ 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.

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

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

Relationships: Query related data with populate
Creating Data: Insert new records
Updating Data: Modify existing records
Deleting Data: Remove records

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

Basic Querying​

Get All Records​

Get Single Record​

Filtering​

Exact Match​

Comparison Operators​

IN Operators​

String Matching​

Contains​

Starts With​

Ends With​

Range Queries​

Null Checks​

Sorting​

Single Field​

Multiple Fields​

Pagination​

Basic Pagination​

Calculating Pages​

Combining Filters​

Response Format​

List Response​

Response Fields​

Examples by Use Case​

Search Functionality​

Recent Items​

Popular Content​

Active Users​

Status Filtering​

Client Libraries​

JavaScript/TypeScript​

Python​

Performance Tips​

1. Use Specific Fields​

2. Limit Page Size​

3. Use Indexes​

4. Cache Results​

Next Steps​

Common Patterns​

Search with Autocomplete​

Infinite Scroll​

Faceted Search​

Basic Querying

Get All Records

Get Single Record

Filtering

Exact Match

Comparison Operators

IN Operators

String Matching

Contains

Starts With

Ends With

Range Queries

Null Checks

Sorting

Single Field

Multiple Fields

Pagination

Basic Pagination

Calculating Pages

Combining Filters

Response Format

List Response

Response Fields

Examples by Use Case

Search Functionality

Recent Items

Popular Content

Active Users

Status Filtering

Client Libraries

JavaScript/TypeScript

Python

Performance Tips

1. Use Specific Fields

2. Limit Page Size

3. Use Indexes

4. Cache Results

Next Steps

Common Patterns

Search with Autocomplete

Infinite Scroll

Faceted Search