We have several TMS XData REST servers (Comandline Program) and address them via XDataClient from FMX and web clients.
There is a large number of queries to the data in different ways. Here's an example:
I have a lot of such constructs. Very often with more than 2 tables.
From time to time (in the program life), new data is always added in the form of additional tables.
So it must be very easy to add such new tables / columns to the program.
Table / Entity (TTab_1): TAB_1 = T1_ID, T1_NAME, T1_VALUE
Table / Entity (TTab_2): TAB_2 = T2_ID, T1_ID, T2_CAPTION
select * from TAB_1 left join TAB_2 on TAB_1.T1_ID = TAB_2.T1_ID
The best thing would be if I got a list of TList <TMySelectRec> in ServiceFunction of REST-Server
TMySelectRec = record
Tab1: TTab_1;
Tab2: TTab_2
end;
The entity units are all made with the data modeler. And that now works without manual intervention.
What is the easiest way to do this?
Is it even possible to write a JOIN in the XData URL?
Additional note: Some tables that I use in the JOIN are used by many other master tables.
[Name] [Value] [Caption]
John 23 MisterX
John 23 Jonnyboy
Frank 15 <NULL>
Jamie 45 CoolMan
Some names have no caption.
Some names have multiple captions
This is version 1 of our release (our program)
A customer comes in the next month and wants to see more data
[Name] [Value] [Caption] [Place]
John 23 MisterX <NULL>
Jamie 45 CoolMan New York
Jose 38 Zorro San Diego
We are expanding the database by a table TAB_3
We program a new query: select * from TAB_1 left join TAB_2 left join TAB_3
The original entities TTab_1 and TTab_2 must NOT change !!!
The new entity TTab_3 is only used by the new customer
With a "normal" database application, I would simply join the new table with the old one
Whether the tables are permanently linked or not should not matter.
ToDo:
Point 1: A query with Aurelius in the server (I can also assemble this by hand)
Point 2: Transfer the finished data to the client via XData REST API.
A service function result would be great: TList <TMySelectRec> a combination of the entities used
I think these are quite normal and common requirements. In our old programs there are definitely over 70% such queries. These are working data.
I can not change an existing entity. I only can make new Entities and new service functions.
You say, this is a Association attribute
But I want a LEFT JOIN with a separate table
I want all datasets from the master table. Also if Detail table is emty.
I want 2 Datasets, if Detail table has 2 Dataset of the master.
BUT, the Detail table ist used by many many tables.
For example, a special table for Tags.
I have a table with TAG names and description.
Then when I have to display a list, I want to make a query that returns all the records. If there are several TAGs, there are also several lines in the result.
Ok, it is difficult for me to explain, because my english ist not so good.
I want to try to make it easy
I'm looking for an easy way to create queries/requests to my XData server and deliver individual data sets. It is not a problem, to create an own (virtual) Entity for every request/response (Kind of request/response). The new entity or the data who are sent, should arrive in a TDataset in the client.
It would be great if I could group existing entities together. In a record or a class
On server side:
I make 2 queries with aurelius: Master List and Detail List
Then I am putting them together in a for loop
or
I create a database view and then ... ??
or
I create a manual SQL-Query with the join I need. I go through the result and send it to the client
The Problem, I cannot extend the basis entities. They would get too big.
AND
I have to make sure that everything goes very quickly.
Currently I try to make a new entity, which includes 2 existing entities.
In my server I take 2 Aurelius Queries und try to combine the data sets.
But I'm probably running into a dead end
Sounds like you need to implement a custom XData Service that would respond with dynamically generated back-end queries results based on some input parameters (which could be a well formed select with custom joins).
You cannot make the joins you need by using custom “URL parameters” over Aurelius/XData automatically exposed entities, especially when as you mentioned sometimes you’ll have new db tables to join.
I think, no.
We build a very big management system, that we supervising over many years. We have very small customers and very big ones. Some need only a small part of the data and some many more.
All requests can be service functions. Not URL Parameter.
The Main part is to combine entities in a result set
@Ferrari_Julio suggestion is the main one to be followed.
@Kaeswurm_Thomas I more or less understand what you want, but as I said, it's only conceptual. There might be different approaches for your need, depending on each case. Julio's suggestion is the most generic one. But it really depend on each specific case, each one might have different solutions and one that fits best.
One common approach is create a DTO object. A class which is not an entity (not persisted in database), but just an "in-memory" one that you manually build in your XData service and return it (or a list of it) in function result.
function TMyService.MyResults: TList<TMySpecificResult>;
var
MySpecificResults: TList<MySpecificResult>;
begin
// Build a list of TMySpecificResult
{...}
// Now return it
Result := MySpecificResults;
end;
[Entity]
[Model('sngKernel')]
[Id('vslID', TIdGenerator.Guid)] // Do I ned this ???
TServicelistX = class
private
FvslID: TGUID;
FService: TsngService; // this is an existing Entity
FActive : TsngActiveService; // this is an existing Entity
public
property Service : TsngService read FService write FService;
property Active : TsngActiveService read FActive write FActive;
end;
There is no "memory entity". If you are not going to persist it, Aurelius doesn't need to know about it. You can remove all attributes like Entity, Model, Id from it.
Because of this question I thought you were looking for a way to do so; that's why I mentioned it was not possible.
About your TServicelistX, it doesn't have to be an Aurelius entity, as Aurelius works with entities persistence. Your service may return a simple class, or a tlist of your custom class.
function TMyFunc.GetServicelist1: TList<TServicelistEntry>;
var
LSession : IDBSession;
LServicelist : TList<TsngService>;
LActiveServicelist : TList<TsngActiveService>;
LService : TsngService;
LActiveSvc : TsngActiveService;
c : Integer;
slEntry1 : TServicelistEntry;
slEntry2 : TServicelistEntry;
begin
Result := TList<TServicelistX>.Create;
try
LSession := TDBSession.Create( ... not relevant ...);
LServicelist := LSession.objManager.Find<TsngService>.List; // Aurelius Objectmanager
LActiveServicelist := LSession.objManager.Find<TsngActiveService>.List; // Aurelius Objectmanager
for LService in LServicelist do begin
slEntry1 := TServicelistX.Create;
slEntry1.sngService := LService;
c := 0;
for LActiveSvc in LActiveServicelist do begin
if LActiveSvc.Service.ServiceID = LService.ServiceID then begin
inc(c);
if c > 1 then begin
slEntry2 := TServicelistX.Create;
slEntry2.sngService := LService;
slEntry2.sngActiveService := LActiveSvc;
Result.Add( slEntry2);
end else
slEntry1.sngActiveService := LActiveSvc;
end;
end;
Result.Add( slEntry1);
end;
except
// on e: Exception do
//
end;
end;
I have made some tests. Sometime, I've got error "not entity", sometime "no json converter" and last it was no error, but the result set was empty
I cannot reconstruct every error
I can't see the relationship between TServicelistEntry and TServiceListX?
We are moving forward now with a more specific case, but ideally we need a specific code/project reproducing a specific problem, then we can fix and/or suggest a specific solution.
