oData $select and $expand

[jr01]

Hey @ghiscoding,

As briefly mentioned here #578 (comment)

Imagine we have order-lines and products as entities. An order-line has an id, quantity, price and a reference/navigation to a product entity. A product has a name and a bunch of other properties. Suppose we are only interested in displaying the order-line id and the product name and we don't want to retrieve all the other properties from the backend.

Currently slickgrid-universal has no option to 1) be able to $select just the properties I want from the backend and 2) be able to select properties from a navigation/related entity using $expand.

To work around that I have defined the grid/columns as:

const columns = [
    { id: 'id', name: 'Order Line Id'  },
    { id: 'product/name', name: 'Product Name' },
];

const mapper = new SlickGridODataHelper(this._http,  columns);

const gridOption: GridOption = {
  backendServiceApi: {
     service: new GridODataService(),
     process: (query: string) => mapper.getODataApiCall(query),
     postProcess: (response: oDataResponse) => {
        this._dataset = mapper.processODataApiResponse(response, gridOption);
         ...
  },

The getODataApiCall extends the query from slickgrid-univeral to api/orderlines?$select=id&$expand=product($select=name)

That results in this response from the server: {"@odata.context":"api/orderlines/$metadata#OrderLines(id,product(name)","value":[{""id":1,"product":{"name":"Cake"} ....

and the processODataApiResponse flattens that to [ { id: 1, 'product/name': 'Cake' }] which slickgrid understands.

import { HttpClient, HttpParams } from '@angular/common/http';
import { Observable } from 'rxjs';
import { GridConfig } from './slickgrid.config';
import { Column, GridOption } from '@slickgrid-universal/common';

export type GridDataSet = Record<string, never>[];

export interface oDataResponse {
  '@odata.count': number;
  value: GridDataSet;
}

export class SlickGridODataHelper {
  private _oDataDefinition!: {
    navigations: { [navigation: string]: string[] };
    navigationFields: { [field: string]: string[] };
    selectFields: string[];
  };

  constructor(
    private _http: HttpClient,
    columnDefinitions: Column[]) {
    this.buildDefinitions(columnDefinitions);
  }

  public getODataApiCall(odataQuery: string): Observable<unknown> {

    const addQueryPart = (name: string, part: string): void => {
      if (part.length > 0) {
        if (odataQuery.length > 0) {
          odataQuery += '&';
        }

        odataQuery += (name.length > 0 ? name + '=' : '') + part;
      }
    };

    const selectExpand = this.buildSelectExpand(this._oDataDefinition.selectFields, '&');
    addQueryPart('', selectExpand);

    let params = new HttpParams({ fromString: odataQuery });
    return this._http.get(gridConfig.uri, { params });
  }

  public processODataApiResponse(
    data: oDataResponse,
    gridOption: GridOption
  ): GridDataSet {
    if (gridOption.pagination) {
      gridOption.pagination.totalItems = data['@odata.count'];
    }

    const dataset = data['value'];
    this.flattenODataDataset(dataset);
    return dataset;
  }

  private buildSelectExpand(fields: string[], separator: '&' | ';'): string {
    const navigations: { [collection: string]: string[] } = { };
    const selectFields = [];

    for (const field of fields) {
      const splits = field.split('/');
      if (splits.length === 1) {
        selectFields.push(field);
      } else {
        const collection = splits[0];
        const properties = splits.splice(1).join('/');

        if (!navigations[collection]) {
          navigations[collection] = [];
        }

        navigations[collection].push(properties);
      }
    }

    let select = (selectFields.length > 0 ? '$select=' : '') + selectFields.join(',');

    let expand = '';
    for (const collection of Object.keys(navigations)) {
      expand = expand.length === 0 ? '$expand=' : expand += ',';

      expand += collection + '(';

      const subSelectExpand = this.buildSelectExpand(navigations[collection], ';');
      expand += subSelectExpand;

      expand += ')';
    }

    const selectExpand = select += (expand.length > 0 ? separator : '') + expand;
    return selectExpand;
  }

  private buildDefinitions(columnDefinitions: Column[]): void {
    this._oDataDefinition = {
      navigations: {},
      navigationFields: {},
      selectFields: ['id'],
    };
    for (const columnDef of columnDefinitions) {
      const fields = columnDef.fields ? columnDef.fields : [columnDef.field];
      for (const field of fields) {
        if (!this._oDataDefinition.selectFields.includes(field)) {
          this._oDataDefinition.selectFields.push(field);
        }

        const names = field.split('/');
        if (names.length > 1) {
          if (!this._oDataDefinition.navigationFields[field]) {
            const navigation = names[0];

            if (!this._oDataDefinition.navigations[navigation]) {
              this._oDataDefinition.navigations[navigation] = [];
            }

            const navigationTarget = names.slice(1).join('/');
            this._oDataDefinition.navigations[navigation].push(navigationTarget);

            this._oDataDefinition.navigationFields[field] = names;
          }
        }
      }
    }
  }

  // flattens the oData dataset. E.g. item.account: { code: 'a' } is flattened to item['account/code'].
  private flattenODataDataset(dataset: GridDataSet): void {
    for (const item of dataset) {
      for (const navigationField of Object.keys(this._oDataDefinition.navigationFields)) {
        const names = this._oDataDefinition.navigationFields[navigationField];
        let val = item[names[0]];

        for (let i = 1; i < names.length; i++) {
          const mappedName = names[i];
          if (val && typeof val === 'object' && mappedName in val) {
            val = val[mappedName];
          }
        }

        item[navigationField] = val;
      }

      for (const navigation of Object.keys(this._oDataDefinition.navigations)) {
        delete item[navigation];
      }
    }
  }
}

The choice for / as a separator has benefit that filtering ($filter) and sorting ($sort) work out of the box. IIRC, this was a year ago, I first tried with . but ran into issues.

I haven't thought about how to integrate this into slickgrid-universal.

What do you think? If a good idea, how can this be best done?

[ghiscoding]

Hmm I'm not sure I understand everything, I remember that in GraphQL I needed to query with extra arguments (like a productId) and for that I added this GraphQL Extra Arguments - Wiki. But I'm not sure if that is what you're aiming at?

As for the ., it used in a few areas of the code (Filter/Sort Services and other areas) and might be why it gives you a bit of problems, however the dot is the only character that is used so passing a / or any other character for splitting could be used. The only thing that I remember is the column id is also used on each Filters/Editors to make them unique (for example a text filter on firstName will add a css class of filter-firstName to that filter and having a . inside a css class is problematic so I added a console warning when user has columns with a dot . in a column id), so I don't think a / would cause any problem in css.

If you want to add some new features to the OData Service then go ahead, you're one of the big user, on my side it's mostly plain grids (with simple JSON array) and/or GraphQL and I haven't used OData in a while. However I like to have similar features in both Services (OData/GraphQL) but I understand that there are distinction between the 2 technologies, the GraphQL Extra Arguments - Wiki was not added to the OData Service so this might be a nice addition too.

As you know, I'm always very open for Contributions, as long as I can keep my 100% unit test coverage, I'm always happy to add more features 😉

[jr01]

I started working on ^^^ and ran into a small annoying bug.

When I select odata version 4 in example 9, then the response gets data.count instead of the expected @data.count.

In template09.html:

 <input type="radio" name="inlineRadioOptions" data-test="version4" id="radio4" value.bind="odataVersion"
                 onclick.delegate="setOdataVersion(4)"> 4

When selecting 4 in the UI, first the onclick.delegate gets executed with a numeric 4, but then directly after that the value.bind binding gets executed and sets the input's value (string '4') to the odataVersion member.

The solution is simple, just remove the value.bind here. If you'd like I can apply that in all templates where I find it?

[ghiscoding]

hmm I'm not surprised of this bug because I wanted to have the minimal working demo and I came up with my own Binding Service and my own template parser (the later being very rudimentary), of course those are very minimal and aren't smart to make decision like this (e.g. it won't automatically convert to number, it's not smart enough). Note however that the Binding Service and Binding Helper are now used by a few components like the Custom Footer and Pagination, so if there's bug in the Service then we should fix it but on the other end the template parser is only the demo.

So yeah after looking at the code, I guess it makes sense to remove the value.bind on that input, if there's more input checkbox with the same issue then it also makes sense to fix them as well but I assume this would only exist in the Examples. Funny enough if I look back at Angular-Slickgrid and Aurelia-Slickgrid, I made the same mistake of adding a binding and a checkbox event, so it's probably a good idea to fix them as well and just keep the event (not the binding)

[ghiscoding]

I just pushed a PR #583 to make the Binding Service a bit smarter, it will now parse values to numbers when detecting an input type number (other types should be ok as they are). That doesn't fix what you mentioned but it might help a bit and it's nice to know about the changes. At least I won't have to do parse manually anymore.

[jr01]

@ghiscoding - I did an initial commit here: https://github.com/jr01/slickgrid-universal/tree/feature/odata-select-expand - would be great to get some early feedback, but that can also wait until I submit a PR (with tests...)

[ghiscoding]

I left few small comments. Can you maybe provide a print screen or animated gif of that? For example I don't quite get how that helps with filtering & sorting like you said

[jr01]