JayData.org

JavaScript Language Query (JSLQ) 101

Author: Peter Aron Zentai April 30th, 2012

, , , ,


This article covers the query capabilities aspects of JayData.

JayData models databases and tables as EntityContext and Entity types. To model a simple database you need to define its entities, and via the sets of entities you define the tables as well.

Define model:

If you work with OData you can have this definition inferred from the metadata document. Read this for details.

Create or open your local database with a database file name of ‘db’:

To initialize an OData connection use ‘oData’ as provider value and instead of the databaseName set oDataServiceHost parameter to your OData service.

The general form is for using the fluent query API is

Bootstrap

This is included to initialize a JSON enabled OData connection to the sample Northwind database publish by Microsoft. The features presented below work across WebSQL/sqLite/OData and in a limited mode ASP.NET WebAPI.

JS Bin

Switch to the HTML pane to see what script files are request, check the JavaScript pane to see the syntax. $data.northwind is defined in the northwind.js file.

Getting results

toArray()

toArray()eturns all the items that has been selected by expressions on its left side. The simplest selection is addressing the collection of all items of a type.

source code

result

Generated requests
OData

webSQL

forEach()

source code

Result

Translated query
OData

webSQL

first() / single() – find the first/only entity of a set

source code

Result

translated query
OData

webSQL

first() / single() – find the first/only entity of a set

Single and first are very similar, only that single throws an exception if there’s more then one item matching the criterion or criteria.

source code

Result

translated query
OData

webSQL

Filtering

The filter() function receives two parameters, a mandatory filter predicate (a function that returns a Boolean value) and an optional filter parameter, presented to the predicate as the [this].

filter() – simple 1

source code

Result

translated query
OData

webSQL

filter() – simple 1 – using lambda expressions

source code for modern browsers

Result

translated query
OData

webSQL

filter() – simple 2

source code

Result

translated query
OData

webSQL

filter() – simple 3

Filtering with a logical operator.

source code

Result

translated query
OData

webSQL

filter() – simple 4

Filtering for a set of values

source code

Result

translated query
OData

webSQL

filter() – simple 5

Using parameters instead of  constant values

source code

Result

translated query
OData

webSQL

filter() – using strings instead of lambda functions

Notice how “it” represents the actual entity. Using a string to filter is terse however you will not get parser time error checking.

source code

Result

translated query
OData

webSQL

filter() – filtering on navigation properties

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(Category/CategoryName eq ‘Beverages’)
webSql

filter() – filtering on navigation and real properties

source code

Result

translated query
OData

webSQL

filter() – complex expression with operator grouping

Logical operator precedence and grouping

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(((ProductID gt 5) and startswith(ProductName,’Tofu’)) or ((Category/CategoryID gt 5) and (Category/CategoryID lt 20)))
webSql

filter() – filter on multiple fields in an expression

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID add Category/CategoryID) gt 20)
webSql

filter() – filter on field operations

The list of supported field operations depends on the provider. CompatLevel1 operations are the most common to be supported: startsWith, endsWith, contains, toLowerCase, toUpperCase, substr, legnth

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(substringof(‘hai’,substring(tolower(ProductName),0,5)) or (endswith(Category/CategoryName,’ages’) and not((length(ProductName) gt 15))))
webSql

filter() – filter with string syntax

You can pass a string expression to the filter() in string format, where you can reference the entity with the ‘it’ string.

source code

Result

translated query
OData

webSQL

filter() – filter with string syntax using parameters

You can pass a string expression to the filter() in string format, where you can reference the entity with the ‘it’ string. The arguments can be passed in the second parameter of the filter().

source code

Result

translated query
OData

webSQL

filter() – filter with simple builder syntax

The simple builder syntax lets you to write dynamic filter expression generated by code. Read more on this filtering option – Simplified filter syntax for simple task

 

source code

Result

translated query
OData

webSQL

Projection

Projection is the function to shape data on the storage provider side (and not locally) to only transfer data that is necessary thus saving bandwidth and reducing response times. Projection is invoked using the map() function.

map() – return a single field

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$select=ProductID
webSql

map() – return an anonymous type

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$select=ProductID,ProductName
webSql

map() – create a viewmodel with related entity fields in an anonymous type

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$expand=Category&$select=ProductName,Category/CategoryName
webSql

 

map() – return multiple navigation properties as Viewmodel

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$expand=Category,Supplier&$select=ProductID,Category,Supplier
webSql

map() – use field operations to build a view model

Using expressions with the map operator is supported in the webSql, sqLite providers at the time of writing. Regarding oData this is a protocol limitation of oData V2.

Result

Using expressions within the map operator with oData provider is not supported!

Ordering

You can sort items of a set with the orderyBy() and orderByDescending() query operators. Both take a field name or a field selector expression as argument.

orderBy() – sort by an entity column

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$orderby=ProductName
webSql

orderBy() – sort by column referred by name

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$orderby=ProductName
webSql

orderByDescending() – sort by column referred by name

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$orderby=ProductName
webSql

orderBy() – sort by a complex expression with multiple fields

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Products?$orderby=ProductID add Category/CategoryID
webSql

order() – sort by column referred by name in ascending order

source code

Result

Translated query

oData http://services.odata.org/Northwind/Northwind.svc/Northwind.svc/Products?$orderby=ProductName%20
webSql

order() – sort by column referred by name in ascending order

source code

Result

Translated query

oData http://services.odata.org/V3/Northwind/Northwind.svc/Products?$orderby=ProductName%20desc
webSql

Geo queries

OData V3 also has support for Geography and Geometry data types. In JayData 1.3 we also started to support these data types not only in oData but in local providers, too. Learn how to manage geography data with JayData or try the live Geo examples.

Working with service operations

OData Service operations can be published using ADO.NET Data Services, JayData Server or JayStorm PaaS.

Service operation with queryable results – Processing the result in success callback

source code

Translated query
oData http://host/YourService/GetPopularProducts

Service operation with scalar result – Processing the result in success callback

source code

Translated query
oData http://host/YourService/GetPopularProducts

 

Service operation with queryable results – Processing the result in a promise

source code

Translated query
oData http://host/YourService/GetPopularProducts

Service operation with scalar result – Processing the result in a promise

source code

Translated query
oData http://host/YourService/GetPopularProducts

Service operation with parameters – Processing the result success callback

source code

Translated query
oData http://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

Service operation – Processing the result in a promise

source code

Translated query
oData http://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

Service operation – with named parameters with success callback

source code

Translated query
oData http://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

 

Service operation – with named parameters with promise

source code

Translated query
oData http://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

 

Working with OData actions

OData actions can be published only via ASP.NET WebAPI OData. OData support in ASP.NET Web API –OData Actions

EntitySet-level action – with success callback

EntitySet-level actions are useful if your don’t have the reference to a particular entity or you want to perform your operation on multiple entities.

source code

Translated query
oData http://host/YourService/Products/GetPopularProducts

EntitySet-level action – with promise

source code

Translated query
oData http://host/YourService/Products/GetPopularProducts

Entity-level action with parameters – using promise

Entity-level actions can be used if you already have reference to a JayData entity.

source code

Translated query
oData http://host/YourService/Todos[82]/SetPrice

Entity-level action with named parameters

Entity-level actions can be used if you already have reference to a JayData entity.

source code

Translated query
oData http://host/YourService/Todos[82]/SetPrice

, , , ,