vicnovais/odata-sequelize

Parser library to transform an OData query string to a sequelize-compliant query

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 odata-sequelize

How 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

• 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

1. String Functions

• substringof
• endswith
• startswith
• tolower
• toupper
• trim
• concat
• substring
• replace
• indexof

2. Date Functions

• day
• hour
• minute
• month
• second
• year

Others

• Test (Jest) - Thanks to @remcohaszing
• Lint & Prettier - Thanks to @remcohaszing
• Complex query with precedence
• top
• select
• filter
• skip
• expand
• query children tables

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("odata-sequelize");
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("odata-sequelize");
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("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
  "$filter=(Foo eq 'Test' or Bar eq 'Test') and ((Foo ne 'Lorem' or Bar ne '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.ne]: "Lorem"
                                },
                            },
                            {
                                Bar: {
                                    [Op.ne]: "Ipsum"
                                }
                            }
                        ]
                    },
                    {
                        Year: {
                            [Op.gt]: 2017
                        }
                    }
                ]
            }
        ]
    }
}

3) Using Date

var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
  "$filter=Foo eq 'Test' and Date gt datetime'2012-09-27T21:12:59'",
  sequelize
);

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("odata-sequelize");
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("odata-sequelize");
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("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=startswith('Foo', Name) and Bar eq 'Test'", sequelize);

query becomes...

{
    where: {
        [Op.and]: [
            {
                Name: {
                    [Op.like]: "Foo%"
                }
            },
            {
                Bar: {
                    [Op.eq]: "Test"
                }
            }
        ]
    }
}

7) trim function

var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=trim(Name) eq 'Foo' and Bar eq 'Test'", sequelize);

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("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=tolower(Name) eq 'foobaz' and Name eq 'bar'", sequelize);

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("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=toupper(Name) eq 'FOOBAZ' and Name eq 'bar'", sequelize);

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("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData("$filter=year(StartDate) gt 2017", sequelize);

becomes...

{
    where: {
        {
            StartDate: {
                comparator: [Op.gt],
                logic: 2017,
                attribute: {
                    fn: "year",
                    args: [
                        {
                            col: "StartDate"
                        }
                    ]
                }
            }
        }
    }
}

odata-sequelize's Issues

Filter child collection based on count

We've run into a scenario lately where we're trying to filter a collection based on the count of a child collection.

For example, we want to filter users who are assigned to groups. Based on the OData specification, the $count query option or length operator are available to get counts on collections.

For reference how to use these, refer to:

• Example 32 - 4.8 Addressing the Count of a Collection
• Example 43 - 4.12 Addressing a Subset of a Collection
  • GET /users?$filter=groups/$count gt 0
  • GET /users?$filter=groups/$count
• Example 73 - 5.1.1.5.5 length
  • GET /users?$filter=length(groups) gt 0

Does anyone have thoughts on how something like this might be implemented to a Sequelize query?

Thanks!

substringof does not work when used with and or or

  [Op.and]: [ 
          { [Op.and]: {Foo: '%lorem%'} }, 
          { Bar: { [Op.eq]: 'Test'} }
    ]

The above is obtained instead of

[Op.and]: [
            {
                Foo: {
                    [Op.like]: "%lorem%"
                }
            },
            {
                Bar: {
                    [Op.eq]: "Test"
                }
            }
        ]

var query = parseOData("$filter=substringof('lorem', Foo) and Bar eq 'Test'", sequelize);

filtering with nested functions not working

Using a query string of: ?$filter=substringof('a',tolower(column))

I get the following exception:

TypeError: Cannot read property 'name' of undefined
        at parseFunction (.../node_modules/odata-sequelize/index.js:141:83)
        at parseFunctionCall (.../node_modules/odata-sequelize/index.js:209:7)
        at preOrderTraversal (.../node_modules/odata-sequelize/index.js:222:5)
        at parseFilter (.../node_modules/odata-sequelize/index.js:335:16)
        at module.exports (.../node_modules/odata-sequelize/index.js:380:18)

It looks like the function parser doesn't support nested functions:

odata-sequelize/index.js

Line 141 in 996ab3b

const key = args.filter(t => Object.prototype.hasOwnProperty.call(t, "name"))[0].name;

Error in parsing simple string filters

parseOdata("$filter=startswith('some', intentName)", sequelize)
returns error "Operator not recognized"

Import using typescript

When try to use typescript to import typescript generate this error.

"This module can only be referenced with ECMAScript imports/exports by turning on the 'esModuleInterop' flag and referencing its default export."

Thanks

Include (joins) operation

Is there any way to include relationships?

filter wrong and or paramters

.......?$top=100&$filter=(foo eq '2019-02-12' and bar eq 'TK0001') or (foo eq '2019-02-12' and bar eq 'TK0003')

sequelize generated MSSQL sentece in WHERE

WHERE ((([foo] = N'2019-02-12') OR ([bar] = N'TK0001')) OR  (([foo] = N'2019-02-12') OR ([bar] = N'TK0003'))) ORDER BY [foo] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

my expected

WHERE ((([foo] = N'2019-02-12')  AND  ([bar] = N'TK0001')) OR  (([foo] = N'2019-02-12') AND ([bar] = N'TK0003'))) ORDER BY [foo] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

another one odata

$top=100&$filter=foo eq '2019-02-12' and (bar eq 'TK0001' or bar eq 'TK0003')

generated

WHERE (([foo] = N'2019-02-12') AND (([bar] = N'TK0001') AND ([bar] = N'TK0003'))) ORDER BY [foo] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

my excepted

WHERE (([foo] = N'2019-02-12') AND (([bar] = N'TK0001') OR ([bar] = N'TK0003'))) ORDER BY [foo] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

what wrong? Is bug?

State for $expand

Hi there, what's the state for the $expand implementation?

Functions Error

1. Function Comparison not working
   oData
   odata : ...?$filter=year(OrderDate) gt '2016'

sequelize generated MSSQL sentece in WHERE:
year([OrderDate]) = N'2016'

my expected:
year([OrderDate]) > N'2016'

always run eq..

2)Error : Function fieldName not working:
"Invalid column name 'OrderDate'.",

Model:

OrderDate: {
			type: Sequelize.DataTypes.DATEONLY,
			allowNull:false,
			unique:true,
			primaryKey:true,
			field:'Order Date' //my defination
		}