Difficult (dynamic) query with XData

BIZ TMS XData
1

Hello,

I have a query problem.
I need the data from one table with a filter in another table. But the connection is as follows:

table CONTRACTS
C_ID
C_DATE
C_TEXT

table DOCUMENTS
D_ID
C_ID (foreign key to CONTRACTS)
D_TYPE
D_TEXT

I need a list of all contracts that contain type 'A', 'D' or 'X' documents.

In SQL I can write:

select * from CONTRACTS
where C_ID in (select C_ID from DOCUMENTS where D_TYPE in ('A', 'D', 'X'))

Both tables and join fields can be configured.
But they are all simple links.

This data is available:

entity/table name that is to be queried
field name for the query
join-entity/table name
field name of join-entity/table

Very important

The client is NOT an XData client.

The client only knows the server URL, the entity name and the 3 other necessary names.

Alternatively, all primary keys can be used with a first query. The list of keys can then be passed on to the 2nd query.

But there is no IN operator over XData (URL)

Is there a solution for this?

2

Hi Thomas,

You should create a specific endpoint for such query, using a service operation.

I assume you are asking about how to do it using Automatic CRUD endpoints, but there is no such feature for them (subselects).

So just create a service operation and implement the query server-side.

3

OK, thanks, I suspected it.
The goal is actually to supply an external tool with data sets. These are very freely configurable. It involves around 200 tables, some of which can have several hundred thousand entries.
The model has been around for a while. I have gained nothing with one (hundreds of) special queries.

I will then go with the approach of reading table 1 and then reading table 2 in a loop for each entry. This way I can generate the result myself.

I will wait and see how fast the runtime will be. Perhaps everything will be fast enough.

1 Like
4

Is there a way to read a table including foreign keys without knowing the field names?

I need the values ​​of the foreign keys of a table.

I get this

{ ... [Fields] ...
"fk_fieldname@xdata.proxy": "this_entity_name(this_primary_key)/fk_entity_name"
}

but I want this:

{ ... [Fields] ...
"fk_fieldname": "fk_value"
}

$expand is the wrong way, I need the flat array for the external connection

5

What you can do is create a transient, public property with a different name where you return the FK value in it. Then flag is with a [XDataJsonProperty] attribute so it appears in JSON.

6

What is the fastest way to read the foreign key?
We don’t want another subquery to be executed. The foreign key should already be known.

class MyEntity2
...
FMyField: Proxy<TMyEntity1>;

public

property MyField: TMyEntity1 read GetMyField write SetMyField;

[XDataProperty]
property MyFieldFK: TGUID read GetMyFieldFK write SetMyFieldFK;

end; // class

function TMyEntity2.GetMyFieldFK: TGUID;
begin
  Result := MyField.Id_Field_From_MyEntity1;
end;

Is that OK? Or should it be implemented differently?

7

You can read FMyField.Key property.