Querying Filtering

Learn how to query, filter, sort, and paginate database records using DreamFactory's REST API

Querying and Filtering Records in DreamFactory

Quick Reference

Parameter	Purpose	Example
filter	Filter records	filter=(status='active')
fields	Select columns	fields=id,name,email
limit	Max records	limit=10
offset	Skip records	offset=20
order	Sort results	order=name ASC
include_count	Get total count	include_count=true
ids	Get by IDs	ids=1,2,3

Overview

DreamFactory provides powerful filtering capabilities for database operations, allowing you to precisely query and manipulate your data through the REST API. This guide will walk you through the basics and advanced features of filtering in DreamFactory.

Basic Usage

Endpoint Structure

GET https://{your-dreamfactory-url}/api/v2/{api_name}/_table/{table_name}?filter={filter_string}

Component	Description	Example
api_name	Name of your API service	mysql, postgres
table_name	Database table to query	customers, orders
filter_string	URL-encoded filter expression	(status='active')

Your HTTP client will automatically handle URL encoding of special characters in the filter string.

Quick Examples

# Basic filtering
GET /api/v2/db/_table/users?filter=(status='active')

# Multiple conditions
GET /api/v2/db/_table/orders?filter=(status='pending') AND (total>100)

# Pagination and sorting
GET /api/v2/db/_table/products?limit=10&offset=0&order=name ASC

Filter Syntax

DreamFactory uses SQL-like syntax for filtering. Ensure you are familiar with SQL operators and their usage in DreamFactory.

Logical Operators

Operator	Description	Example
AND	Both conditions must be true	(status='active') AND (age>21)
OR	Either condition must be true	(status='active') OR (status='pending')
NOT	Negates the condition	NOT(status='deleted')

Comparison Operators

Operator	Description	Example
=, !=	Equality/Inequality	(status = 'active')
>, >=	Greater than (or equal)	(age > 21)
<, <=	Less than (or equal)	(price < 100)
IN	Match any value in set	(status IN ('active','pending'))
LIKE	Pattern matching	(email LIKE '%@company.com')
IS NULL	Check for null values	(phone IS NULL)
Range check	Value in range	(age>=18) AND (age<=65)

String Operations

ℹ️ Note: Database Compatibility The CONTAINS, STARTS WITH, and ENDS WITH operators have limited database support. For maximum compatibility across all databases (MySQL, PostgreSQL, SQL Server, etc.), use the LIKE operator with wildcards instead. ::: {

tip Security Best Practice Always use parameter replacement to prevent SQL injection attacks. This ensures that user input is safely handled. :::

Batch Operations

Allows you to handle multiple records in a single call, with options to continue processing after failures or roll back all changes if any operation fails. | Parameter | Description | Example | |———–|————-|———| | continue | Continue processing after failures | continue=true | | rollback | Rollback all changes if any operation fails | rollback=true |

Record Identification

# Get by single ID
GET /api/v2/db/_table/users/123

# Get by multiple IDs
GET /api/v2/db/_table/users?ids=1,2,3

# Get by custom ID field
GET /api/v2/db/_table/users?id_field=email&[email protected]

Best Practices

1. Use Parameters: Enhance security with parameter replacement
2. Limit Results: Always paginate large datasets
3. Select Fields: Only request needed fields
4. URL Encoding: Properly encode special characters
5. Error Handling: Use rollback=true for critical operations
6. Test Incrementally: Build complex filters step by step
7. Wrap Conditions: Always wrap logical conditions in parentheses
8. Validate Schema: Use include_schema=true to verify field names

Troubleshooting

Common Issues

• Wrap all logical conditions in parentheses
• Use proper quotes for string values
• Verify field names with include_schema=true
• Test complex filters incrementally

Response Format

{
    "resource": [
        {
            "id": 1,
            "name": "Example"
        }
    ],
    "meta": {
        "count": 1,
        "schema": {
            "name": "table_name",
            "fields": [...]
        }
    }
}

Date/time formats can be configured globally in your DreamFactory configuration. Detailed documentation is available here.

See also

• Generating a Database-backed API
• Microsoft SQL Server