query-ql v1.1.5

query-ql

parse http query to sql.

use with pg (es6)

const res = pg.query(...parsePGQuery(model, query))
const total = pg.query(...parsePGCount(model, query))

parsePGQuery (model, query, pagination = true)

const model = {
  __config__: {
    tableName: 'member',
    pagination: {
      defaultRows: 20,
      maxRows: 30
    }
  },
  id: {
    type: 'number',
    name: 'id'
  },
  name: {
    type: 'string',
    name: 'name'
  },
  age: {
    type: 'number',
    name: 'age'
  },
  createTime: {
    type: 'number',
    name: '_create_time'
  },
  other: 'this property is unused'
}

const query = {
  id: ['[0, 10]', '<-1, 11, 32>'],
  name: ['%name', 'name', ''],
  age: [],
  createTime: ['[200, 1000]'],
  sorter: '-id, +name, age, -createTime',
  rows: '20',
  page: '2'
}

const sql = parsePGQuery(model, query)
// sql is array
// sql[0] is 'SELECT id, name, age, _create_time FROM member WHERE (((id >= $1 AND id <= $2) OR (id = $3 OR id = $4 OR id = $5))) AND ((name LIKE $6 OR name = $7)) AND (((_create_time >= $8 AND _create_time <= $9))) ORDER BY id DESC, name ASC, age ASC, _create_time DESC OFFSET $10 LIMIT $11',
// sql[1] is [0, 10, -1, 11, 32, '%name', 'name', 200, 1000, 20, 20]

const sql2 = parsePGQuery(mdoel, query, false)
// sql is array
// sql[0] is 'SELECT id, name, age, _create_time FROM member WHERE (((id >= $1 AND id <= $2) OR (id = $3 OR id = $4 OR id = $5))) AND ((name LIKE $6 OR name = $7)) AND (((_create_time >= $8 AND _create_time <= $9))) ORDER BY id DESC, name ASC, age ASC, _create_time DESC'
// sql[1] is [0, 10, -1, 11, 32, '%name', 'name', 200, 1000]

parsePGCount (model, query)

const sql = parsePGCount(model, query)
// sql[0] is 'SELECT COUNT(id)::INTEGER FROM member WHERE (((id >= $1 AND id <= $2) OR (id = $3 OR id = $4 OR id = $5))) AND ((name LIKE $6 OR name = $7)) AND (((_create_time >= $8 AND _create_time <= $9)))
// sql[1] is [0, 10, -1, 11, 32, '%name', 'name', 200, 1000]

parseWhere (model, query)

const model = {
  id: {
    type: 'number',
    name: 'id'
  },
  name: {
    type: 'string',
    name: 'name'
  },
  age: {
    type: 'number',
    name: 'age'
  },
  createTime: {
    type: 'number',
    name: '_create_time'
  }
}

const query = {
  id: ['[0, 10]', '<-1, 11, 32>'],
  name: ['%name', 'name', ''],
  age: [],
  createTime: ['[200, 1000]']
}

const sql = parseWhere(model, query)
// the sql is: ['(((id >= $ AND id <= $) OR (id = $ OR id = $ OR id = $))) AND ((name LIKE $ OR name = $)) AND (((_create_time >= $ AND _create_time <= $)))', [0, 10, -1, 11, 32, '%name', 'name', 200, 1000]]

parseSorters (model, query)

const model = {
  id: {
    type: 'number',
    name: 'id'
  },
  name: {
    type: 'string',
    name: 'name'
  },
  age: {
    type: 'number',
    name: 'age'
  },
  createTime: {
    type: 'number',
    name: '_create_time'
  }
}
const query = {
  sorter: '-id, +name, age, -createTime'
}
const sorter = parseSorters(model, query)
// sorter is: 'id DESC, name ASC, age ASC, _create_time DESC'

pattern

interval

?id=[0, 10] -> 0 <= id <= 10

?id=[0, 10) -> 0 <= id < 10

?id=(0, 10) -> 0 < id < 10

?id=(0, 10] -> 0 < id <= 10

?id=(0,] -> 0 < id

?id=(0,) -> 0 < id

?id=[0,] -> 0 <= id

?id=[0,) -> 0 <= id

?id=(, 10] -> id <= 10

?id=[, 10] -> id <= 10

?id=(, 10) -> id < 10

?id=[, 10) -> id < 10

equal

?id=10 -> id = 10

?name=foo -> name = 'foo'

like

?name=%foo -> name LIKE '%foo'

or

?id=<0, 1, 5> -> id = 0 OR id = 1 OR id = 5

?name=<foo, %bar> -> name = 'foo' OR name LIKE '%bar'