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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
//define Product entity var Product = $data.define("Product", { ProductID: { key: true, type: 'integer', computed: true }, ProductName: String, Category: { type: 'Category', inverseProperty: 'Products' } }); //define Category entity var Category = $data.define("Product", { CategoryID: { key: true, type: 'integer', computed: true }, CategoryName: String, Products: { type: Array, elementType: Product, inverseProperty: 'Category' } }); //define database var Database = $data.EntityContext.extend("Database", { Products: { type: $data.EntitySet, elementType: Product }, //setDefinition Categories: { type: $data.EntitySet, elementType: Category } //setDefinition }); |
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’:
1 2 3 4 5 6 7 8 9 10 11 |
var database = new Database({ provider: 'local', databaseName: 'db' }); database.onReady(function () { //work with database here. database.Products //Products is a set definition. .filter("it.CategoryId == 12") .filter("it.ProductName == pname", {pname: ‘Chai’}) .forEach(function (item) { //render item }); }); |
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
1 2 3 4 5 6 7 8 9 10 11 |
setDefinition .include (predicate ) [0..n] .filter(predicate) [0..n] .orderBy ( predicate ) [0..n] .take ( number ) [0..1] .skip( number ) [0..1] .map( projector ) [0..1] .forEach ( processor ) | .toArray( arrayProcessor ) [1..1] predicate => function (item, parameters) { /*Boolean valued expression */; } projector => function (item, parameters) { /*Object literal expression*/; } processor => function (item) { processItem(item); } |
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.
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
1 2 3 4 5 |
<script> northwind.Products.toArray(function (products) { console.dir(products); }); </script> |
result
1 2 3 4 5 6 7 8 9 |
0 : NorthwindModel.Product(1):Chai, 1 : NorthwindModel.Product(2):Chang, 2 : NorthwindModel.Product(3):Aniseed Syrup, 3 : NorthwindModel.Product(4):Chef Anton's Cajun Seasoning, 4 : NorthwindModel.Product(5):Chef Anton's Gumbo Mix, 5 : NorthwindModel.Product(6):Grandma's Boysenberry Spread, 6 : NorthwindModel.Product(7):Uncle Bob's Organic Dried Pears, 7 : NorthwindModel.Product(8):Northwoods Cranberry Sauce, 8 : NorthwindModel.Product(9):Mishi Kobe Niku, |
Generated requests
OData
1 |
http://services.odata.org/Northwind.svc/Products |
webSQL
1 |
SELECT T0.* FROM [Products] T0 |
forEach()
source code
1 2 3 4 5 |
<script> northwind.Products.forEach(function (product) { console.log(product.ProductName); }); </script> |
Result
1 2 3 4 5 6 |
LOG: Chai LOG: Chang LOG: Aniseed Syrup LOG: Chef Anton's Cajun Seasoning LOG: Chef Anton's Gumbo Mix LOG: Grandma's Boysenberry Spread |
Translated query
OData
1 |
http://services.odata.org/Northwind.svc/Products |
webSQL
1 |
SELECT T0.* FROM [Products] T0 |
first() / single() – find the first/only entity of a set
source code
1 2 3 4 5 |
<script> northwind.Products.first(null, null, function (product) { console.dir(product); }); </script> |
Result
1 2 3 4 5 6 |
LOG: NorthwindModel.Product(1):Chai { isValidated : false, ProductID : 1, ProductName : "Chai", SupplierID : 1, CategoryID : 1, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products$top=1 |
webSQL
1 |
SELECT T0.* FROM [Products] T0 LIMIT 1 |
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
1 2 3 4 5 6 7 |
<script> northwind.Products.single( function (product) { return product.ProductID == 42 }, {}, function (product) { console.dir(product); }); </script> |
Result
1 2 3 4 5 6 7 |
LOG: NorthwindModel.Product(42):Singaporean Hokkien Fried Mee { isValidated : false, ProductID : 42, ProductName : "Singaporean Hokkien Fried Mee", SupplierID : 20, CategoryID : 5, ... |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID eq 42)&top=2 |
webSQL
1 2 3 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductId = ? LIMIT 2 Params: [42] |
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
1 2 3 4 5 6 7 |
<script> northwind.Products .filter(function (product) { return product.ProductName == 'Chai'; }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 |
0 : NorthwindModel.Product(1):Chai |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’) |
webSQL
1 2 3 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductName like ? Params: [‘Chai%’] |
filter() – simple 1 – using lambda expressions
source code for modern browsers
1 2 3 4 5 6 7 |
<script> northwind.Products .filter(it => it.ProductName == 'Chai') .toArray(function (products) { console.dir(products); }); </script> |
Result
1 |
0 : NorthwindModel.Product(1):Chai |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’) |
webSQL
filter() – simple 2
source code
1 2 3 4 5 6 7 |
<script> northwind.Products .filter(function (product) { return product.ProductID > 7 ; }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 7 8 |
0 : NorthwindModel.Product(8):Northwoods Cranberry Sauce, 1 : NorthwindModel.Product(9):Mishi Kobe Niku, 2 : NorthwindModel.Product(10):Ikura, 3 : NorthwindModel.Product(11):Queso Cabrales, 4 : NorthwindModel.Product(12):Queso Manchego La Pastora, 5 : NorthwindModel.Product(13):Konbu, 6 : NorthwindModel.Product(14):Tofu, 7 : NorthwindModel.Product(15):Genen Shouyu, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ? |
1 |
Params: [7] |
filter() – simple 3
Filtering with a logical operator.
source code
1 2 3 4 5 6 7 8 9 |
<script> northwind.Products .filter(function (product) { return product.ProductID > 7 && product.ProductName.contains("Tofu"); }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 |
0 : NorthwindModel.Product(14):Tofu, 1 : NorthwindModel.Product(74):Longlife Tofu, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=substringof(ProductName,’Tofu’) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductName like ? |
1 |
Params: [‘%Tofu%’] |
filter() – simple 4
Filtering for a set of values
source code
1 2 3 4 5 6 7 8 9 |
<script> northwind.Products .filter(function (product) { return product.ProductID in [1,5,7,8,23,68]; }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 |
0 : NorthwindModel.Product(1):Chai, 1 : NorthwindModel.Product(5):Chef Anton's Gumbo Mix, 2 : NorthwindModel.Product(7):Uncle Bob's Organic Dried Pears, 3 : NorthwindModel.Product(8):Northwoods Cranberry Sauce, 4 : NorthwindModel.Product(23):Tunnbröd, 5 : NorthwindModel.Product(68):Scottish Longbreads, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID eq 1) or |
1 |
(ProductID eq 5) or (ProductID eq 7) … |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.Product_ID in (?,?,?,?,?,?) |
1 |
Params: [1,5,7,8,23,68] |
filter() – simple 5
Using parameters instead of constant values
source code
1 2 3 4 5 6 7 8 9 |
<script> northwind.Products .filter(function (product) { return product.ProductID == this.id || product.ProductName == this.name; }, { id: 17, name: 'Chai' }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 |
0 : NorthwindModel.Product(1):Chai, 1 : NorthwindModel.Product(17):Alice Mutton, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID eq 17) or |
1 |
(ProductName eq ‘Chai’)) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.Product_ID in (?,?,?,?,?,?) |
1 |
Params: [1,5,7,8,23,68] |
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
1 2 3 4 5 |
<script> northwind.Products .filter("it.ProductID == this.id || it.ProductName == this.name", { id: 17, name: 'Chai' }) .forEach(function (product) { console.log(product); }); </script> |
Result
1 2 |
0 : NorthwindModel.Product(1):Chai, 1 : NorthwindModel.Product(17):Alice Mutton, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID eq 17) or |
1 |
(ProductName eq ‘Chai’)) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.Product_ID in (?,?,?,?,?,?) |
1 |
Params: [1,5,7,8,23,68] |
filter() – filtering on navigation properties
source code
1 2 3 4 5 6 7 |
<script> northwind.Products .filter(function (product) { return product.Category.CategoryName == 'Beverages'; }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 |
0 : NorthwindModel.Product(1):Chai, 1 : NorthwindModel.Product(17):Alice Mutton, |
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
1 2 3 4 5 6 7 8 9 |
<script> northwind.Products .filter(function (product) { return product.Category.CategoryName == 'Beverages' && product.Discontinued == true; }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 |
0 : NorthwindModel.Product(24):Guaraná Fantástica, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((Category/CategoryName eq 'Beverages') and (Discontinued eq true)) |
webSQL
1 2 3 |
SELECT T0.* FROM [Products] T0 LEFT OUTER JOIN [Categories] T1 ON T0.CategoryID = T1.CategoryID WHERE T1.CategoryName = ? AND T0.Discontinued = ? |
1 |
Params: ['Beverages',1] |
filter() – complex expression with operator grouping
Logical operator precedence and grouping
source code
1 2 3 4 5 6 7 8 9 10 |
<script> northwind.Products .filter(function (product) { return (product.ProductID > 5 && product.ProductName.startsWith('Tofu')) || (product.Category.CategoryID > 5 && product.Category.CategoryID < 20); }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 7 |
0 : NorthwindModel.Product(7):Uncle Bob's Organic Dried Pears, 1 : NorthwindModel.Product(9):Mishi Kobe Niku, 2 : NorthwindModel.Product(10):Ikura, 3 : NorthwindModel.Product(13):Konbu, 4 : NorthwindModel.Product(14):Tofu, 5 : NorthwindModel.Product(17):Alice Mutton, ... |
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
1 2 3 4 5 6 7 8 9 |
<script> northwind.Products .filter(function (product) { return product.ProductID + product.Category.CategoryID > 20 }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 |
0 : NorthwindModel.Product(13):Konbu, 1 : NorthwindModel.Product(14):Tofu, 2 : NorthwindModel.Product(17):Alice Mutton, 3 : NorthwindModel.Product(18):Carnarvon Tigers, 4 : NorthwindModel.Product(19):Teatime Chocolate Biscuits, 5 : NorthwindModel.Product(20):Sir Rodney's Marmalade, |
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
1 2 3 4 5 6 7 8 9 10 11 |
<script> northwind.Products .filter(function (product) { return product.ProductName.toLowerCase.substr(0, 5).contains('hai') || (product.Category.CategoryName.endsWith('ages') && ! (product.ProductName.length() > 15)) }) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 |
0 : NorthwindModel.Product(13):Konbu, 1 : NorthwindModel.Product(14):Tofu, 2 : NorthwindModel.Product(17):Alice Mutton, 3 : NorthwindModel.Product(18):Carnarvon Tigers, 4 : NorthwindModel.Product(19):Teatime Chocolate Biscuits, 5 : NorthwindModel.Product(20):Sir Rodney's Marmalade, |
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
1 2 3 4 5 6 7 |
<script> northwind.Products .filter('.it.ProductID > 7') .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 7 8 |
0 : NorthwindModel.Product(8):Northwoods Cranberry Sauce, 1 : NorthwindModel.Product(9):Mishi Kobe Niku, 2 : NorthwindModel.Product(10):Ikura, 3 : NorthwindModel.Product(11):Queso Cabrales, 4 : NorthwindModel.Product(12):Queso Manchego La Pastora, 5 : NorthwindModel.Product(13):Konbu, 6 : NorthwindModel.Product(14):Tofu, 7 : NorthwindModel.Product(15):Genen Shouyu, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ? |
1 |
Params: [7] |
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
1 2 3 4 5 6 7 |
<script> northwind.Products .filter('.it.ProductID > minValue', {minValue: 7}) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 7 8 |
0 : NorthwindModel.Product(8):Northwoods Cranberry Sauce, 1 : NorthwindModel.Product(9):Mishi Kobe Niku, 2 : NorthwindModel.Product(10):Ikura, 3 : NorthwindModel.Product(11):Queso Cabrales, 4 : NorthwindModel.Product(12):Queso Manchego La Pastora, 5 : NorthwindModel.Product(13):Konbu, 6 : NorthwindModel.Product(14):Tofu, 7 : NorthwindModel.Product(15):Genen Shouyu, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ? |
1 |
Params: [7] |
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
1 2 3 4 5 6 7 |
<script> northwind.Products .filter('Product_ID', '>', 7) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 2 3 4 5 6 7 8 |
0 : NorthwindModel.Product(8):Northwoods Cranberry Sauce, 1 : NorthwindModel.Product(9):Mishi Kobe Niku, 2 : NorthwindModel.Product(10):Ikura, 3 : NorthwindModel.Product(11):Queso Cabrales, 4 : NorthwindModel.Product(12):Queso Manchego La Pastora, 5 : NorthwindModel.Product(13):Konbu, 6 : NorthwindModel.Product(14):Tofu, 7 : NorthwindModel.Product(15):Genen Shouyu, |
translated query
OData
1 |
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7) |
webSQL
1 2 |
SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ? |
1 |
Params: [7] |
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
1 2 3 4 5 6 7 |
<script> northwind.Products .map( function (product) { return product.ProductID } ) .toArray(function (products) { console.dir(products); }); </script> |
Result
1 |
LOG: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 |
Translated query
oData | http://services.odata.org/Northwind/Northwind.svc/Products?$select=ProductID |
webSql | |
map() – return an anonymous type
1 2 3 4 5 6 7 8 9 10 |
<script> northwind.Products .map(function (product) { return { ID: product.ProductID, Name: product.ProductName } }) .toArray(function (products) { console.dir(products[0]); console.dir(products); }); </script> |
Result
1 2 3 4 5 6 7 8 9 |
LOG: { ID : 1, Name : "Chai" } LOG:{ 0 : [object Object], 1 : [object Object], 2 : [object Object], ... |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<script> northwind.Products .map(function (product) { return { ProductID: product.ProductID, ProductName: product.ProductName, CategoryName: product.Category.CategoryName, CompanyName: product.Supplier.CompanyName, ContactName: product.Supplier.ContactName, } }) .toArray(function (products) { console.dir(products[0]); }); </script> |
Result
1 2 3 4 5 6 7 8 |
LOG: { ProductID : 1, ProductName : "Chai", CategoryName : "Beverages", CompanyName : "Exotic Liquids", ContactName : "Charlotte Cooper" } |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<script> northwind.Products .map(function (product) { return { ProductID: product.ProductID, Category: product.Category, Supplier: product.Supplier } }) .toArray(function (products) { console.dir(products[0]); console.dir(products[0].Category); console.dir(products[0].Supplier); }); </script> |
Result
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
LOG: { ProductID : 1, Category : NorthwindModel.Category(1):Beverages, Supplier : NorthwindModel.Supplier(1):Charlotte Cooper@Exotic Liquids } LOG: NorthwindModel.Category(1):Beverages { CategoryID : 1, CategoryName : "Beverages", Description : "Soft drinks, coffees, teas, beers, and ales", Picture : "FRwvAAIAAAANAA4AFAAhAP///..., Products : undefined, ... LOG: NorthwindModel.Supplier(1):Charlotte Cooper@Exotic Liquids { SupplierID : 1, CompanyName : "Exotic Liquids", ContactName : "Charlotte Cooper", ... |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<script> northwind.Products .map(function (product) { return { ProductID: product.ProductID, ProductName: product.ProductName, ProductCode: product.productName.substr(0,5).concat(product.ProductID) } }) .toArray(function (products) { console.dir(products[0]); console.dir(products[0].Category); console.dir(products[0].Supplier); }); </script> |
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
1 2 3 4 5 |
<script> northwind.Products .orderBy(function(product) { return product.ProductName }) .forEach(function(product) { console.log(product) }); </script> |
Result
1 2 3 4 5 6 |
LOG: NorthwindModel.Product(17):Alice Mutton LOG: NorthwindModel.Product(3):Aniseed Syrup LOG: NorthwindModel.Product(40):Boston Crab Meat LOG: NorthwindModel.Product(60):Camembert Pierrot LOG: NorthwindModel.Product(18):Carnarvon Tigers LOG: NorthwindModel.Product(1):Chai |
Translated query
oData | http://services.odata.org/Northwind/Northwind.svc/Products?$orderby=ProductName |
webSql | |
orderBy() – sort by column referred by name
source code
1 2 3 4 |
<script> northwind.Products.orderBy("it.ProductName") .forEach(function(product) { console.log(product) }); </script> |
Result
1 2 3 4 5 6 |
LOG: NorthwindModel.Product(17):Alice Mutton LOG: NorthwindModel.Product(3):Aniseed Syrup LOG: NorthwindModel.Product(40):Boston Crab Meat LOG: NorthwindModel.Product(60):Camembert Pierrot LOG: NorthwindModel.Product(18):Carnarvon Tigers LOG: NorthwindModel.Product(1):Chai |
Translated query
oData | http://services.odata.org/Northwind/Northwind.svc/Products?$orderby=ProductName |
webSql | |
orderByDescending() – sort by column referred by name
source code
1 2 3 4 |
<script> northwind.Products.orderByDescending("it.ProductName") .forEach(function(product) { console.log(product) }); </script> |
Result
1 2 3 4 5 6 |
LOG: NorthwindModel.Product(17):Alice Mutton LOG: NorthwindModel.Product(3):Aniseed Syrup LOG: NorthwindModel.Product(40):Boston Crab Meat LOG: NorthwindModel.Product(60):Camembert Pierrot LOG: NorthwindModel.Product(18):Carnarvon Tigers LOG: NorthwindModel.Product(1):Chai |
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
1 2 3 4 5 |
<script> northwind.Products .orderBy(function (product) { return product.ProductID + product.Category.CategoryID }) .forEach(function(product) { console.log(product) }); </script> |
Result
1 2 3 4 5 |
LOG: NorthwindModel.Product(3):Aniseed Syrup LOG: NorthwindModel.Product(4):Chef Anton's Cajun Seasoning LOG: NorthwindModel.Product(5):Chef Anton's Gumbo Mix LOG: NorthwindModel.Product(65):Louisiana Fiery Hot Pepper Sauce LOG: NorthwindModel.Product(66):Louisiana Hot Spiced Okra |
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
1 2 3 4 5 |
<script> northwind.Products .order('Product_Name') .forEach(function (product) { console.log(product) }) </script> |
Result
1 2 3 4 5 |
LOG: NorthwindModel.Product(17):Alice Mutton LOG: NorthwindModel.Product(3):Aniseed Syrup LOG: NorthwindModel.Product(40):Boston Crab Meat LOG: NorthwindModel.Product(60):Camembert Pierrot LOG: NorthwindModel.Product(18):Carnarvon Tigers |
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
1 2 3 4 5 |
<script> northwind.Products .order('-Product_Name') .forEach(function (product) { console.log(product) }) </script> |
Result
1 2 3 4 5 |
LOG: NorthwindModel.Product(47):Zaanse koeken LOG: NorthwindModel.Product(64):Wimmers gute Semmelknödel LOG: NorthwindModel.Product(63):Vegie-spread LOG: NorthwindModel.Product(50):Valkoinen suklaa LOG: NorthwindModel.Product(7):Uncle Bob's Organic Dried Pears |
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
1 2 3 4 5 |
<script> context.GetPopularProducts().toArray(function(r) { // your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts |
Service operation with scalar result – Processing the result in success callback
source code
1 2 3 4 5 |
<script> context.GetPopularProducts(function(r) { // your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts |
Service operation with queryable results – Processing the result in a promise
source code
1 2 3 4 5 |
<script> context.GetPopularProducts().toArray().then(function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts |
Service operation with scalar result – Processing the result in a promise
source code
1 2 3 4 5 |
<script> context.GetPopularProducts().then(function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts |
Service operation with parameters – Processing the result success callback
source code
1 2 3 4 5 |
<script> context.GetPopularProducts(stringParam1, intParam2).toArray(function(r) { // your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts?param1=’stringParam1¶m2=intParam2 |
Service operation – Processing the result in a promise
source code
1 2 3 4 5 |
<script> context.GetPopularProducts(param1, param2).then(function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts?param1=’stringParam1¶m2=intParam2 |
Service operation – with named parameters with success callback
source code
1 2 3 4 5 |
<script> context.GetPopularProducts({param1Name: param1, param2Name: param2}, function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts?param1=’stringParam1¶m2=intParam2 |
Service operation – with named parameters with promise
source code
1 2 3 4 5 |
<script> context.GetPopularProducts({param1Name: param1, param2Name: param2}).then(function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/GetPopularProducts?param1=’stringParam1¶m2=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
1 2 3 4 5 |
<script> context.Products.GetPopularProducts(stringParam1, intParam2, function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/Products/GetPopularProducts |
EntitySet-level action – with promise
source code
1 2 3 4 5 |
<script> context.Products.GetPopularProducts(stringParam1, intParam2).then(function(r) { //your code comes here }); </script> |
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
1 2 3 4 5 |
<script> myProduct.SetPrice(param1, param2).then(function(r) { //your code comes here }); </script> |
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
1 2 3 4 5 6 |
<script> myProduct.SetPrice({paramName1: param1, paramName2: param2}) .then(function(r) { //your code comes here }); </script> |
Translated query
oData | http://host/YourService/Todos[82]/SetPrice |