odata-query-plus

Just Adding SubstringOf for Sequelize // Kritchapon.C

OData v4 query builder that uses a simple object-based syntax similar to MongoDB and js-data

Install

``
yarn add odata-query-sequelize
`
or
`
npm install --save odata-query-sequelize
`

and then use the library
`js
import buildQuery from 'odata-query-sequelize'

const query = buildQuery({...})
fetch(http://localhost${query})
`
where the query object syntax for {...} is defined below. There is also react-odata which utilizies this library for a declarative React component.

Usage

See tests for examples as well

- Filtering
- Simple equality filter
- Comparison operators
- Logical operators
- Implied and with an array of objects
- Implied and with multiple comparison operators for a single property
- Explicit operator
- Collection operators - any, all
- Implied and with an object or array of objects
- Explicit operator (and, or, and not)
- Functions
- String functions returning boolean
- Functions returning non-boolean values (string, int)
- Functions returning non-boolean values (string, int) with parameters
- Strings
- Data types
- Search
- Selecting
- Ordering
- Expanding
- Nested expand using slash seperator
- Nested expand with an object
- Multiple expands as an array
- Filter expanded items
- Select only specific properties of expanded items
- Return only a subset of expanded items
- Order expanded items
- filter, select, top, and orderBy can be used together
- Pagination (skip and top)
- Single-item (key)
- Counting
- Actions
- Functions
- Transforms

$3


`js
buildQuery({ filter: {...} })
=> '?$filter=...'
`

#### Simple equality filter
`js
const filter = { PropName: 1 };
buildQuery({ filter })
=> '?$filter=PropName eq 1'
`

#### Comparison operators
`js
const filter = { PropName: { gt: 5 } };
buildQuery({ filter })
=> '?$filter=PropName gt 5'
`
Supported operators: eq, ne, gt, ge, lt, le, in

#### Logical operators
##### Implied and with an array of objects
`js
const filter = [{ SomeProp: 1 }, { AnotherProp: 2 }, 'startswith(Name, "foo")'];
buildQuery({ filter })
=> '?$filter=SomeProp eq 1 and AnotherProp eq 2 and startswith(Name, "foo")'
`

##### Implied and with multiple comparison operators for a single property
Useful to perform a between query on a Date property
`js
const startDate = new Date(Date.UTC(2017, 0, 1))
const endDate = new Date(Date.UTC(2017, 2, 1))
const filter = { DateProp: { ge: startDate, le: endDate } }
buildQuery({ filter })
=> "?$filter=DateProp ge 2017-01-01T00:00:00Z and DateProp le 2017-03-01T00:00:00Z"
`

##### Explicit operator
`js
const filter = {
and: [
{ SomeProp: 1 },
{ AnotherProp: 2 },
'startswith(Name, "foo")'
]
};

buildQuery({ filter })
=> '?$filter=SomeProp eq 1 and AnotherProp eq 2 and startswith(Name, "foo")'
`

`js
const filter = {
not: {
and:[
{SomeProp: 1},
{AnotherProp: 2}
]
}
};

buildQuery({ filter })
=> '?$filter=(not (SomeProp eq 1) and (AnotherProp eq 2))'
`

Supported operators: and, or, and not.

#### Collection operators
##### Empty any
Using an empty object
`js
const filter = {
ItemsProp: {
any: {}
}
};

buildQuery({ filter })
=> '?$filter=ItemsProp/any()'
`

or also as an empty array
`js
const filter = {
ItemsProp: {
any: []
}
};

buildQuery({ filter })
=> '?$filter=ItemsProp/any()'
`

##### Implied and

Using an object
`js
const filter = {
ItemsProp: {
any: {
SomeProp: 1,
AnotherProp: 2
}
}
};

buildQuery({ filter })
=> '?$filter=ItemsProp/any(i:i/SomeProp eq 1 and i/AnotherProp eq 2)'
`

or also as an array of object
`js
const filter = {
ItemsProp: {
any: [
{ SomeProp: 1 },
{ AnotherProp: 2},
]
}
};

buildQuery({ filter })
=> '?$filter=ItemsProp/any(i:i/SomeProp eq 1 and i/AnotherProp eq 2)'
`

##### Explicit operator (and, or, and not)
`js
const filter = {
ItemsProp: {
any: {
or: [
{ SomeProp: 1 },
{ AnotherProp: 2},
]
}
}
};

buildQuery({ filter })
=> '?$filter=ItemsProp/any(i:(i/SomeProp eq 1 or i/AnotherProp eq 2)'
`
`js
const filter = {
not: {
ItemsProp: {
any: {
or: [
{ SomeProp: 1 },
{ AnotherProp: 2},
]
}
}
}
};

buildQuery({ filter })
=> '?$filter=not ItemsProp/any(i:((i/SomeProp eq 1) or (i/AnotherProp eq 2)))'
`

Supported operators: any, all

#### Functions
##### String functions returning boolean
`js
const filter = { PropName: { contains: 'foo' } };
buildQuery({ filter })
=> "$filter=contains(PropName,'foo')"
`
##### ADD SubstringOf by Kritchapon.C
Supported operators: startswith, endswith, contains , substringof

##### Functions returning non-boolean values (string, int)
`js
const filter = { 'length(PropName)': { gt: 10 } };
buildQuery({ filter })
=> "$filter=length(PropName) gt 10"
`
Supported operators: length, tolower, toupper, trim,
day, month, year, hour, minute, second,
round, floor, ceiling

##### Functions returning non-boolean values (string, int) with parameters
`js
const filter = { "indexof(PropName, 'foo')": { eq: 3 } };
buildQuery({ filter })
=> "$filter=indexof(PropName, 'foo') eq 3"
`
Supported operators: indexof, substring

#### Strings
A string can also be passed as the value of the filter and it will be taken as is. This can be useful when using something like odata-filter-builder or if you want to just write the OData filter sytnax yourself but use the other benefits of the library, such as groupBy, expand, etc.
`js
import f from 'odata-filter-builder';

const filter = f().eq('TypeId', '1')
.contains(x => x.toLower('Name'), 'a')
.toString();
buildQuery({ filter })
`

#### Data types
GUID:
`js
const filter = { "someProp": { eq: { type: 'guid', value: 'cd5977c2-4a64-42de-b2fc-7fe4707c65cd' } } };
buildQuery({ filter })
=> "?$filter=someProp eq cd5977c2-4a64-42de-b2fc-7fe4707c65cd"
`

Binary:
`js
const filter = { "someProp": { eq: { type: 'binary', value: 'YmluYXJ5RGF0YQ==' } } };
buildQuery({ filter })
=> "?$filter=someProp eq binary'YmluYXJ5RGF0YQ=='"
`
Note that as per OData specification, binary data is transmitted as a base64 encoded string. Refer to Primitive Types in JSON Format, and binary representation.

Other types coming soon

#### Search
`js
const search = 'blue OR green';
buildQuery({ search });
=> '?$search=blue OR green';
`

$3


`js
const select = ['Foo', 'Bar'];
buildQuery({ select })
=> '?$select=Foo,Bar'
`

$3

`js
const orderBy = ['Foo desc', 'Bar'];
buildQuery({ orderBy })
=> '?$orderby=Foo desc,Bar'
`

$3

#### Nested expand using slash seperator
`js
const expand = 'Friends/Photos'
buildQuery({ expand })
=> '?$expand=Friends($expand=Photos)';
`

#### Nested expand with an object
`js
const expand = { Friends: { expand: 'Photos' } }
buildQuery({ expand })
=> '?$expand=Friends($expand=Photos)';
`

#### Multiple expands as an array
Supports both string (with slash seperators) and objects
`js
const expand = ['Foo', 'Baz'];
buildQuery({ expand })
=> '?$expand=Foo,Bar';
`
#### Filter expanded items
`js
const expand = { Trips: { filter: { Name: 'Trip in US' } } };
buildQuery({ expand })
=> "?$expand=Trips($filter=Name eq 'Trip in US')";
`

#### Select only specific properties of expanded items
`js
const expand = { Friends: { select: ['Name', 'Age'] } };
buildQuery({ expand })
=> '?$expand=Friends($select=Name,Age)';
`

#### Return only a subset of expanded items
`js
const expand = { Friends: { top: 10 } };
buildQuery({ expand })
=> '?$expand=Friends($top=10)';
`

#### Order expanded items
`js
const expand = { Products: { orderBy: 'ReleaseDate asc' } };
buildQuery({ expand })
=> "?$expand=Products($orderby=ReleaseDate asc)";
`

#### filter, select, top, and orderBy can be used together
Select only the first and last name of the top 10 friends who's first name starts with "R" and order by their last name
`js
const expand = {
Friends: {
select: ['FirstName', 'LastName'],
top: 10,
filter: {
FirstName: { startswith: 'R' }
},
orderBy: 'LastName asc'
}
};
buildQuery({ expand })
=> '?$expand=Friends($select=Name,Age;$top=10;$filter=startswith eq 'R'))';
`

$3


#### Get page 3 (25 records per page)
`js
const page = 3;
const perPage = 25;
const top = perPage;
const skip = perPage * (page - 1);
buildQuery({ top, skip })
=> '?$top=25&$skip=50'
`

$3

Simple value
`js
const key = 1;
buildQuery({ key })
=> '(1)'
`

As object (explicit key property
`js
const key = { Id: 1 };
buildQuery({ key })
=> '(Id=1)'
`

$3


Include count inline with result
`js
const count = true;
const filter = { PropName: 1}
buildQuery({ count, filter })
=> '?$count=true&$filter=PropName eq 1'
`

Or you can return only the count by passing a filter object to count (or empty object to count all)
`js
const count = { PropName: 1 }
const query = buildQuery({ count })
=> '/$count?$filter=PropName eq 1'
`

$3


Action on an entity
`js
const key = 1;
const action = 'Test';
buildQuery({ key, action })
=> '(1)/Test'
`

Action on a collection
`js
const action = 'Test';
buildQuery({ action })
=> '/Test'
`
Action parameters are passed in the body of the request.

$3


Function on an entity
`js
const key = 1;
const func = 'Test';
buildQuery({ key, func })
=> '(1)/Test'
`

Function on an entity with parameters
`js
const key = 1;
const func = { Test: { One: 1, Two: 2 } };
buildQuery({ key, func })
=> '(1)/Test(One=1,Two=2)'
`

Function on a collection
`js
const func = 'Test';
buildQuery({ func })
=> '/Test'
`

Function on a collection with parameters
`js
const func = { Test: { One: 1, Two: 2 } };
buildQuery({ func })
=> '/Test(One=1,Two=2)'
`




$3


Transforms can be passed as an object or an array (useful when applying the same transform more than once, such as filter)

Aggregations
`js
const transform = {
aggregate: {
Amount: {
with: 'sum',
as: 'Total'
}
}
};
buildQuery({ transform });
=> '?$apply=aggregate(Amount with sum as Total)';
`
Supported aggregations: sum, min, max, average, countdistinct

Group by (simple)
`js
const transform = [{
groupBy: {
properties: ['SomeProp'],
}
}]
buildQuery({ transform });
=> '?$apply=groupby((SomeProp))';
`

Group by with aggregation
`js
const transform = {
groupBy: {
properties: ['SomeProp'],
transform: {
aggregate: {
Id: {
with: 'countdistinct',
as: 'Total'
}
}
}
}
}
buildQuery({ transform });
=> '?$apply=groupby((SomeProp),aggregate(Id with countdistinct as Total))';
`

Group by with filtering before and after
`js
const transform = [{
filter: {
PropName: 1
}
},{
groupBy: {
properties: ['SomeProp'],
transform: [{
aggregate: {
Id: {
with: 'countdistinct',
as: 'Total'
}
}
}]
}
},{
filter: {
Total: { ge: 5 }
}
}]
buildQuery({ transform });
=> '?$apply=filter(PropName eq 1)/groupby((SomeProp),aggregate(Id with countdistinct as Total))/filter(Total ge 5)';
`

Supported transforms: aggregate, groupby, filter`. Additional transforms may be added later

OData specs

- OData Version 4.0. Part 1: Protocol Plus Errata 03
- OData Version 4.0. Part 2: URL Conventions Plus Errata 03
- OData Extension for Data Aggregation Version 4.0