Dynamic building queries with IXDataQueryBuilder

BIZ TMS XData
1

Hello!

I try to create a common routine to use for queries in XData. Here's the (simplified) code:

function GetQueryString(Qry: IXDataQueryBuilder; Ds: TAureliusDataSet; Filter: string): string;
var FilterDateTime: TDateTime;
    FilterInt: integer;
    Filterfloat: Double;

begin
  FilterDateTime := StrToDateTimeDef(Filter, MinDateTime);
  FilterInt := StrToIntDef(Filter, -9999);
  FilterFloat := StrToFloatDef(Filter, -9999);


  if Filter.Trim <> '' then
    for var col in DS.Fields do begin

      if IsString then begin
        case col.DataType of
          ftString: Qry.Filter(Linq[col.FieldName].ILike('%'+Filter+'%'));
...
        end;
      end;

      if IsInt then begin
        case col.DataType of
          ftSmallint: Qry.Filter(Linq[col.FieldName] = FilterInt);
...
        end;
      end;

      if IsFloat then begin
        case col.DataType of
          ftFloat: Qry.Filter(Linq[col.FieldName] = FilterFloat);
...
        end;
      end;

      if IsDateTime then begin
        case col.DataType of
          ftDateTime: Qry.Filter(Linq[col.FieldName] = FilterDateTime);
...
        end;
      end;
    end;

  Result := Qry.QueryString;

  LogProxy.Log(llVerbose, Result);

The result is without logical operators (and, or):

$filter=((((((((((((((((((((((TaxId'%2510e%25')%20and%20(SocialId'%2510e%25'))%20and%20(Name'%2510e%25'))%20and%20(Address'%2510e%25'))%20and%20(Tel1'%2510e%25'))%20and%20(Tel2'%2510e%25'))%20and%20(Mobile'%2510e%25'))%20and%20(Email'%2510e%25'))%20and%20(BankAccount'%2510e%25'))%20and%20(BankName'%2510e%25'))%20and%20(BankBIC'%2510e%25'))%20and%20(AgentBIC'%2510e%25'))%20and%20(ExternalId'%2510e%25'))%20and%20(AccountingId'%2510e%25'))%20and%20(GLNCode'%2510e%25'))%20and%20(Notes'%2510e%25'))%20and%20(Param1'%2510e%25'))%20and%20(Param2'%2510e%25'))%20and%20(Param3'%2510e%25'))%20and%20(Param4'%2510e%25'))%20and%20(Param5'%2510e%25'))%20and%20(Param6'%2510e%25'))

I would kindly ask what I'm doing wrong?

2

I see several and words in your filter string. So I don't see why you say the result is without logical operators.

Now, it would be very convenient for everyone that you first try to debug your code, simplifying it to a smaller case, instead of throwing a big huge string of conditions that we don't even know where they come from, as there is no clue about how you are calling your code and also the code is missing parts (IsInt, etc.).

So please try to understand better your code and reduce it to a minimal (e..g, only two field comparisons) for better support.

3

Sorry, you're right, I confused/mixed with another problem, but it was not related to TMS.

Here's a short URL

$filter=(Name'%2510e%25')

and the error is:

{
    "error": {
        "code": "QueryParenCloseExpectedError",
        "message": "Parenthesis close \")\" expected at position 6"
    }
}

It's missing the comparation operator (like 'eq'.. etc.). If I manually correct the query to

http://localhost:2001/easy/partner?$filter=(Name eq '%25test%25')

then it works (I added 'eq' to the query). I Use Linq to create the query as shown in the documentation.

IsInt, IsFloat, IsString... Are helper functions just to see if the user typed string could be used to search appropriate fields. For example if the user inserts 'test', then is not necessary to search on TDateTime fields in the DB.

4

Minimal code:

function GetQueryString(Qry: IXDataQueryBuilder; Ds: TAureliusDataSet; Filter: string): string;
begin
    for var col in DS.Fields do begin
      Qry.Filter(Linq[col.FieldName] = FilterInt);
    end;

  Result := Qry.QueryString;
5

I took the effort to create a sample reproducing the issue. I cannot see it. Please modify it so it reproduces your issue.

TestQueryFilter.zip (7.2 KB)

6

Hello!

I tried and your example works perfectly. But I found the problem. The error is on my side, because I used the points for subproperties instead of slashes. Example that does not work:

http://localhost:2001/easy/Partner?$filter=(Zip.Code eq '10e' or Zip.City eq '10e')

but this works:

http://localhost:2001/easy/Partner?$filter=(Zip/Code eq '10e' or Zip/City eq '10e')

But strangely, the error displayed is strange, because it's not related to the parenthesis, it's a syntax error.

Thank you for the help!

1 Like
7

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.