Design parameter handling table for search/listing/filter.
- User
Design parameter handling table for search/listing/filter.
Assistant
The user wants a "Design parameter handling table for search/listing/filter." This request is a bit abstract. It implies the user needs a schema, a logic table, or a reference guide on how to handle different types of parameters when building an API endpoint (likely REST) or a backend service for listing entities (like a product list, user list, etc.) with search and filtering capabilities.
Context: Backend development, API design, Database querying.
What needs to be covered?
- Input parameters: What comes from the client? (Query strings, path params).
- Parameter types: Pagination, Sorting, Filtering (exact, range, fuzzy), Search (full-text).
- Handling logic: How should the backend interpret these?
- Default behaviors: What happens if the parameter isn't provided?
- Validation/Errors: What if the input is bad?
Structure of the Output: A table is explicitly requested. I should organize it by categories of parameters (Common, Filtering, Sorting, Pagination).
Drafting the columns:
- Parameter Category: (e.g., Pagination, Sorting, Filter).
- Parameter Name/Key: (e.g.,
page,sort_by,status). - Type: (e.g., Integer, String, Enum).
- Example Value: Concrete examples.
- Handling Logic/Behavior: How the code processes it.
- Default Value: Fallback behavior.
Refining the content:
- Pagination: Standard
page/limitoroffset/size. Let's stick to the popularpage/size(orper_page). - Sorting:
sort_by(field) andorder(asc/desc). Or a combined string likecreated_at:desc. - Search:
qorkeyword. Usually full-text search across multiple fields. - Filtering: This is the tricky part. Exact match, partial match (like), ranges (gt/lt), date ranges. I should list generic patterns.
Scenario: Let's assume a generic resource, e.g., "Products".
The Table Design:
Category Param Name Data Type Example Behavior / Logic Default Pagination pageInt 1Offset calculation: (page - 1) * size. Must be > 0.1limit/sizeInt 20Max items to return. Cap at 100 (prevent DoS). 20Sorting sortString price:descFormat: field:direction. Validate field exists.created_at:descSearch qString iphoneFull-text search on indexed fields (name, desc). null(no filter)Filter: Exact statusEnum/String activeSQL: WHERE status = 'active'All Filter: Range min_priceFloat 100SQL: WHERE price >= 100nullmax_priceFloat 500SQL: WHERE price <= 500nullFilter: Date created_fromISO Date 2023-01-01SQL: WHERE created_at >= '2023-01-01'nullAdditional Considerations: The user might also want to know about sanitization and validation. I should add a section on "General Rules" or "Security/Best Practices" to make the answer robust.
Self-Correction/Improvement: Simply providing the table might be dry. I should provide a JSON structure example of how the request looks, and maybe a pseudo-code SQL generation logic.
Let's make the table comprehensive. I'll structure it into:
- The Table (The core request).
- URL Structure Example (How it looks in practice).
- Backend Logic Implementation Steps (Algorithm).
- Security & Performance checklist.
Detailed Table Content:
- Pagination:
page,per_page. - Sorting:
sort_by,sort_order. - Search:
searchorq. - Field Selection:
fields(Partial response). - Filters: Needs to handle
field=value,field[gt]=value,field[like]=value. Let's keep it simple for the table but mention the complexity.
Final Plan:
- Introduction: Define scope (REST API usually).
- The Master Table: Columns: Param, Type, Example, Description, Default/Constraint.
- Logic Examples: How to combine them (SQL/ORM style).
- Best Practices: CORS, SQL Injection prevention, etc.
Let's refine the table columns for maximum clarity:
- Parameter
- Type
- Format/Example
- Backend Logic (SQL/ORM interpretation)
- Validation Rules
This looks solid. I will write the response based on this structure. Designing a parameter handling strategy is critical for building robust, scalable, and secure APIs for listing and searching data.
Below is a comprehensive design specification for handling parameters in a RESTful API context.
1. The Parameter Handling Matrix
This table maps client-side inputs to backend logic, including types, defaults, and validation rules.
Category Parameter Name Type Example Logic / SQL Mapping Default & Validation Pagination pageInteger 1OFFSET = (page - 1) * limitDef: 1Val: Must be 1 limit/sizeInteger 20LIMIT / FETCH NEXTDef: 20Val: 1–100 (Prevent DoS) Sorting sort/sort_byString created_atORDER BY {column}Def: created_atoridVal: Must exist in Whitelistorder/sort_dirEnum descASCorDESCDef: descVal:asc,desc(case-insensitive)Search q/keywordString johnWHERE field LIKE '%q%'(Multiple cols)Def: NullVal: Max length 100 chars Filtering {field}Any status=activeWHERE field = valVal: Exact match {field}_inList id_in=1,2,3WHERE field IN (val)Comma-separated values {field}_gtNumber price_gt=100WHERE field > valNumber/Date {field}_gteNumber price_gte=100WHERE field >= valNumber/Date {field}_ltNumber price_lt=1000WHERE field < valNumber/Date {field}_lteNumber price_lte=1000WHERE field <= valNumber/Date {field}_likeString name_like=smithWHERE field LIKE '%val%'String {field}_nullBoolean deleted_null=falseWHERE field IS NOT NULLtrue/falseField Selection fields/selectString id,name,emailSelects specific columns to return Def: *(All)Val: Must exist in Whitelist
2. URL Structure & Encoding
How these parameters appear in the URL is vital for caching and readability.
A. Standard Query Params (Recommended)
The most common REST approach.
GET /api/v1/users?status=active&role_id_in=1,2,3&sort=created_at&order=desc&q=johnB. JSON Body Filtering (Complex Queries)
If the filter logic is deeply nested (e.g., complex boolean logic
AND/OR), passing JSON viaPOSTis often cleaner, though technically less "RESTful" for retrieval.POST /api/v1/users/search { "filter": { "and": [ { "field": "status", "operator": "=", "value": "active" }, { "field": "price", "operator": ">", "value": 100 } ] } }Note: The table above assumes the Query Param approach (A).
3. Backend Processing Pipeline
When the request hits the backend, follow this order of operations:
- Sanitization: Trim whitespace. Check for SQL injection characters (handled by ORM usually, but good to be aware).
- Whitelisting:
- Sorting: Ensure
sort_byvalue is an actual column name in the database. If the client sendssort_by=password, reject or ignore it. - Fields: Ensure requested
fieldsare allowed to be read (ignore requests forpassword_hash).
- Sorting: Ensure
- Type Casting: Convert query string values (which are always strings) to Integers, Booleans, or Dates.
- Default Application: If
pageis missing, inject1. Iflimitis missing, inject20. - Query Building: Dynamically construct the database query (SQL or ORM chain).
Pseudo-Code Logic (Javascript/Node Style)
function buildQuery(params) { const query = {}; const MAX_LIMIT = 100; // 1. Pagination const page = parseInt(params.page) || 1; const limit = Math.min(parseInt(params.limit) || 20, MAX_LIMIT); query.offset = (page - 1) * limit; query.limit = limit; // 2. Sorting const allowedSorts = ['created_at', 'name', 'price']; const sortBy = allowedSorts.includes(params.sort) ? params.sort : 'created_at'; const sortOrder = params.order === 'asc' ? 'ASC' : 'DESC'; query.order = [[sortBy, sortOrder]]; // 3. Search (Global) if (params.q) { query.where = { [Op.or]: [ { name: { [Op.iLike]: `%${params.q}%` } }, { email: { [Op.iLike]: `%${params.q}%` } } ] }; } // 4. Filters (Dynamic mapping) // Example: map URL ?price_gte=100 -> DB query if (params.price_gte) { query.where.price = { ...query.where.price, [Op.gte]: params.price_gte }; } return query; }
4. Response Envelope Design
The API response should echo the parameters used (or the calculated pagination state) so the UI can update its controls.
{ "data": [ { "id": 1, "name": "John Doe" } ], "meta": { "page": 1, "limit": 20, "total_items": 105, "total_pages": 6 }, "links": { "self": "/users?page=1", "next": "/users?page=2", "last": "/users?page=6" } }5. Security & Performance Checklist
- Enforce Max Limit: Always cap the
limitparameter (e.g., at 100). Never allow a client to request 1,000,000 rows, or it will crash the database. - Column Whitelisting: Never pass raw user input into an
ORDER BYclause. Map user input (e.g.,created) to database columns (e.g.,created_at). - Indexing: Ensure database indexes exist for:
- Columns used in sorting (
ORDER BY). - Columns used in equality filters (
WHERE status = 'active').
- Columns used in sorting (
- Fuzzy Search Performance:
LIKE %term%is slow on large datasets. For production systems handling large text search, use dedicated search engines (Elasticsearch, Algolia) or Database Full-Text Search capabilities (Postgrestsvector).