UI5 OData bind multiple entities to sap.m.Table

Question

I am testing a UI5 application using the Northwind Database. I am trying to bring two entities together (Products and Suppliers). I want to show the supplier for each product in a table.

I have managed to get it to work using parameters:{expand : 'Supplier'} in the items parameter of the sap.m.Table, however, this brings back all data for every field.

So it brings back something similar to the following (where the first two columns are pulled from /Products and the last column is pulled from /Supplier:

ProductName    UnitPrice    SupplierName
Bread          10           Big Bread Co

At the moment my path is set to:

items="{
    path: '/Products',
    sorter: {
        path: 'ProductName',
        descending: false
    }
}"

and I have tried to access the Supplier Company Name using:

<Text text="{Suppliers/CompanyName}"/>

However, I can see why this wouldn't work but I cannot figure out how to get it to work.

Thanks for your help.

Please see what I have tried below:

<Table 
    id="table" 
    width="auto" 
    class="sapUiResponsiveMargin" 
    items="{
        path: '/Products',
        sorter: {
            path: 'ProductName',
            descending: false
        }
    }" 
    noDataText="{worklistView>/tableNoDataText}"
    busyIndicatorDelay="{worklistView>/tableBusyDelay}"
    growing="true"
    growingScrollToLoad="true"
    updateFinished="onUpdateFinished">

    <!--    parameters:{expand : 'Category'}, -->
    <headerToolbar>
        <Toolbar id="toolbar">
            <Title id="tableHeader" text="{worklistView>/worklistTableTitle}" />
            <ToolbarSpacer />
            <SearchField id="searchField" tooltip="{i18n>worklistSearchTooltip}" search="onSearch" width="auto" />
        </Toolbar>
    </headerToolbar>
    <columns>
        <Column id="nameColumn">
            <Text text="{i18n>tableNameColumnTitle}" id="nameColumnTitle" />
        </Column>
        <Column id="unitNumberColumn" hAlign="End">
            <Text text="{i18n>tableUnitNumberColumnTitle}" id="unitNumberColumnTitle" />
        </Column>
        <Column>
            <Text text="SupplierName" id="SupplierName" />
        </Column>
    </columns>
    <items>
        <ColumnListItem type="Navigation" press="onPress">
            <cells>
                <ObjectIdentifier title="{ProductName}" />
                <ObjectNumber number="{ path: 'UnitPrice', formatter: '.formatter.numberUnit' }" />
                <Text text="{Suppliers/CompanyName}" />
            </cells>
        </ColumnListItem>
    </items>
</Table>

Product:

<EntityType Name="Product">
    <Key>
        <PropertyRef Name="ProductID" />
    </Key>
    <Property xmlns:p6="http://schemas.microsoft.com/ado/2009/02/edm/annotation" Name="ProductID" Type="Edm.Int32" Nullable="false" p6:StoreGeneratedPattern="Identity" />
    <Property Name="ProductName" Type="Edm.String" Nullable="false" MaxLength="40" FixedLength="false" Unicode="true" />
    <Property Name="SupplierID" Type="Edm.Int32" />
    <Property Name="CategoryID" Type="Edm.Int32" />
    <Property Name="QuantityPerUnit" Type="Edm.String" MaxLength="20" FixedLength="false" Unicode="true" />
    <Property Name="UnitPrice" Type="Edm.Decimal" Precision="19" Scale="4" />
    <Property Name="UnitsInStock" Type="Edm.Int16" />
    <Property Name="UnitsOnOrder" Type="Edm.Int16" />
    <Property Name="ReorderLevel" Type="Edm.Int16" />
    <Property Name="Discontinued" Type="Edm.Boolean" Nullable="false" />
    <NavigationProperty Name="Category" Relationship="NorthwindModel.FK_Products_Categories" ToRole="Categories" FromRole="Products" />
    <NavigationProperty Name="Order_Details" Relationship="NorthwindModel.FK_Order_Details_Products" ToRole="Order_Details" FromRole="Products" />
    <NavigationProperty Name="Supplier" Relationship="NorthwindModel.FK_Products_Suppliers" ToRole="Suppliers" FromRole="Products" />
</EntityType>

Supplier:

<EntityType Name="Supplier">
    <Key>
        <PropertyRef Name="SupplierID" />
    </Key>
    <Property xmlns:p6="http://schemas.microsoft.com/ado/2009/02/edm/annotation" Name="SupplierID" Type="Edm.Int32" Nullable="false" p6:StoreGeneratedPattern="Identity" />
    <Property Name="CompanyName" Type="Edm.String" Nullable="false" MaxLength="40" FixedLength="false" Unicode="true" />
    <Property Name="ContactName" Type="Edm.String" MaxLength="30" FixedLength="false" Unicode="true" />
    <Property Name="ContactTitle" Type="Edm.String" MaxLength="30" FixedLength="false" Unicode="true" />
    <Property Name="Address" Type="Edm.String" MaxLength="60" FixedLength="false" Unicode="true" />
    <Property Name="City" Type="Edm.String" MaxLength="15" FixedLength="false" Unicode="true" />
    <Property Name="Region" Type="Edm.String" MaxLength="15" FixedLength="false" Unicode="true" />
    <Property Name="PostalCode" Type="Edm.String" MaxLength="10" FixedLength="false" Unicode="true" />
    <Property Name="Country" Type="Edm.String" MaxLength="15" FixedLength="false" Unicode="true" />
    <Property Name="Phone" Type="Edm.String" MaxLength="24" FixedLength="false" Unicode="true" />
    <Property Name="Fax" Type="Edm.String" MaxLength="24" FixedLength="false" Unicode="true" />
    <Property Name="HomePage" Type="Edm.String" MaxLength="Max" FixedLength="false" Unicode="true" />
    <NavigationProperty Name="Products" Relationship="NorthwindModel.FK_Products_Suppliers" ToRole="Products" FromRole="Suppliers" />
</EntityType>

Marc Marc · Accepted Answer · 2016-01-21T14:16:02

First of all, there is nothing wrong with expanding the complete Supplier entity. It shouldn't make a huge difference on the size/loading time of the response.

In fact, if you open the Chrome Dev Tools and track the time of your current request and my solution below, both times will be very similar (it's even faster without a select)

It surely makes a difference when you compare the size of the response.

Without the $select, you get Content-Length:2547
With $select, you get Content-Length:836 (so only a third)

Notice that the unit is Bytes! So both requests are still very tiny.

But to answer your question: In theory, the $select operator in combination with a nested URL should help you.

However, keep in mind that nested URLs where added in OData v4.

This is the URL that gives you your desired result

http://services.odata.org/V4/Northwind/Northwind.svc/Products?$select=ProductName,UnitPrice&$expand=Supplier($select=CompanyName)

You can see that I applied a $select on the main entity (Products). If you wanted all properties from the Products, but only the CompanyName of the Suppliers, simply change the parent-level $select to $select=*

http://services.odata.org/V4/Northwind/Northwind.svc/Products?$select=*&$expand=Supplier($select=CompanyName)

UI5 OData bind multiple entities to sap.m.Table

2 Answers