JavaScript Language Query (JSLQ) 101

Author: Peter Áron Zentai
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    





    


    


                    

                    


                        

    
    

  • 

    

    

    

    Use your Javascript skills on server-side!
    
TRY IT NOW  

     
    

    

    

    

    • 

  • 

    

    

    

    Are You A Pro? Learn more about how JayData can take your apps to the next level.
    
This way for more!  

     
    

    

    

    

    • 






    

            
Contents

        
    

    



    


Download Jaydata


Get the free, opensource JayData Library and try it now!

Download