Custom Condition Paged Query

Overview

Paging query is a common function in data retrieval. In most cases, we need to control the pagination display of query results through parameters such as page number, number of pages per page, and sorting. And conditional query is another important function in data retrieval, which allows users to filter out data that meets the conditions by specifying parameters such as column names, operators, query values, logical connectors, etc.

The following will provide a detailed introduction to the parameter rules of the custom conditional pagination query API, including the rules for pagination parameters (page number, number of pages per page, sorting) and conditional parameters (column name, operator, query value, logical connector such as AND/OR), including how to implement multi condition combination and logical grouping (such as AND, OR) through columns arrays to achieve flexible and powerful data retrieval functions.

The parameters for the custom conditional pagination query interface are as follows:

{
    "page": 0,
    "limit": 10,
    "sort": "-id",

    "columns": [
          {"name": "language", "exp":"=", "value": "go", "logic": "and"}
    ]
}

The parameters include two parts: pagination and conditions. The pagination part is required, while the conditions part is optional. The detailed usage rules are as follows:

1. Pagination Parameters (Required)

Parameter Name	Required	Description	Example
page	No	Page number, starts counting from 0	0 (First page)
limit	Yes	Data volume per page (1-1000)	10
sort	No	Sorting rule: • Add - before the field (table column name) for descending order • Separate multiple fields with commas • Default is descending order	-id

Tips

When the sort parameter is not specified and an id column exists in the table, the system defaults to sorting by id in descending order.

2. Condition Parameters (Optional)

Uses the columns array structure and supports multi-condition combined queries:

Parameter Name	Required	Description	Default Value
name	Yes	Table column name	-
exp	No	Operator: =, !=, >, >=, <, <=, in, like, notin, isnull, isnotnull	=
value	Yes	Query value: • Separate multiple values with commas for in/notin • Can be left empty for isnull/isnotnull	-
logic	No	Logical connector: • and/or • Append :( or :) to implement logical grouping	and

Tips

The logic for the last condition is meaningless and can be omitted, but if it is an ending group, it must be included.

Security Tip

• GORM: Strict input validation is performed on pagination query parameters, effectively preventing SQL injection risks and ensuring system security and stability.
• MongoDB: In addition to basic parameter validation, it also supports custom validation rules for field values, providing more flexible security protection.

Performance Note

GORM: When using offset-based pagination, query performance can degrade significantly when the table has a large number of rows and deeper pages are queried. Please carefully evaluate the usage scenario. Pagination queries using the ListByLastID method do not slow down as the number of rows increases.

Grouping Syntax Explanation

When using grouping conditions, you must explicitly specify paired parentheses ( and ) and follow these rules:

• Based on and / or, grouping is indicated by appending ( or ) — for example, the :( suffix denotes the start of a group, and the :) suffix denotes the end.
• Parentheses must be correctly nested and appear in pairs.

For example:

{
    "columns": [
        {"name": "foo1", "value": "bar1", "logic": "and"},
        {"name": "foo2", "value": "bar2", "logic": "or:("},  // can be understood as {"group":"(", "name": "foo2", "value": "bar2", "logic": "or"}
        {"name": "foo3", "value": "bar3", "logic": "and:)"}  //can be understood as {"name": "foo3", "value": "bar3", "logic": "and", "group": ")"}
    ]
}

• Equivalent SQL:

  WHERE foo1 = 'bar1' AND  ( foo2 = 'bar2' OR foo3 = 'bar3' )

• Equivalent MongoDB query:

  {
      "foo1": "bar1",
      "$or": [
          {"foo2": "bar2"},
          {"foo3": "bar3"}
      ]
  }

Typical Examples

Example 1

Query the first 10 new user data entries. The API interface request parameters are as follows:

{
    "page": 0,
    "limit": 10,
    "sort": "-created_at"
}

• Equivalent SQL:

  ORDER BY created_at DESC
  LIMIT 10 OFFSET 0

• Equivalent MongoDB query:

  {
      "sort": { "created_at": -1 },
      "skip": 0,
      "limit": 10
  }

Example 2

Query users who are male and over 20 years old. The API interface request parameters are as follows:

{
    "page": 0,
    "limit": 10,
    "columns": [
        {
            "name": "age",
            "exp": ">",
            "value": "20"
        },
        {
            "name": "gender",
            "value": "male"
        }
    ]
}

• Equivalent SQL:

  WHERE age > 20 AND gender = 'male'
  ORDER BY id DESC
  LIMIT 10 OFFSET 0

• Equivalent MongoDB query:

  {
      "filter": {
          "age": { "$gt": 20 },
          "gender": "male"
      },
      "sort": { "_id": -1 },
      "skip": 0,
      "limit": 10
  }

Example 3

Query employees in the R&D department with salary greater than or equal to 10000, OR employees in the marketing department with level 3, 4, or 5. The API interface request parameters are as follows:

{
    "page": 0,
    "limit": 20,
    "sort": "-created_at",
    "columns": [
          {"name": "dept", "value": "rd", "logic": "and:("},
          {"name": "salary", "exp": ">=", "value": "10000", "logic": "or:)"},
          {"name": "dept", "value": "mkt", "logic": "and:("},
          {"name": "level", "exp": "in", "value": "3,4,5", "logic": "and:)"}
    ]
}

• Equivalent SQL:

  WHERE (dept = 'rd' AND salary >= 10000) OR (dept = 'mkt' AND level IN (3,4,5))
  ORDER BY created_at DESC
  LIMIT 20 OFFSET 0

• Equivalent MongoDB query:

  {
      "filter": {
          "$or": [
              { "dept": "rd", "salary": { "$gte": 10000 } },
              { "dept": "mkt", "level": { "$in": [3,4,5] } }
          ]
      },
      "sort": { "created_at": -1 },
      "skip": 0,
      "limit": 20
  }

Preserve String Type

In conditional queries, the value will by default be converted to the corresponding type. For example, value = "100" will automatically be converted to the number 100.

If you want to preserve it as a string and prevent conversion to a number, you can wrap the value in double quotes to explicitly indicate a string type (value = "\"100\"").

Example:

{
    "page": 0,
    "limit": 10,
    "columns": [
        {
            "name": "id",
            "exp": "=",
            "value": "\"100\""
        },
    ]
}