Custom Condition Paged Query

The parameters for the custom condition paged query interface include two parts: pagination and conditions. The specific 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	Database 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.

Note

For MongoDB, if the value type is an ObjectId, the corresponding name field must include the :oid suffix — for example, user_id:oid. This is not required for GORM. This is the only difference between MongoDB and GORM in this regard.

Security Tip

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

Performance Note

When using offset-based pagination in GORM, query performance can significantly degrade as the number of rows increases and deeper pages are requested. Please carefully evaluate the usage scenario.

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
  }