Skip to content

Query.md

Latest commit

 

History

History
225 lines (178 loc) · 3.65 KB

Query.md

File metadata and controls

225 lines (178 loc) · 3.65 KB

Why

I use DataTables in some projects and wrote SpringMVC server side for it. Later the frontend is rewritten and DataTables is no longer used. Now is the time to refine the protocol for general data-grid query.

  • You can not assign a range via columns[i][search][value], as in SQL: BETWEEN.
  • You can only do regular search OR regex search via columns[i][search][regex], but not =, LIKE.

Query

HTTP POST as @RequestBody is recommended:

{
    "draw": 1,
    "offset": 0,
    "limit": 10,
    "order_by": [{
        "field1": "asc"
    }, {
        "field2": "desc"
    }],
    "where": {
        "field1": {
            "_gt": "",
            "_gte": "",
            "_lt": "",
            "_lte": "",
            "_eq": "",
            "_ne": "",
            "_in": [],
            "_nin": [],
            "_all": [],
            "_regex": "",
            "_like": "",
            "_notlike": "",
            "_exists": true,
            "_null": true,
            "_empty": true,
            "_isvoid": true
        }
    }
}

Response format

{
    "draw": 1,
    "total": 0,
    "filtered": 0,
    "error": "",
    "data": []
}

Examples

Basic Query

None of the draw, offset, limit, order_by, where are required, so the basic query is:

POST /search HTTP/1.1
Content-Type: application/json

{}

e.g. NO parameters are needed.

Pagination

Paging is controlled by offset and limit, which keeps the same as SKIP and LIMIT in SQL.

POST /search HTTP/1.1
Content-Type: application/json

{
    "offset": 30,
    "limit": 10
}

Ordering

Ordering by one column:

POST /search HTTP/1.1
Content-Type: application/json

{
    "order_by": [{
        "field": "price.value",
        "dir": "desc"
    }]
}

Ordering by more columns:

POST /search HTTP/1.1
Content-Type: application/json

{
    "order_by": [{
        "price.value": "asc"
    }, {
        "createTime": "desc"
    }]
}

Filtering

  • Search by equality
POST /search HTTP/1.1
Content-Type: application/json

{
    "where": {
        "user.name": {
            "_eq": "James Bond"
        }
    }
}
  • Like (LIKE %value%)
POST /search HTTP/1.1
Content-Type: application/json

{
    "where": {
        "user.name": {
            "_like": "James%"
        }
    }
}
  • By Numerical Range
POST /search HTTP/1.1
Content-Type: application/json

{
    "where": {
        "price.value": {
            "type": "double",
            "_gte": 9.0,
            "_lt": 9.5
        }
    }
}
  • Date Range & Time Range
POST /search HTTP/1.1
Content-Type: application/json

{
    "where": {
        "createTime": {
            "type": "date",
            "_lt": "2017-09-20",
            "_gt": "2017-09-19"
        }
    }
}
  • In
POST /search HTTP/1.1
Content-Type: application/json

{
    "where": {
        "status": {
            "_in": ["Deleted", "Invalid"]
        }
    }
}

Multiple Filters

Multiple filters are joined by AND logic.

POST /search HTTP/1.1
Content-Type: application/json

{
    "where": {
        "name": {
            "_like": "James"
        },
        "status": {
            "_eq": "Valid"
        },
        "createTime": {
            "type": "date",
            "_gte": "2018-09-18"
        }
    }
}

References