--- title: "Querying and Filtering - DreamFactory Documentation" source: "https://wiki.dreamfactory.com/Querying_Filtering" canonical_url: "https://wiki.dreamfactory.com/Querying_Filtering" converted_at: "2026-04-05T06:13:43.941Z" format: "markdown" converted_by: "html-to-md-ai" --- []() # Querying Filtering From DreamFactory Wiki [Jump to navigation](#mw-head) [Jump to search](#searchInput) **Learn how to query, filter, sort, and paginate database records using DreamFactory's REST API** ## Contents - [1 Querying and Filtering Records in DreamFactory](#Querying_and_Filtering_Records_in_DreamFactory) - [2 Quick Reference](#Quick_Reference) - [3 Overview](#Overview) - [4 Basic Usage](#Basic_Usage) - [4.1 Endpoint Structure](#Endpoint_Structure) - [4.2 Quick Examples](#Quick_Examples) - [5 Filter Syntax](#Filter_Syntax) - [5.1 Logical Operators](#Logical_Operators) - [5.2 Comparison Operators](#Comparison_Operators) - [5.3 String Operations](#String_Operations) - [5.4 Batch Operations](#Batch_Operations) - [5.5 Record Identification](#Record_Identification) - [6 Best Practices](#Best_Practices) - [7 Troubleshooting](#Troubleshooting) - [7.1 Common Issues](#Common_Issues) - [7.2 Response Format](#Response_Format) - [8 See also](#See_also) ## 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](/Getting-started/dreamfactory-configuration/date-and-time-configuration). ## See also - [Generating a Database-backed API](/Generating_Database_Backed_Api) - [Microsoft SQL Server](/Sql_Server) Retrieved from "[https://wiki.dreamfactory.com/index.php?title=Querying_Filtering&oldid=836](https://wiki.dreamfactory.com/index.php?title=Querying_Filtering&oldid=836)" [Categories](/Special:Categories): - [Database Query](/Category:Database_Query) - [Api Filtering](/Category:Api_Filtering) - [Rest Api Query](/Category:Rest_Api_Query) - [API](/Category:API) - [Database](/Category:Database) - [Difficulty Intermediate](/Category:Difficulty_Intermediate)