Skip to content
TheChatArchiveThe Chat Archive
GLMGLM-5 / Pony Alpha· unknownHugging Face Frontier Reasoning Traces

Design parameter handling table for search/listing/filter.

  1. User

    Design parameter handling table for search/listing/filter.

  2. GLM 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?

    1. Input parameters: What comes from the client? (Query strings, path params).
    2. Parameter types: Pagination, Sorting, Filtering (exact, range, fuzzy), Search (full-text).
    3. Handling logic: How should the backend interpret these?
    4. Default behaviors: What happens if the parameter isn't provided?
    5. 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/limit or offset/size. Let's stick to the popular page/size (or per_page).
    • Sorting: sort_by (field) and order (asc/desc). Or a combined string like created_at:desc.
    • Search: q or keyword. 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:

    CategoryParam NameData TypeExampleBehavior / LogicDefault
    PaginationpageInt1Offset calculation: (page - 1) * size. Must be > 0.1
    limit / sizeInt20Max items to return. Cap at 100 (prevent DoS).20
    SortingsortStringprice:descFormat: field:direction. Validate field exists.created_at:desc
    SearchqStringiphoneFull-text search on indexed fields (name, desc).null (no filter)
    Filter: ExactstatusEnum/StringactiveSQL: WHERE status = 'active'All
    Filter: Rangemin_priceFloat100SQL: WHERE price >= 100null
    max_priceFloat500SQL: WHERE price <= 500null
    Filter: Datecreated_fromISO Date2023-01-01SQL: WHERE created_at >= '2023-01-01'null

    Additional 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:

    1. The Table (The core request).
    2. URL Structure Example (How it looks in practice).
    3. Backend Logic Implementation Steps (Algorithm).
    4. Security & Performance checklist.

    Detailed Table Content:

    • Pagination: page, per_page.
    • Sorting: sort_by, sort_order.
    • Search: search or q.
    • 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:

    1. Introduction: Define scope (REST API usually).
    2. The Master Table: Columns: Param, Type, Example, Description, Default/Constraint.
    3. Logic Examples: How to combine them (SQL/ORM style).
    4. 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.

    CategoryParameter NameTypeExampleLogic / SQL MappingDefault & Validation
    PaginationpageInteger1OFFSET = (page - 1) * limitDef: 1Val: Must be ≥\ge 1
    limit / sizeInteger20LIMIT / FETCH NEXTDef: 20Val: 1–100 (Prevent DoS)
    Sortingsort / sort_byStringcreated_atORDER BY {column}Def: created_at or idVal: Must exist in Whitelist
    order / sort_dirEnumdescASC or DESCDef: descVal: asc, desc (case-insensitive)
    Searchq / keywordStringjohnWHERE field LIKE '%q%' (Multiple cols)Def: NullVal: Max length 100 chars
    Filtering{field}Anystatus=activeWHERE field = valVal: Exact match
    {field}_inListid_in=1,2,3WHERE field IN (val)Comma-separated values
    {field}_gtNumberprice_gt=100WHERE field > valNumber/Date
    {field}_gteNumberprice_gte=100WHERE field >= valNumber/Date
    {field}_ltNumberprice_lt=1000WHERE field < valNumber/Date
    {field}_lteNumberprice_lte=1000WHERE field <= valNumber/Date
    {field}_likeStringname_like=smithWHERE field LIKE '%val%'String
    {field}_nullBooleandeleted_null=falseWHERE field IS NOT NULLtrue/false
    Field Selectionfields / selectStringid,name,emailSelects specific columns to returnDef: * (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=john
    

    B. JSON Body Filtering (Complex Queries)

    If the filter logic is deeply nested (e.g., complex boolean logic AND/OR), passing JSON via POST is 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:

    1. Sanitization: Trim whitespace. Check for SQL injection characters (handled by ORM usually, but good to be aware).
    2. Whitelisting:
      • Sorting: Ensure sort_by value is an actual column name in the database. If the client sends sort_by=password, reject or ignore it.
      • Fields: Ensure requested fields are allowed to be read (ignore requests for password_hash).
    3. Type Casting: Convert query string values (which are always strings) to Integers, Booleans, or Dates.
    4. Default Application: If page is missing, inject 1. If limit is missing, inject 20.
    5. 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

    1. Enforce Max Limit: Always cap the limit parameter (e.g., at 100). Never allow a client to request 1,000,000 rows, or it will crash the database.
    2. Column Whitelisting: Never pass raw user input into an ORDER BY clause. Map user input (e.g., created) to database columns (e.g., created_at).
    3. Indexing: Ensure database indexes exist for:
      • Columns used in sorting (ORDER BY).
      • Columns used in equality filters (WHERE status = 'active').
    4. 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 (Postgres tsvector).