Sequelice-Query

Sequelice-Query is a helpers that simplify querying Sequelize from Query URL

Installation

$ npm install --save sequelice-query

Query String

Sequelice-Query read key filtered and sorted from URL Query for example:

http://example.com?filtered=[{"id":"firstName", "value":"john"}]&sorted=[{"id":"id", "desc":true}]

both of them is JSON Stringify Array and it's contain object:

Key	Value	Object Key
filtered	Array Object	id, value
sorted	Array Object	id, desc

filtered=[{ 
    "id": "firstName", // for column name
    "value": "john" // for value 
}]

sorted=[{
    "id": "id", // for column name
    "desc": true // boolean value true/false 
}]

Quick Usage

const models = require('../models')
const sQuery = require('sequelice-query')
const Departement = models.Departement
 
async function getDepartement(req, res) {
    const condition = await sQuery.generate({
        req,
        model: Departement,
    })
 
    const { include, queryFilter: where, querySort: order } = condition
 
    const data = await Departement.findAll({
        include,
        where,
        order,
    })
 
    return res.status(200).json({data})
}
 

API

.generate({req, model, configs})

.generate({
req: object req express, 
model: Model, 
configs: object
})

const sQuery = require('sequelice-query')
const models = require('../models')
const User = models.User
const Role = models.Role
 
async function getUser(req, res) {
  const condition = await sQuery.generate({
    req,
    model: User,
    configs: {
      include: [
        { model: Role }
      ],
      optFilter: {
        transformValue: [
            sQuery.Helpers.handlePrefix('between', (args) => {
                const { key, value } = args
                const [from, to] = value
                return sequelize.literal(
                    `${sQuery.Helpers.getColumnQueryKey(
                        key
                    )} between '${from}' and '${to}'`
                )
            }),
        ],
        customIncludeOptions: {
            ['Role']: {
                required: false,
            },
        },        
      }
    }
  }) 
}

configs

These are the available config options

key	value	description
include	Array Object/Model	use as usual in the sequelize
optFilter	Object	optFilter options
optSort	Object	optSort options

lets say we have table and data like this for example:

Departement

id	name	createdAt	updatedAt
1	WiGen	2019-08-24 20:09:04	2019-08-24 20:09:04
2	Niku	2019-08-24 20:14:57	2019-08-24 20:14:57
3	Kuma	2019-08-24 20:09:37	2019-08-24 20:09:40

optFilter options and example

key	value
initValues	object
defaultValues	object
isSkipKey	function
customIncludeOptions	object
transformValue	Function, Array Function
transformValueByKey	object
transformKey	Function, Array Function
transformKeyByKey	object

#initValues: object

GET URL: /departement

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            initValues: {
                id: 1,
            },
        },
    },
})
 
const { include, queryFilter: where, querySort: order } = condition
 
const data = await Departement.findAll({
    include,
    where,
    order,
})
 
return data
 
/*
result
data: [
    {
        "id": 1,
        "name": "WiGen",
        "createdAt": "2019-08-24T20:09:04.000Z",
        "updatedAt": "2019-08-24T20:09:04.000Z"
    }
]
*/

so basically initValues is just initialization your condition if there's no filter condition request by user

#defaultValues: object

GET URL: /departement

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            defaultValues: {
                name: 'u',
            },
        },
    },
})
 
const { include, queryFilter: where, querySort: order } = condition
 
const data = await Departement.findAll({
    include,
    where,
    order,
})
 
return data
 
/*
result
"data": [
    {
        "id": 2,
        "name": "Niku",
        "createdAt": "2019-08-24T20:14:57.000Z",
        "updatedAt": "2019-08-24T20:14:57.000Z"
    },
    {
        "id": 3,
        "name": "Kuma",
        "createdAt": "2019-08-24T20:09:37.000Z",
        "updatedAt": "2019-08-24T20:09:40.000Z"
    }
]
*/

if no condition for name then the default values is LIKE %u%

#isSkipKey: function(args)

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            isSkipKey: (args) => {
                /*
                    { args:
                       { key: 'DepartementEmployees.id',
                         value: 5,
                         configs:
                          { isSkipKey: [Function: isSkipKey],
                            transformValue: [Array],
                            customIncludeOptions: [Object] },
                         include: [ [Object], [Object] ] } 
                    }
                */
 
                // return true to skip the key
                return true
            },
        },
    },
})

#customIncludeOptions: function(args)

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        include: [
            {
                model: DepartementEmployee,
                //customIncludeOptions will goes here
            }
        ],
        optFilter: {
            customIncludeOptions: {
                // Model name if its 1:M than use plural if 1:1 use singular name
                // or u can see from result data, what is properties name for that model
                ['DepartementEmployees']: args => {
                    /*
                        { args:
                             { configs:
                                    { transformValue: [Array],
                                        customIncludeOptions: [Object],
                                        transformKey: [Function: handleIncludeTransfromKey] },
                                 modelName: 'DepartementEmployees',
                                 modelPath: 'DepartementEmployees',
                                 model: DepartementEmployee,
                                 handledKeys: [ 'DepartementEmployees.id' ],
                                 filtered: [ [Object] ] }
                         }
                     */
 
                    // return custom obj include
                    return {
                        required: false,
                    }
                },
            },
        },
    },
})

#transformValue: array function (args, cont) | function (args, cont)

args = {
    key: 'Departement.id',
    value: [ 1, 3 ], 
    configs: { ... }, //configs optFilter
    include: [], //include model
    model: Departement, // Model Object
    newKey: 'between$id', //generated from transformKeyByKey
    type: INTEGER //type column STRING/INTEGER/DATE .etc
}
 
/*
   return cont value if you want to continue to next function transformValue
   or handle by default,
   if it's return undefined then it will not handle anything nor 
   to next function transformValue
*/
cont 
 

GET URL: /departement?filtered=[{"id":"between$id", "value":[2, 3]}]

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            transformValue: [
                sQuery.Helpers.handlePrefix('between', (args, cont) => {
                    const { key, value } = args
                    // handle only with column 'Departement.id'
                    if (key === 'Departement.id') {
                        const [from, to] = value
                        return sequelize.literal(
                            `${sQuery.Helpers.getColumnQueryKey(
                                key
                            )} between '${from}' and '${to}'`
                        )
                    }
                    /*
                       return cont value if you want to continue to next function transformValue
                       or handle by default,
                       if it's return undefined then it will not handle anything nor 
                       to next function transformValue
                    */
                    return cont 
                }),
            ],
        },
    },
})
 
const { include, queryFilter: where, querySort: order } = condition
 
const data = await Departement.findAll({
    include,
    where,
    order,
})
 
return data
 
/*
result
"data": [
    {
        "id": 2,
        "name": "Niku",
        "createdAt": "2019-08-24T20:14:57.000Z",
        "updatedAt": "2019-08-24T20:14:57.000Z"
    },
    {
        "id": 3,
        "name": "Kuma",
        "createdAt": "2019-08-24T20:09:37.000Z",
        "updatedAt": "2019-08-24T20:09:40.000Z"
    }
]
*/

#transformValueByKey: object, value: function (args, cont)

GET URL: /departement?filtered=[{"id":"between$id", "value":[2, 3]}]

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            transformValueByKey: {
                ['between$id']: args => {
                    const { value } = args
                    const [from, to] = value
                    return sequelize.literal(
                        `${sQuery.Helpers.getColumnQueryKey(
                            'id'
                        )} between '${from}' and '${to}'`
                    )
                },
            },
        },
    },
})
 
const { include, queryFilter: where, querySort: order } = condition
 
const data = await Departement.findAll({
    include,
    where,
    order,
})
 
return data
 
/*
result
"data": [
    {
        "id": 2,
        "name": "Niku",
        "createdAt": "2019-08-24T20:14:57.000Z",
        "updatedAt": "2019-08-24T20:14:57.000Z"
    },
    {
        "id": 3,
        "name": "Kuma",
        "createdAt": "2019-08-24T20:09:37.000Z",
        "updatedAt": "2019-08-24T20:09:40.000Z"
    }
]
*/

it's same like transformValue but with spesific key

#transformKey: function (args, cont) | array function (args, cont)

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            transformKey: [
                (args, cont) => {
                    const { key } = args
                    if (key === 'between$id') {
                        return 'id'
                    }
                    /*
                        return cont to continue to next transformKey function
                        or to handle by default
                        if it's return undefined then it will not handle anything nor
                        to next function transformValue
                     */
                    return cont
                },
            ],
            transformValueByKey: {
                ['between$id']: args => {
                    const { newKey, value } = args // newKey is from transformKeyByKey
                    const [from, to] = value
                    return sequelize.literal(
                        `${sQuery.Helpers.getColumnQueryKey(
                            newKey
                        )} between '${from}' and '${to}'`
                    )
                },
            },
        },
    },
})
 
const { include, queryFilter: where, querySort: order } = condition
 
const data = await Departement.findAll({
    include,
    where,
    order,
})
 
return data

#transformKeyByKey: object, value: function (args) | string

const condition = await sQuery.generate({
    req,
    model: Departement,
    configs: {
        optFilter: {
            transformKeyByKey: {
                ['between$id']: (args) => {
                    // args:
                    // { key: 'between$id',
                    // 	value: [ 2, 3 ],
                    // 	configs:
                    // 	{ transformKeyByKey: [Object], transformValueByKey: [Object] },
                    // 	include: [],
                    // 	model: Departement }
                    
                    // do whatever you want to transform key
                    return 'id'
                },
            },
            transformValueByKey: {
                ['between$id']: args => {
                    const { newKey, value } = args // newKey is from transformKeyByKey
                    const [from, to] = value
                    return sequelize.literal(
                        `${sQuery.Helpers.getColumnQueryKey(
                            newKey
                        )} between '${from}' and '${to}'`
                    )
                },
            },
        },
    },
})

//or simply just passing string
transformKeyByKey: {
    ['between$id']: 'id',
}

optSort options and example

key	value
initValues	object
defaultValues	object
transformValue	Function, Array Function
transformValueByKey	object
transformDesc	Function, Array Function