JavaScript Language Query (JSLQ) 101

Author: Peter Aron Zentai April 30th, 2012

featured post, JSLQ, odata, SQLite, WebSQL

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
    });

//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 
                });
    });

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); }

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>

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,

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

1	http://services.odata.org/Northwind.svc/Products

webSQL

SELECT T0.* FROM [Products] T0

1	SELECT T0.* FROM [Products] T0

forEach()

source code

    <script>
        northwind.Products.forEach(function (product) {
            console.log(product.ProductName);
        });
    </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

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

1	http://services.odata.org/Northwind.svc/Products

webSQL

SELECT T0.* FROM [Products] T0

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

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,

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

1	http://services.odata.org/Northwind/Northwind.svc/Products$top=1

webSQL

SELECT T0.* FROM [Products] T0 LIMIT 1

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

<script>
    northwind.Products.single(
        function (product) { return product.ProductID == 42 }, {},
        function (product) {
            console.dir(product);
        });
</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,
...

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

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

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>

northwind.Products

.filter(function (product) { return product.ProductName == 'Chai'; })

.toArray(function (products) {

console.dir(products);

});

</script>

Result

0 : NorthwindModel.Product(1):Chai

1	0 : NorthwindModel.Product(1):Chai

translated query

OData

http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’)

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.ProductName like ?
Params: [‘Chai%’]

SELECT T0.* FROM [Products] T0

WHERE T0.ProductName like ?

Params: [‘Chai%’]

filter() – simple 1 – using lambda expressions

source code for modern browsers

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

northwind.Products

.filter(it => it.ProductName == 'Chai')

.toArray(function (products) {

console.dir(products);

});

</script>

Result

0 : NorthwindModel.Product(1):Chai

1	0 : NorthwindModel.Product(1):Chai

translated query

OData

http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=startswith(ProductName,’Chai’)

webSQL

filter() – simple 2

source code

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

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)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ?

Params: [7]

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

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,

1 2	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’)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=substringof(ProductName,’Tofu’)

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.ProductName like ?

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.ProductName like ?

Params: [‘%Tofu%’]

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

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,

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

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID eq 1) or

(ProductID eq 5) or (ProductID eq 7) …

1	(ProductID eq 5) or (ProductID eq 7) …

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.Product_ID in (?,?,?,?,?,?)

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.Product_ID in (?,?,?,?,?,?)

Params: [1,5,7,8,23,68]

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

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,

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=((ProductID eq 17) or

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID eq 17) or

(ProductName eq ‘Chai’))

1	(ProductName eq ‘Chai’))

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.Product_ID in (?,?,?,?,?,?)

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.Product_ID in (?,?,?,?,?,?)

Params: [1,5,7,8,23,68]

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

<script>
    northwind.Products
        .filter("it.ProductID == this.id || it.ProductName == this.name", { id: 17, name: 'Chai' })
        .forEach(function (product) { console.log(product); });
</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,

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=((ProductID eq 17) or

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=((ProductID eq 17) or

(ProductName eq ‘Chai’))

1	(ProductName eq ‘Chai’))

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.Product_ID in (?,?,?,?,?,?)

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.Product_ID in (?,?,?,?,?,?)

Params: [1,5,7,8,23,68]

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

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,

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

<script>
    northwind.Products
        .filter(function (product) {
            return product.Category.CategoryName == 'Beverages' && product.Discontinued == true;
        })
        .toArray(function (products) {
            console.dir(products);
        });
</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,

1 2	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))

1	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 = ?

SELECT T0.* FROM [Products] T0

LEFT OUTER JOIN [Categories] T1 ON T0.CategoryID = T1.CategoryID

WHERE T1.CategoryName = ? AND T0.Discontinued = ?

Params: ['Beverages',1]

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

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

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>

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,

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>

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,

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>

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,

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)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ?

Params: [7]

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

<script>
    northwind.Products
        .filter('.it.ProductID > minValue', {minValue: 7})
        .toArray(function (products) {
            console.dir(products);
        });
</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,

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)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ?

Params: [7]

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

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

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)

1	http://services.odata.org/Northwind/Northwind.svc/Products?$filter=(ProductID gt 7)

webSQL

SELECT T0.* FROM [Products] T0 
WHERE T0.ProductID > ?

1 2	SELECT T0.* FROM [Products] T0 WHERE T0.ProductID > ?

Params: [7]

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

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

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

<script>
    northwind.Products
        .map(function (product) {
            return { ID: product.ProductID, Name: product.ProductName }
        })
        .toArray(function (products) {
            console.dir(products[0]);
            console.dir(products);
        });
</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],
...

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>

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

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>

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

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>

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>

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

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>

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

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>

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

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>

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

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>

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

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

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

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

<script>
  context.GetPopularProducts().toArray(function(r) {
    // your code comes here
  });
</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

<script>
  context.GetPopularProducts(function(r) {
    // your code comes here
  });
</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

<script>
  context.GetPopularProducts().toArray().then(function(r) {
    //your code comes here
  });
</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

<script>
  context.GetPopularProducts().then(function(r) {
    //your code comes here
  });
</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

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

context.GetPopularProducts(stringParam1, intParam2).toArray(function(r) {

// your code comes here

});

</script>

Translated query

oData

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

context.GetPopularProducts(param1, param2).then(function(r) {

//your code comes here

});

</script>

Translated query

oData

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

context.GetPopularProducts({param1Name: param1, param2Name: param2}, function(r) {

//your code comes here

});

</script>

Translated query

oData

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

context.GetPopularProducts({param1Name: param1, param2Name: param2}).then(function(r) {

//your code comes here

});

</script>

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

<script>
  context.Products.GetPopularProducts(stringParam1, intParam2, function(r) {
    //your code comes here
  });
</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

<script>
  context.Products.GetPopularProducts(stringParam1, intParam2).then(function(r) {
    //your code comes here
  });
</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

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

<script>
  myProduct.SetPrice({paramName1: param1, paramName2: param2})
    .then(function(r) {
     //your code comes here
  });
</script>

myProduct.SetPrice({paramName1: param1, paramName2: param2})

.then(function(r) {

//your code comes here

});

</script>

Translated query

oData

http://host/YourService/Todos[82]/SetPrice

featured post, JSLQ, odata, SQLite, WebSQL

JayData.org