Skip to content

Querying The Offline Store

End users can run various OData system queries like $search and $filter on the local offline store after the initial download.

The Search System Query

If the property type of an entity is Edm.String, then the property is searchable. By default, all searchable properties of the entity are scanned for a given search term using a LIKE SQL query. If there are many searchable properties, the search may be time-consuming. To improve performance, selective search is supported, limiting searches to selected properties.

Offline OData supports the $search system query with the following capabilities and limitations:

  • Capabilities:

    • Searching simple properties of the type Edm.String.
    • Searching collection-typed properties.

  • Limitations:

    • Searching dynamic properties is only possible if the underlying entity type is open. However, selective search is not supported for dynamic properties, meaning all dynamic properties will be searched even if only one dynamic property is selected.
    • If no searchable property is selected or if the targeting entity type has no searchable properties, the $search query option will be ignored when generating the query result.

The Search OData API

The following OData Core APIs are designed for the $search query:

It's worth noting that Offline OData supports prefix search introduced in SAPOData. A search term ending with a star (*) indicates prefix matching.

The examples below illustrate how to apply the $search query on the local store.

// search from the Email property of the Customer entity type
EntitySet customerSet = dataService.getEntitySet("Customers");
EntityType customerType = customerSet.getEntityType();
Property emailProp = customerType.getProperty("Email");

// basic search term
SearchTerm searchTerm = SearchTerm.word("Jenni");
DataQuery searchQuery = new DataQuery().from(customerSet).select(emailProp).search(searchTerm);
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

// prefix search
searchTerm = SearchTerm.prefix("Jenni");
searchQuery = new DataQuery().from(customerSet).select(emailProp).search(searchTerm);
entities = dataService.executeQuery(searchQuery).getEntityList();

// search phrase
StringList stringList = new StringList();
stringList.add("Jennifer@sap.com");
searchQuery = new DataQuery().from(customerSet).select(emailProp).search(SearchTerm.phrase(stringList));
entities = dataService.executeQuery(searchQuery).getEntityList();

// logical search operator
searchQuery = new DataQuery().from(customerSet).search(SearchTerm.word("Jenni").or(SearchTerm.word("Victor")));
entities = dataService.executeQuery(searchQuery).getEntityList();

// search from the Email property of the Customer entity type
val customerSet = dataService.getEntitySet("Customers")
val customerType = customerSet.entityType
val emailProp = customerType.getProperty("Email")

// basic search term
var searchTerm = SearchTerm.word("Jenni")
var searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
var entities = dataService.executeQuery(searchQuery).getEntityList()

// prefix search
searchTerm = SearchTerm.prefix("Jenni")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
entities = dataService.executeQuery(searchQuery).getEntityList()

// search phrase
val stringList = StringList()
stringList.add("Jennifer@sap.com")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(SearchTerm.phrase(stringList))
entities = dataService.executeQuery(searchQuery).getEntityList()

// logical search operator
searchQuery = DataQuery().from(customerSet).search(SearchTerm.word("Jenni").or(SearchTerm.word("Victor")))
entities = dataService.executeQuery(searchQuery).getEntityList()

// search from the Email property of the Customer entity type
let customerSet: EntitySet = dataService.entitySet(withName:"Customers")
let customerType: EntityType = customerSet.entityType
let emailProp = customerType.property(withName:"Email")

// basic search term
var searchTerm : SearchTerm = SearchTerm.word("Jenni")
var searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
var entities = try dataService.executeQuery(searchQuery).entityList()

// prefix search
searchTerm = SearchTerm.prefix("Jenni")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
entities = try dataService.executeQuery(searchQuery).entityList()

// search phrase
let stringList : StringList = StringList()
stringList.append("Jennifer@sap.com")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(SearchTerm.phrase(stringList))
entities = try dataService.executeQuery(searchQuery).entityList()

// logical search operator
searchQuery =  DataQuery().from(customerSet).search(SearchTerm.word("Jenni").or(SearchTerm.word("Victor")));
entities = try dataService.executeQuery(searchQuery).entityList()

Note

$search system query with an empty search term (E.g. $search="" ) is ignored and will be removed while querying the local store. It is mandatory to provide a valid search term or search phrase to the $search system query option.

The Search Case Sensitivity

By default, the UltraLite database uses a case-sensitive collation, which makes the search functionality case-sensitive as well. You can modify the case sensitivity in the mobile services cockpit; however, this option only affects new databases. The collation of existing UltraLite databases cannot be changed. If the collation is set to case-insensitive in the mobile services cockpit, search conditions will also be case-insensitive.

For an existing case-sensitive database, you can perform a case-insensitive search for a single character. For example, the query $search=[Aa]bc will search for both Abc and abc from the local store.

The Underlying UltraLite Search SQL

The UltraLite LIKE SQL operator is used to search within searchable columns. For more information on the restrictions of the LIKE search, refer to UltraLite LIKE Search Condition.

Terms can be searched using a LIKE query with the string %term% in the selected searchable columns.

The following characters have special meaning in UltraLite LIKE query:

_ : The underscore (_) matches any one character. For example, "a_" matches "ab" and "a5", but not "a".

% : The percent (%) will match any string with zero or more characters. For example, "go%" matches "goes" and "goods".

[] : The [] （square brackets）matches any single character in the specified range or set. For example, "[BT]oy" matches "Boy" or "Toy". [%] matches only the percent character (the percent character does not act as a wildcard in this context), and [_] matches only the underscore character.

Search Performance Considerations

Performance of a search query is linear to the number of rows in the table multiplied by the number of columns being searched. Therefore, performance may degrade with larger datasets. To boost the performance, it is necessary to index the columns being searched and utilize prefix search to leverage the index efficiently. For example, if there are 1 million rows in the Customers entity set, and the property email of type Edm.String is indexed, using a prefix search query like $select=Email&$search=Emily* will benefit significantly from the index. If the index is not present, and the prefix search is not applicable, it is recommended to use the search query together with other filters to limit the rows to search.

Debugging Search With ILOData

You can use the $search system query within the ILOData command line utility. Here are a few ILOData query examples:

  • Basic search query: get Customers?$search=abc
  • Search query with logical operator AND, OR, NOT: get Customers?$search=abc AND (NOT def) OR xyz
  • Search query with parenthesis: get Customers?$search=abc AND (def OR xyz)
  • Search query with NOT operator: get Customers?$search=NOT def

Note

AND, NOT, OR are uppercase logical operators for the $search system query.

Offline OData Query Functions

The Offline OData supports the below functions for the standard OData $filter system query options.

  • cast
  • ceiling
  • concat
  • day
  • date
  • endswith
  • floor
  • fractionalseconds
  • hour
  • indexof
  • isof
  • length
  • minute
  • month
  • now
  • round
  • second
  • startswith
  • substring
  • substringof
  • contains
  • time
  • tolower
  • totaloffsetminutes
  • totalseconds
  • toupper
  • trim
  • year

Besides the above standard query functions, Offline OData supports the following query functions specially designed for the Offline OData use case.

  • sap.entityexists
  • sap.islocal
  • sap.hasPendingChanges
  • sap.inerrorstate
  • sap.upsertedlastdownload

The sap.entityexists Function

The sap.entityexists is the Offline OData specific function to check if a related entity exists.

The following example illustrates how to use the sap.entityexists query function on the local store.

EntitySet customerSet = dataService.getEntitySet("Customers");
EntityType customerType = customerSet.getEntityType();
Property ordersNavigationProp = customerType.getProperty("Orders");
DataPath dataPathToOrders = DataPath.of(ordersNavigationProp)

// GET Customers?$filter=sap.entityexists(Orders)
DataQuery searchQuery = new DataQuery().from(customerSet).filter(OfflineODataQueryFunction.entityExists(dataPathToOrders));
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

val customerSet = dataService.getEntitySet("Customers")
val customerType = customerSet.entityType
val ordersNavigationProp = customerType.getProperty("Orders")
var dataPathToOrders = DataPath.of(ordersNavigationProp)

// GET Customers?$filter=sap.entityexists(Orders)
var searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.entityExists(dataPathToOrders))
var entities = dataService.executeQuery(searchQuery).getEntityList()

let customerSet: EntitySet = dataService.entitySet(withName:"Customers")
let customerType: EntityType = customerSet.entityType
let ordersNavigationProp = customerType.property(withName:"Orders")
let dataPathToOrders = DataPath.of(ordersNavigationProp)

// GET Customers?$filter=sap.entityexists(Orders)
let searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.entityExists(dataPathToOrders))
let entities = try dataService.executeQuery(searchQuery).entityList()

Note

ILOData command line utility supports the function sap.entityexists. Below is an example ILOData query: GET Customers?$filter=sap.entityexists(Orders)

The sap.islocal Function

The sap.islocal is a Offline OData specific function to check if an entity is local. An entity is local if there are changes that have been made to the entity that have not yet been downloaded from the back end, regardless of whether or not those operations have been sent to the back end.

For more information, refer to Local Information

Note

The sap.islocal function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=not sap.islocal()

The sap.hasPendingChanges Function

The sap.hasPendingChanges is the Offline OData specific function to check if an entity has any pending change requests in the request queue. The sap.hasPendingChanges is different from sap.islocal based on whether or not the change requests have been sent to the back end. An entity that has changes in the request queue that have not been sent will be both local and pending. When the changes in the request queue have been sent to the back end but the results have not been received by a download operation, the entity will be local but not pending.

For more information, refer to Pending Information.

Note

The sap.hasPendingChanges function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=sap.hasPendingChanges()

The sap.inerrorstate Function

The sap.inerrorstate is the Offline OData specific function to check if an entity is in the error state.

For more information, refer to Finding Entities in Error State.

Note

The sap.inerrorstate function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=sap.inerrorstate()

The sap.upsertedlastdownload Function

The sap.upsertedlastdownload is the Offline OData specific function to check if an entity was inserted or updated in the last download.

The following example illustrates how to use the sap.upsertedlastdownload query function on the local store.

EntitySet customerSet = dataService.getEntitySet("Customers");

// GET Customers?$filter=sap.upsertedlastdownload()
DataQuery searchQuery = new DataQuery().from(customerSet).filter(OfflineODataQueryFunction.upsertedLastDownload());
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

val customerSet = dataService.getEntitySet("Customers")

// GET Customers?$filter=sap.upsertedlastdownload()
var searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.upsertedLastDownload())
var entities = dataService.executeQuery(searchQuery).getEntityList()

let customerSet: EntitySet = dataService.entitySet(withName:"Customers")

// GET Customers?$filter=sap.upsertedlastdownload()
let searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.upsertedLastDownload())
let entities = try dataService.executeQuery(searchQuery).entityList()

Note

The sap.upsertedlastdownload function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=sap.upsertedlastdownload()

Data Aggregation

Offline OData supports basic data aggregation functionalities as specified in the OData Data Aggregation Extension Version 4.0. The supported functionalities include sum, min, max, average, count, countdistinct aggregation, and groupby, top, bottom, concat, and filter transformations. Although the specification is defined for OData Version 4.0, the aggregation capability is extended to local offline OData stores populated for OData Version 2.0.

Aggregation Methods

The following aggregation methods are supported:

  • sum: Calculates the sum of a numeric property in the entity set.
  • min: Finds the minimum value of a numeric property in the entity set.
  • max: Finds the maximum value of a numeric property in the entity set.
  • average: Calculates the average value of a numeric property in the entity set.
  • count: Counts the number of items.
  • countdistinct: Counts the number of distinct items.

Grouping and Transformations

  • groupby: Groups the items by specified properties.
  • topcount: Limits the number of items to the specified number in descending order.
  • bottomcount: Limits the number of items to the specified number in ascending order.
  • topsum: Retrieves the top N items which sum up to a specified value.
  • bottomsum: Retrieves the bottom N items which sum up to a specified value.
  • toppercent: Retrieves the top N percent of items.
  • bottompercent: Retrieves the bottom N percent of items.
  • concat: Concatenates the results of multiple aggregations.
  • filter: Filters the items based on specified criteria.
  • orderby: Orders the items based on specified criteria.
  • top: Retrieves the top N items.
  • skip: Skips the specified number of items.

Example Usage

The following example illustrates how to use the data aggregation in Offline OData.

EntitySet customerSet = dataService.getEntitySet("Customers");
Property nameProp = customerSet.getEntityType().getProperty("Name");
Property cityProp = customerSet.getEntityType().getProperty("City");

EntitySet salesSet = dataService.getEntitySet("Sales");
Property amountProp = salesSet.getEntityType().getProperty("Amount");

// Get Customers?$apply=groupby((Name))
DataQuery aggregateQuery = new DataQuery().from(customerSet).groupBy(nameProp);
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

// Get Customers?$apply=groupby((City),aggregate($count as CustomersPerCity))
GroupTransform groupTransform = GroupTransform.groupBy(cityProp).aggregate(AggregateValue.count(as: "Total"));
aggregateQuery = new DataQuery().from(customerSet).transform(groupTransform);
entities = dataService.executeQuery(aggregateQuery).getEntityList();

// Get Sales?$apply=topcount(2,Amount)
TopTransform topCountTransform = TopTransform.count(2, amountProp);
aggregateQuery = new DataQuery().from(salesSet).transform(topCountTransform);
entities = dataService.executeQuery(aggregateQuery).getEntityList();

// Get Sales?$apply=aggregate(Amount with sum as Total, Amount with min as Min, Amount with max as Max)
AggregateExpressionList aggregateExprList = new AggregateExpressionList();
aggregateExprList.add(AggregateExpression.create(amountProp, AggregateMethod.sum, "Total"));
aggregateExprList.add(AggregateExpression.create(amountProp, AggregateMethod.minimum, "Min"));
aggregateExprList.add(AggregateExpression.create(amountProp, AggregateMethod.countDistinct, "Max"));
aggregateQuery = new DataQuery().from(salesSet).aggregate(AggregateValue.createWithExpressionList(
            aggregateExprList));
entities = dataService.executeQuery(aggregateQuery).getEntityList();

// refer to the API documentation for detailed usage of aggregation API

val customerSet = dataService.getEntitySet("Customers")
val nameProp = customerSet.entityType.getProperty("Name")
val cityProp = customerSet.entityType.getProperty("City")

val salesSet = dataService.getEntitySet("Sales")
val amountProp = salesSet.entityType.getProperty("Amount")

// Get Customers?$apply=groupby((Name))
var aggregateQuery = DataQuery().from(customerSet).groupBy(nameProp)
var entities = dataService.executeQuery(aggregateQuery).getEntityList()

// Get Customers?$apply=groupby((City),aggregate($count as CustomersPerCity))
val groupTransform = GroupTransform.groupBy(cityProp).aggregate(AggregateValue.count("Total"))
aggregateQuery = DataQuery().from(customerSet).transform(groupTransform)
entities = dataService.executeQuery(aggregateQuery).getEntityList()

// Get Sales?$apply=topcount(2,Amount)
val topTransform = TopTransform.count(2, amountProp)
aggregateQuery = DataQuery().from(salesSet).transform(topTransform)
entities = dataService.executeQuery(aggregateQuery).getEntityList()

// Get Sales?$apply=aggregate(Amount with sum as Total, Amount with min as Min, Amount with max as Max)
val aggregateExprList = AggregateExpressionList()
aggregateExprList.add(AggregateExpression.create(amountProp, AggregateMethod.sum, "Total"))
aggregateExprList.add(AggregateExpression.create(amountProp, AggregateMethod.minimum, "Min"))
aggregateExprList.add(AggregateExpression.create(amountProp, AggregateMethod.countDistinct, "Max"))
aggregateQuery = DataQuery().from(salesSet).aggregate(AggregateValue.createWithExpressionList(aggregateExprList))
entities = dataService.executeQuery(aggregateQuery).getEntityList()

// refer to the API documentation for detailed usage of aggregation API

let customerSet = dataService.entitySet(withName: "Customers")
let nameProp = customerSet.entityType.property(withName: "Name")
let cityProp = customerSet.entityType.property(withName: "City")

let salesSet = dataService.entitySet(withName: "Sales")
let amountProp = salesSet.entityType.property(withName: "Amount")

// Get Customers?$apply=groupby((Name))
var aggregateQuery = DataQuery().from(customerSet).groupBy(nameProp)
var entities = try dataService.executeQuery(aggregateQuery).entityList()

// Get Customers?$apply=groupby((City),aggregate($count as CustomersPerCity))
let groupTransform = GroupTransform.groupBy(cityProp).aggregate(AggregateValue.count(as: "Total"))
aggregateQuery = DataQuery().from(customerSet).transform(groupTransform)
entities = try dataService.executeQuery(aggregateQuery).entityList()

// Get Sales?$apply=topcount(2,Amount)
let topTransform = TopTransform.count(amount: 2, value: amountProp)
aggregateQuery = DataQuery().from(salesSet).transform(topTransform)
entities = try dataService.executeQuery(aggregateQuery).entityList()

// Get Sales?$apply=aggregate(Amount with sum as Total, Amount with min as Min, Amount with max as Max)
let aggregateExprList = AggregateExpressionList()
aggregateExprList.add(AggregateExpression.create(amountProp, method: AggregateMethod.sum, alias: "Total"))
aggregateExprList.add(AggregateExpression.create(amountProp, method: AggregateMethod.minimum, alias: "Min"))
aggregateExprList.add(AggregateExpression.create(amountProp, method: AggregatteMethod.maximum, alias: "Max"))
aggregateQuery = DataQuery().from(salesSet).aggregate(AggregateValue.createWithExpressionList(aggregateExpressionList: aggregateExprList))
entities = try dataService.executeQuery(aggregateQuery).entityList()

// refer to the API documentation for detailed usage of aggregation API

Note

  • Ensure that the properties used in the aggregation functions are of appropriate types.
  • The performance of aggregation queries may vary based on the size of the dataset and the complexity of the query.
  • For optimal performance, consider indexing the properties used in the groupby transformation.
  • The aggregation functionality is supported by the ILOData command line utility.

Cross Join and Aggregation

Offline OData supports cross-join queries and chained aggregation on the cross-join results. A cross-join query results in a collection of complex type values, as per OData specifications. The following example illustrates how to use cross-join and aggregation on Offline OData.

EntitySet productSet = dataService.getEntitySet("Products");
EntityType productType = productSet.getEntityType();
Property productIDProp = productType.getProperty("ID");
Property productNameProp = productType.getProperty("Name");

EntitySet salesSet = dataService.getEntitySet("Sales");
EntityType salesType = salesSet.getEntityType();
Property salesIDProp = salesType.getProperty("ID");
Property salesProductIDProp = salesType.getProperty("ProductID");
Property salesAmountProp = salesType.getProperty("Amount");

// Cross join query example
// Get $crossjoin(Products,Sales)?$expand=Products($select=Name),Sales($select=Amount)&$filter=Products/ID eq Sales/ProductID&$orderby=Sales/ID
DataQuery dataQuery = new DataQuery().join(productSet, salesSet)
        .filter(productSet.path(productIDProp).equal(salesSet.path(salesProductIDProp)))
        .expand(productSet.select(productNameProp))
        .expand(salesSet.select(salesAmountProp))
        .orderBy(salesSet.path(salesIDProp));
ComplexValueList complexValueList = dataService.executeQuery(dataQuery).getComplexList();
for (ComplexValue complexValue : complexValueList) {
    DataValue product = complexValue.getDynamic("Products");
    DataValue sales = complexValue.getDynamic("Sales");
    Log.d(TAG, "product: " + product.toString());
    Log.d(TAG, "sales: " + sales.toString());
}

// cross join with grouping and aggregation
// Get $crossjoin(Products,Sales)?$expand=Products($select=Name),Sales($select=Amount)&$filter=Products/ID eq Sales/ProductID&$apply=groupby((Products/Name),aggregate(Sales/Amount with average as AverageAmount))/orderby(Products/Name asc)
GroupTransform groupTransform = GroupTransform.groupBy(productSet.path(productNameProp)).aggregate(AggregateValue.create(salesSet.path(salesAmountProp), AggregateMethod.average, "AverageAmount"));
dataQuery = new DataQuery().join(productSet, salesSet)
        .filter(productSet.path(productIDProp).equal(salesSet.path(salesProductIDProp)))
        .expand(productSet.select(productNameProp))
        .expand(salesSet.select(salesAmountProp))
        .transform(groupTransform)
        .transform(OrderByTransform.orderBy(SortItem.of(productSet.path(productNameProp))));
complexValueList = dataService.executeQuery(dataQuery).getComplexList();
for (ComplexValue complexValue : complexValueList) {
    Log.d(TAG, complexValue.toString());
}

val productSet: EntitySet = dataService.getEntitySet("Products")
val productType: EntityType = productSet.entityType
val productIDProp: Property = productType.getProperty("ID")
val productNameProp: Property = productType.getProperty("Name")

val salesSet: EntitySet = dataService.getEntitySet("Sales")
val salesType: EntityType = salesSet.entityType
val salesIDProp: Property = salesType.getProperty("ID")
val salesProductIDProp: Property = salesType.getProperty("ProductID")
val salesAmountProp: Property = salesType.getProperty("Amount")

// Cross join query example
// Get $crossjoin(Products,Sales)?$expand=Products($select=Name),Sales($select=Amount)&$filter=Products/ID eq Sales/ProductID&$orderby=Sales/ID
var dataQuery: DataQuery = DataQuery().join(productSet, salesSet)
        .filter(productSet.path(productIDProp).equal(salesSet.path(salesProductIDProp)))
        .expand(productSet.select(productNameProp))
        .expand(salesSet.select(salesAmountProp))
        .orderBy(salesSet.path(salesIDProp))
var complexValueList: ComplexValueList = dataService.executeQuery(dataQuery).getComplexList()
complexValueList.forEach { complexValue ->
    val product = complexValue.getDynamic("Products")
    val sales = complexValue.getDynamic("Sales")
    Log.d(TAG, "product: " + product.toString())
    Log.d(TAG, "sales: " + sales.toString())
}

// cross join with grouping and aggregation
// Get $crossjoin(Products,Sales)?$expand=Products($select=Name),Sales($select=Amount)&$filter=Products/ID eq Sales/ProductID&$apply=groupby((Products/Name),aggregate(Sales/Amount with average as AverageAmount))/orderby(Products/Name asc)
var groupTransform = GroupTransform.groupBy(productSet.path(productNameProp))
        .aggregate(
            AggregateValue.create(
                salesSet.path(salesAmountProp),
                AggregateMethod.average,
                "AverageAmount"
            )
        )
dataQuery = DataQuery().join(productSet, salesSet)
        .filter(productSet.path(productIDProp).equal(salesSet.path(salesProductIDProp)))
        .expand(productSet.select(productNameProp))
        .expand(salesSet.select(salesAmountProp))
        .transform(groupTransform)
        .transform(
            OrderByTransform.orderBy(
                    SortItem.of(
                        productSet.path(productNameProp)
                    )
            )
        )
complexValueList = dataService.executeQuery(dataQuery).getComplexList()
complexValueList.forEach { complexValue ->
    Log.d(TAG, complexValue.toString())
}

let productSet: EntitySet = dataService.entitySet(withName: "Products")
let productType = productSet.entityType
let productNameProp = productType.property(withName: "Name")
let productIDProp = productType.property(withName: "ID")

let salesSet: EntitySet = dataService.entitySet(withName: "Sales")
let salesType = salesSet.entityType
let salesIDProp = salesType.property(withName: "ID")
let salesProductIDProp = salesType.property(withName: "ProductID")
let salesAmountProp = salesType.property(withName: "Amount")

// Cross join query example
// Get $crossjoin(Products,Sales)?$expand=Products($select=Name),Sales($select=Amount)&$filter=Products/ID eq Sales/ProductID&$orderby=Sales/ID
var dataQuery = DataQuery().join(productSet, salesSet)
                .filter(productSet.path(productIDProp).equal(salesSet.path(salesProductIDProp)))
                .expand(productSet.select(productNameProp))
                .expand(salesSet.select(salesAmountProp))
                .orderBy(salesSet.path(salesIDProp))
var complexValueList : ComplexValueList = try dataService.executeQuery(dataQuery).complexList()
for complexValue in complexValueList {
    let product = complexValue.dynamicValue(name: "Products") as! EntityValue
    let supplier = complexValue.dynamicValue(name:"Suppliers") as! EntityValue
    print("product \(product)")
    print("supplier \(supplier)")
}

// cross join with grouping and aggregation
// Get $crossjoin(Products,Sales)?$expand=Products($select=Name),Sales($select=Amount)&$filter=Products/ID eq Sales/ProductID&$apply=groupby((Products/Name),aggregate(Sales/Amount with average as AverageAmount))/orderby(Products/Name asc)
let groupTransform = GroupTransform.groupBy(productSet.path(productNameProp)).aggregate(AggregateValue.create(salesSet.path(salesAmountProp), method: AggregateMethod.average, alias: "AverageAmount"))
dataQuery = DataQuery().join(productSet, salesSet)
                .filter(productSet.path(productIDProp).equal(salesSet.path(salesProductIDProp)))
                .expand(productSet.select(productNameProp))
                .expand(salesSet.select(salesAmountProp))
                .transform(groupTransform)
                .transform(OrderByTransform.orderBy(SortItem.of(productSet.path(productNameProp))))
complexValueList = try dataService.executeQuery(dataQuery).complexList()
for complexValue in complexValueList {
    print("complexValue \(complexValue)")
}

Note

  • Cross-join queries on arbitrary entity sets may yield large data sets. To reduce the size of the dataset, ensure proper filters are used for the cross-join query.
  • For optimal performance, consider indexing the properties used in the cross-join filters.
  • ILOData command line utility supports cross-join functionality.
Last update: February 17, 2025