XData/Aurelius and LEFT JOIN (once again)

Hello Wagner

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

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.

Hello Thomas,

Sorry but I didn't understand your problem? Joins are always automatically build from association mappings declared using Association attribute.

Sorry, I have to use google translate for help.

I want to display such a list in the client

[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.

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.

Well, with provided information I can give you an exact code as we are talking about concepts here.

  1. You can always use pure SQL whenever you need. Is there something preventing you from doing so?
  2. You can use multimodel design if you want to have some entities present in a model, and some entities not present in a model.
  3. Can TTab_1 and TTab_2 will not change if they need to refer to TTab_3?

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
I create a database view and then ... ??
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.
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.

1 Like

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>;
  MySpecificResults: TList<MySpecificResult>;
  // Build a list of TMySpecificResult

  // Now return it
  Result := MySpecificResults;

Yes, that ist what I do. But it doesn't work :slight_smile:

Here is my Memory Entity:

  [Id('vslID', TIdGenerator.Guid)]    // Do I ned this ???
  TServicelistX = class
    FvslID: TGUID;

    FService: TsngService;   // this is an existing Entity
    FActive   : TsngActiveService; // this is an existing Entity
    property Service  : TsngService read FService write FService;
    property Active   : TsngActiveService read FActive write FActive;

What does "doesn't work" mean, exacty?

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.

Here are the code

  TServicelistEntry = class
    FsngService       : TsngService;
    FsngActiveService : TsngActiveService;
    property sngService       : TsngService read FsngService write FsngService;
    property sngActiveService : TsngActiveService read FsngActiveService write FsngActiveService;
function TMyFunc.GetServicelist1: TList<TServicelistEntry>;
  LSession           : IDBSession;
  LServicelist       : TList<TsngService>;
  LActiveServicelist : TList<TsngActiveService>;
  LService    : TsngService;
  LActiveSvc  : TsngActiveService;
  c           : Integer;
  slEntry1    : TServicelistEntry;
  slEntry2    : TServicelistEntry;
  Result := TList<TServicelistX>.Create;
    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
          if c > 1 then begin
            slEntry2 := TServicelistX.Create;
            slEntry2.sngService := LService;
            slEntry2.sngActiveService  := LActiveSvc;
            Result.Add( slEntry2);
          end else
            slEntry1.sngActiveService  := LActiveSvc;
      Result.Add( slEntry1);

//    on e: Exception do

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

This code is now the basis for my next tests.

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.

Sorry, I have renamed ServicelsitX to ServicelistEntry.
Here is the Association

  TServicelistEntry = class

    [JoinColumn('svc_id', [TColumnProp.Required], 'svc_id')]
    FsngService       : TsngService;

    [JoinColumn('asv_id', [TColumnProp.Required], 'asv_id')]
    FsngActiveService : TsngActiveService;

    property sngService       : TsngService read FsngService write FsngService;
    property sngActiveService : TsngActiveService read FsngActiveService write FsngActiveService;

The Project:

I have a table, a list of services.
Some services are active.
Some services can be active 2, 3, 4 times

I want have a Servicelist of all services. If a service runs I want get it multiple

Can you please reduce this to a sample project so we can work on it?

Ok, I will try it. Can I upload the files?

Yes, you can just attach the files to your message.