Sequtil

Tool for API Filter and Pagination and Sort to Sequelize Where and Limit and Offset and Order.

Sequtil From RESTful API Specification

Install

npm install sequtil

Quick Start

const sequtil = new Sequtil();

const query = {
  page: 1,
  pageSize: 1,
  sort: 'field1:asc',
  field1: '1',
  filters: JSON.stringify([
    { field: 'field1', op: 'eq', value: 1 }
  ])
};

// pagination
const page = sequtil.genPage(query); // { page: 1, pageSize: 1, limit: 1, offset: 0 }

// order
const order = sequtil.genOrder(query.sort, { field1: null }); // [ [ 'field1', 'asc' ] ]

// where
const where = sequtil.genWhere(query, {
  field1: { type: Sequtil.Types.Number, op: 'eq' }
}); // { field1: { [Symbol(eq)]: 1 } }

const condWhere = sequtil.genCondWhere(query.filters, {
  field1: { type: Sequtil.Types.Number }
}); // { field1: { [Symbol(eq)]: 1 } }

Document

Pagination

// set config
const sequtil = new Sequtil({
  size: 20,
  maxSize: 100,
  minSize: 1
});

const page = sequtil.genPage({
  page: 1,
  pageSize: 101
}); // { page: 1, pageSize: 100, limit: 100, offset: 0 }

// override config
const page = sequtil.genPage({
  page: 1,
  pageSize: 101
}, {
  maxSize: 2
}); // { page: 1, pageSize: 2, limit: 2, offset: 0 }

Sort

const sequtil = new Sequtil();

// default order
const order = sequtil.genOrder(null, { default: ['id', 'ASC'] }); // [ [ 'id', 'ASC' ] ]

const query = {
  id: 123,
  name: 'xx'
};

const order = sequtil.(query.sort, { id: null, name: null }); // [ [ 'id', 'asc' ] ]

Filter

const sequtil = new Sequtil();

const query = {
  id: '123',
  name: 'xx'
};

const where = sequtil.genWhere(query, {
  id: { type: Sequtil.Types.Number, op: Sequtil.Op.eq },
  name: { type: Sequtil.Types.String, op: Sequtil.Op.like }
}); // { id: { [Symbol(eq)]: 123 }, name: { [Symbol(like)]: Literal { val: "'xx%'" } } }

Complex Filter

const sequtil = new Sequtil();

const query = {
  filters: JSON.stringify([
    { field: 'id', op: 'eq', value: [1, 2] },
    { field: 'name', op: 'like', value: 'xx' }
  ])
};

const where = sequtil.genCondWhere(query.filters, {
  id: { type: Sequtil.Types.Number },
  name: { type: Sequtil.Types.String }
}); // { id: { [Symbol(in)]: [ 1, 2 ] }, name: { [Symbol(like)]: Literal { val: "'xx%'" } } }

API

Sequtil.Op

The same as Sequelize operators

Sequtil.Types

The filter type

Type	Description
Date	Symbol('date')
Number	Symbol('number')
String	Symbol('string')

Sequtil(config) Sequtil

Create Sequtil instance

Parameters

• config - Object - The configuration for worker.
• config.size - Int - Default pagination size.
• config.maxSize - Int - Max pagination size.
• config.minSize - Int - Min pagination size.

Return

• Sequtil instance

Sequtil setConfig(config)

Set config

Sequtil getConfig(config)

Get config

Sequtil genOrder(sort, options = {}) Order

Generate order

Parameters

• sort - String - The sort value, 'field1:asc', 'field2:DESC', etc.
• options - Object - The sort value, 'field1:asc', 'field1:asc,field2:DESC', etc.
• options.default - Array - The default Sequelize sort value, ['field1', 'ASC'], etc.
• options.{field} - String - The Sequelize sort filed, 'id', 'name', etc.

Return

• Sequelize Order

Sequtil genPage(params, options = {}) Pagination

Generate pagination

Parameters

• params - Object - The page & pageSize, { page: 1, pageSize: 100 }, etc.
• params.page - Int - Pagination page.
• params.pageSize - Int - Pagination pageSize.
• options - Object - The pagination config.
• options.size - Int - Default pagination size, will override sequtil.config.size.
• options.maxSize - Int - Max pagination size, will override sequtil.config.maxSize.
• options.minSize - Int - Min pagination size, will override sequtil.config.minSize.

Return

• Pagination
• page - Int - Pagination page.
• pageSize - Int - Pagination size.
• limit - Int - Pagination limit.
• offset - Int - Pagination offset.

Sequtil genWhere(filters, definitions) Where

Generate Sequelize where

Parameters

• filters - Object - The filters k-vs, { id: 123, name: 'xx' }, etc.
• definitions - Object{Field-Definition} - The filter definitions, { id: { type: Sequtil.Types.Number, op: Sequtil.Op.eq } }, etc.
• definition - Object - The filter definition.
• definition.col - Object - The table col, optional, default to filed.
• definition.type - Sequtil.Types - The filter type.
• definition.op - Sequtil.Op - The operator.

Return

• Sequelize Where

Sequtil genCondWhere(filters, definitions) Where

Generate Sequelize where

Parameters

• filters - String - The filters string '[fiter1, filter2, ...]', '[{"field":"id","op":"eq","value":1}, ...]', etc.
• filter - Object - Single filter.
• filter.field - String - The filter name.
• filter.op - String - The filter operator, Sequtil.Op string.
• filter.value - Any - The filter value, multiple values use array [v1, v2, ...].
• definitions - Object{Field-Definition} - The filter definitions, { id: { type: Sequtil.Types.Number } }, etc.
• definition - Object - The filter definition.
• definition.col - Object - The table col, optional, default to filed.
• definition.type - Sequtil.Types - The filter type.

Return

• Sequelize Where

Best Practices