Equivalent of OData filter function substringof

BIZ TMS XData
1

Hi,

In an OData filter there is a function called substringof. Is it possible for me to add this as a custom function in an XData server ind if so, what is the mechanism?

Thanks,

Ken

2

Further to this some background. In web core I am using the DevExtreme DataGrid OData store. XData supports the contains function for filtering whereas DevExtreme submits a substringof because OData version 2 does not support contains.

I have found a work around for this by changing a couple of XData units for my XData server.

I have changed XData.Payload.Json.Writer.pas as follows because without this change the DevExtreme grid cannot get the total count.

procedure TJsonEntityCollectionWriter.Start(ABaseClass: TClass);
begin
  Assert(Model is TXDataAureliusModel);
  if FStarted then Exit;
  FStarted := true;
  FBaseClass := ABaseClass;
  Writer.WriteBeginObject;
  if FUseInlineCount then
  begin
    Writer.WriteName('@odata.count');  // was @xdata.count
    Writer.WriteInteger(FInlineCount);
  end;
  Writer.WriteName(JsonCollectionValue);
  Writer.WriteBeginArray;
end;

And XData.Module.QueryInfo.pas

procedure TXDataQueryInfo.SolveQueryString(const Query: string);
var
  QueryItem: TNameValuePair;
  LowerName: string;
begin
  for QueryItem in TSparkleUtils.GetQueryParams(Query) do
  begin
    if (QueryItem.Name = '') and (QueryItem.Value = '') then Continue;
    LowerName := LowerCase(QueryItem.Name);
    if LowerName = '' then
      LowerName := LowerCase(QueryItem.Value);

    if LowerName = '$filter' then
      SolveFilter(QueryItem.Value)
    else
    if LowerName = '$orderby' then
      SolveOrderBy(QueryItem.Value)
    else
    if LowerName = '$expand' then
      SolveExpand(QueryItem.Value)
    else
    if LowerName = '$top' then
      SolveTop(QueryItem.Value)
    else
    if LowerName = '$skip' then
      SolveSkip(QueryItem.Value)
    else
    if LowerName = '$format' then
      SolveFormat(QueryItem.Value)
    else
    if (LowerName = '$inlinecount') or (LowerName = '$count') then  // Added $count
      SolveInlineCount(QueryItem.Value)
    else
    if LowerName = '$select' then
      SolveSelect(QueryItem.Value)
    else
    if LowerName[1] = '$' then
      raise EXDataHttpUnsupportedQuery.Create(QueryItem.Name)
    else
      SolveCustomQuery(
        TSparkleUtils.PercentEncode(QueryItem.Name) + '=' +
        TSparkleUtils.PercentEncode(QueryItem.Value));
  end;
end;

procedure TXDataQueryInfo.SolveInlineCount(const Value: string);
begin
  if SameText(Value, 'allpages') or SameText(Value, 'true') then  // Added the $count options
    FInlineCount := TQueryInlineCount.AllPages
  else
  if SameText(Value, 'none') or SameText(Value, 'false') then
    FInlineCount := TQueryInlineCount.None
  else
    raise EQueryInvalidInlineCount.Create(Value);
end;

Is there a more elegant/simple way of doing this?

I can then tell the DevExtreme OData store to use version 4 and it then uses contains instead of substringof.

3

Yes. You can use a code like this:

uses Aurelius.Sql.Register, Aurelius.Sql.Interfaces;

TSQLGeneratorRegister.GetInstance.GetGenerator(‘POSTGRESQL’)
  .RegisterFunction(‘unaccent', TSimpleSQLFunction.Create(‘unaccent'));

The above is needed for the correct SQL to be built. Then for XData, you should use this:

uses XData.Query.Parser;

TQueryParser.AddMethod(‘unaccent’, TQueryMethod.Create('unaccent', 1));

No. The query parameters are not "pluggable". I suggest you create a feature request for detailing what is needed, we can eventually implement those for higher compatibility.