Package Exports
- odata-sequelize
This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (odata-sequelize) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.
Readme
odata-sequelize
Objective
This library is intended to take an OData query string as a parameter and transform it on a sequelize-compliant query.
Requirements
- Node.JS
- NPM
- Sequelize.JS
Installing
Simply run a npm command to install it in your project:
npm install sequelize-odata-parserHow does it work?
The OData query string is first parsed by node-odata-parser and then the resulting object is recursively iterated to build a new object that is compliant to sequelize's standard.
Roadmap
WIP
- Test (Mocha)
- Query in children tables
- $expand
Boolean Operators
- AND
- OR
- NOT
Comparison Operators
- Equal (eq)
- Not Equal (ne)
- Greater Than (gt)
- Greater Than or Equal (ge)
- Less Than (lt)
- Less Than or Equal (le)
Functions
- String Functions
- substringof
- endswith
- startswith
- tolower
- toupper
- trim
- concat
- substring
- replace
- indexof
- Date Functions
- day
- hour
- minute
- month
- second
- year
Others
- Complex query with precedence
- top
- select
- filter
- skip
- expand
- query children tables
- test (Mocha)
How to Use
You just need to pass an OData query string as parameter with your sequelize object instance, and automagically it is converted to a sequelize query.
Usage Example:
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$top=5&$skip=1&$select=Foo,Bar&$filter=Foo eq 'Test' or Bar eq 'Test'&$orderby=Foo desc", sequelize);
// Supposing you have your sequelize model
Model.findAll(query);See the examples below to checkout what's created under the hood:
1) Simple Query with Top, Skip, Select, Filter and OrderBy
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$top=5&$skip=1&$select=Foo,Bar&$filter=Foo eq 'Test' or Bar eq 'Test'&$orderby=Foo desc", sequelize);query becomes...
{
attributes: ['Foo', 'Bar'],
limit: 5,
offset: 1,
order: [
['Foo', 'DESC']
],
where: {
[Op.or]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}2) Complex Query with Precedence
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=(Foo eq 'Test' or Bar eq 'Test') and ((Foo neq 'Lorem' or Bar neq 'Ipsum') and (Year gt 2017))", sequelize);query becomes...
{
where: {
[Op.and]: [
{
[Op.or]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
},
{
[Op.and]: [
{
[Op.or]: [
{
Foo: {
[Op.neq]: "Lorem"
},
},
{
Bar: {
[Op.neq]: "Ipsum"
}
}
]
},
{
Year: {
[Op.gt]: 2017
}
}
]
}
]
}
}3) Using Date
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=Foo eq 'Test' and Date gt datetime'2012-09-27T21:12:59'");query becomes...
{
where: {
[Op.and]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Date: {
[Op.gt]: new Date("2012-09-27T21:12:59")
}
}
]
}
}4) startswith function
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=startswith('lorem', Foo) and Bar eq 'Test'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Foo: {
[Op.like]: "lorem%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}5) substringof function
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=substringof('lorem', Foo) and Bar eq 'Test'", sequelize);query becomes...
{
where: {
[Op.and]: [
{
Foo: {
[Op.like]: "%lorem%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}6) startswith function
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=startswith('Foo', Name) and Bar eq 'Test'");query becomes...
{
where: {
[Op.and]: [
{
Name: {
[Op.like]: "Foo%"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}7) trim function
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=trim(Name) eq 'Foo' and Bar eq 'Test'");query becomes...
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "Foo",
attribute: {
fn: "trim",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}8) tolower function
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=tolower(Name) eq 'foobaz' and Name eq 'bar'");query becomes...
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "foobaz",
attribute: {
fn: "lower",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}9) toupper function
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=toupper(Name) eq 'FOOBAZ' and Name eq 'bar'");query becomes...
{
where: {
[Op.and]: [
{
Name: {
comparator: [Op.eq],
logic: "FOOBAZ",
attribute: {
fn: "upper",
args: [
{
col: "Name"
}
]
}
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}10) year, month, day, hour, minute, second function
- The same logic applies to all 6 date functions. The only difference resides in attribute object, whose "fn" property reflects the called function.
var parseOData = require('sequelize-odata-parser');
var sequelize = require('sequelize');
var query = parseOData("$filter=year(StartDate) gt 2017");becomes...
{
where: {
{
StartDate: {
comparator: [Op.gt],
logic: 2017,
attribute: {
fn: "year",
args: [
{
col: "StartDate"
}
]
}
}
}
}
}