JavaScript Language Query (JSLQ) 101

28 June 2013

JSLQ, featured post

 

Update: RainerAtSpirit published the JSLQ playground, so you can make real-time queries against a remote OData endpoint and analyze the results. The same syntax can be used to perform CRUD operations in SQLite, WebSQL, MongoDB, HTML5 localStorage and IndexedDB datasources. While writing JSLQ statements you should take the underlying DB query capabilities into consideration. – Beware, the page is huge, so you might need to wait until it load.


 

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:

    
    //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’:

 

    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

    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.

 

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
    <script>
        northwind.Products.toArray(function (products) {
            console.dir(products);
        });
    </script> 

 

result
    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
http://services.odata.org/Northwind.svc/Products
webSQL
SELECT T0.* FROM [Products] T0

forEach()

source code
    <script>
        northwind.Products.forEach(function (product) {
            console.log(product.ProductName);
        });
    </script>  

 

Result
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
http://services.odata.org/Northwind.svc/Products
webSQL
SELECT T0.* FROM [Products] T0

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

source code
<script>
    northwind.Products.first(null, null, function (product) {
        console.dir(product);
    });
</script> 
Result
LOG: NorthwindModel.Product(1):Chai {
    isValidated : false,
    ProductID : 1,
    ProductName : "Chai",
    SupplierID : 1,
    CategoryID : 1,

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products$top=1
webSQL
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
<script>
    northwind.Products.single(
        function (product) { return product.ProductID == 42 }, {},
        function (product) {
            console.dir(product);
        });
</script> 
Result
LOG: NorthwindModel.Product(42):Singaporean Hokkien Fried Mee {
    isValidated : false,
    ProductID : 42,
    ProductName : "Singaporean Hokkien Fried Mee",
    SupplierID : 20,
    CategoryID : 5,
...

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID eq 42)&top=2
webSQL
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

    <script>
        northwind.Products
            .filter(function (product) { return product.ProductName == 'Chai'; })
            .toArray(function (products) {
            console.dir(products);
        });
    </script>  

 

Result
0 : NorthwindModel.Product(1):Chai

 

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’)
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.ProductName like ?
Params: [‘Chai%’]

filter() – simple 2

source code
    <script>
        northwind.Products
            .filter(function (product) { return product.ProductID > 7 ; })
            .toArray(function (products) {
            console.dir(products);
        });
    </script>  
 
Result
    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
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?
Params: [7]

filter() – simple 3

Filtering with a logical operator.

source code
<script>
    northwind.Products
        .filter(function (product) {
            return product.ProductID > 7 && product.ProductName.contains("Tofu");
        })
        .toArray(function (products) {
            console.dir(products);
        });
</script>    
Result
    0 : NorthwindModel.Product(14):Tofu,
    1 : NorthwindModel.Product(74):Longlife Tofu,

 

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=substringof(ProductName,’Tofu’)
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.ProductName like ?
Params: [‘%Tofu%’]

filter() – simple 4

Filtering for a set of values

source code
<script>
    northwind.Products
        .filter(function (product) {
            return product.ProductID in [1,5,7,8,23,68];
        })
        .toArray(function (products) {
            console.dir(products);
        });
</script> 
Result
    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
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID eq 1) or 
(ProductID eq 5) or (ProductID eq 7) …
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.Product_ID in (?,?,?,?,?,?)
Params: [1,5,7,8,23,68]

filter() – simple 5

Using parameters instead of  constant values

source code
<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
    0 : NorthwindModel.Product(1):Chai,
    1 : NorthwindModel.Product(17):Alice Mutton,

 

 

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID eq 17) or 
(ProductName eq ‘Chai’))
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.Product_ID in (?,?,?,?,?,?)
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
<script>
    northwind.Products
        .filter("it.ProductID == this.id || it.ProductName == this.name", { id: 17, name: 'Chai' })
        .forEach(function (product) { console.log(product); });
</script>
Result
    0 : NorthwindModel.Product(1):Chai,
    1 : NorthwindModel.Product(17):Alice Mutton,

 

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID eq 17) or 
(ProductName eq ‘Chai’))
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.Product_ID in (?,?,?,?,?,?)
Params: [1,5,7,8,23,68]

filter() – filtering on navigation properties

 

source code
<script>
    northwind.Products
        .filter(function (product) { return product.Category.CategoryName == 'Beverages'; })
        .toArray(function (products) {
            console.dir(products);
        });
</script>
Result
    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
<script>
    northwind.Products
        .filter(function (product) {
            return product.Category.CategoryName == 'Beverages' && product.Discontinued == true;
        })
        .toArray(function (products) {
            console.dir(products);
        });
</script> 
Result
0 : NorthwindModel.Product(24):Guaraná Fantástica,

 

translated query
OData
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((Category/CategoryName eq 'Beverages') and (Discontinued eq true))
 
webSQL
SELECT T0.* FROM [Products] T0 
   LEFT OUTER JOIN [Categories] T1 ON T0.CategoryID = T1.CategoryID
WHERE T1.CategoryName = ? AND T0.Discontinued = ?
Params: ['Beverages',1]

filter() – complex expression with operator grouping

Logical operator precedence and grouping

source code
<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
    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
<script>
    northwind.Products
        .filter(function (product) {
            return product.ProductID + product.Category.CategoryID > 20
        })
        .toArray(function (products) {
            console.dir(products);
        });
</script>
Result
    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
<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
    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
<script>
    northwind.Products
        .filter('.it.ProductID > 7')
        .toArray(function (products) {
            console.dir(products);
        });
</script>  

 

Result
    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
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?
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
<script>
    northwind.Products
        .filter('.it.ProductID > minValue', {minValue: 7})
        .toArray(function (products) {
            console.dir(products);
        });
</script> 

 

Result
    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
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?
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

 

<script>
    northwind.Products
        .filter('Product_ID', '>', 7)
        .toArray(function (products) {
            console.dir(products);
        });
</script>  

 

Result
    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
http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)
webSQL
SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?
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
<script>
    northwind.Products
        .map( function (product) { return product.ProductID } )
        .toArray(function (products) {
            console.dir(products);
        });
</script>
 
Result
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

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

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

<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
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.

<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
<script>
    northwind.Products
        .orderBy(function(product) { return product.ProductName })
        .forEach(function(product) { console.log(product) });
</script> 
Result
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
<script>
    northwind.Products.orderBy("it.ProductName")
        .forEach(function(product) { console.log(product) });
</script>
Result
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
<script>
    northwind.Products.orderByDescending("it.ProductName")
        .forEach(function(product) { console.log(product) });
</script>
Result
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
<script>
    northwind.Products
        .orderBy(function (product) { return product.ProductID + product.Category.CategoryID })
        .forEach(function(product) { console.log(product) });
</script> 
Result
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
<script>
   northwind.Products
    .order('Product_Name')
    .forEach(function (product) { console.log(product) })
</script>

 

Result
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

oDatahttp://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
<script>
   northwind.Products
    .order('-Product_Name')
    .forEach(function (product) { console.log(product) })
</script>

 

Result
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

oDatahttp://services.odata.org/Northwind/Northwind.svc/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

<script>
  context.GetPopularProducts().toArray(function(r) {
    // your code comes here
  });
</script>

 

Translated query
oDatahttp://host/YourService/GetPopularProducts

 

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

source code
<script>
  context.GetPopularProducts(function(r) {
    // your code comes here
  });
</script>

 

Translated query
oDatahttp://host/YourService/GetPopularProducts

 

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

source code
<script>
  context.GetPopularProducts().toArray().then(function(r) {
    //your code comes here
  });
</script>
Translated query
oDatahttp://host/YourService/GetPopularProducts

 

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

source code
<script>
  context.GetPopularProducts().then(function(r) {
    //your code comes here
  });
</script>
Translated query
oDatahttp://host/YourService/GetPopularProducts

Service operation with parameters – Processing the result success callback

source code
<script>
  context.GetPopularProducts(stringParam1, intParam2).toArray(function(r) {
    // your code comes here
  });
</script>
Translated query
oDatahttp://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

 

Service operation – Processing the result in a promise

source code
<script>
  context.GetPopularProducts(param1, param2).then(function(r) {
    //your code comes here
  });
</script>
Translated query
oDatahttp://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

Service operation – with named parameters with success callback

source code
<script>
  context.GetPopularProducts({param1Name: param1, param2Name: param2}, function(r) {
    //your code comes here
  });
</script>
 
Translated query
oDatahttp://host/YourService/GetPopularProducts?param1=’stringParam1&param2=intParam2

 

Service operation – with named parameters with promise

source code
<script>
  context.GetPopularProducts({param1Name: param1, param2Name: param2}).then(function(r) {
    //your code comes here
  });
</script>
 
Translated query
oDatahttp://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
<script>
  context.Products.GetPopularProducts(stringParam1, intParam2, function(r) {
    //your code comes here
  });
</script>

 

Translated query
oDatahttp://host/YourService/Products/GetPopularProducts

 

EntitySet-level action – with promise

source code
<script>
  context.Products.GetPopularProducts(stringParam1, intParam2).then(function(r) {
    //your code comes here
  });
</script>

 

Translated query
oDatahttp://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
<script>
  myProduct.SetPrice(param1, param2).then(function(r) {
    //your code comes here
  });
</script>

 

Translated query
oDatahttp://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
<script>
  myProduct.SetPrice({paramName1: param1, paramName2: param2})
    .then(function(r) {
     //your code comes here
  });
</script>

 

Translated query
oDatahttp://host/YourService/Todos[82]/SetPrice

Share this

categories: JSLQ, OData, WebSQL , SQLite

Leave a comment

comments powered by Disqus