how to join 2 tables via Aurelius

WEB TMS WEB Core
1

Hello,

I am new to Aurelius, and wishing to use it for my web applications.
I am finding it difficult to see how you Join tables together using Aurelius "scripting"
A lot of the demo's and examples I find are all based on using a single table.

Basically I am trying to get the results from a simple SQL equivalent script:

select A.field1, A.field2, BField4
from ATABLE A
inner join BTABLE B
on B.field1 = A.field1
and B.field2 = A.field2
where A.field1 = x

Hops this makes sense.

Many thanks.

3

Maybe this blog post helps?

1 Like
4

Hello Wagner,

Thank you for this.
Unfortunately this was no help.

in my case the tables are Not linked with an ID, auto mapping does not work I do not think.

I have a table with Orders
I have a table with Items

The two tables are not linked together in any way by primary ID's, but for a report, I require to get a list of the orders, with some additional date from the item table. the only link is an unique Item Ref code.

Thank you.

5

In this case you can explicitly map the association between the two tables using atributes Association and JoinColumn, and in the latter you specify by which fields they are associated.

[Association([], CascadeTypeAllButRemove)]
[JoinColumn('FK_Field', [], 'REF_CODE')]
property SongFormat: TSongFormat read FSongFormat write FSongFormat;
6

Hello Wagner,

I seem to be making progress (I think )

However when I make my call via browser and get this result

image

I was hoping to see the value from the field KUN_INDEX which is "TEST K INDEX"

Thank you

David Royall

7

Hello Wagner,

Further to my previous mail, here is the aspects of the code I have added. This may make it easier for you to assist.

THE ENTITY(s)
[Entity]
[Table('ANGHEAD')]
[Id('FANG_ORIGNR', TIdGenerator.None)]
[Id('FANG_ANR', TIdGenerator.None)]
TANGHEAD = class
private
[Column('ANG_ORIGNR', [TColumnProp.Required])]
FANG_ORIGNR: Integer;

[Column('ANG_ANR', [TColumnProp.Required])]
FANG_ANR: Integer;

[Column('ANG_EXECNR', [TColumnProp.Required])]
FANG_EXECNR: Integer;

[Column('ANG_VONNR', [TColumnProp.Required])]
FANG_VONNR: Integer;

[Column('ANG_KNR', [TColumnProp.Required])]
FANG_KNR: Integer;

[Association([TAssociationProp.Lazy], [])]
[JoinColumn('ANG_KNR', [], 'KUN_NUMMER')]
FKUN_NUMMER: Proxy;

function GetKUN_NUMMER: TKUNDE;
procedure SetKUN_NUMMER(const Value: TKUNDE);

public

property ANG_ORIGNR: Integer read FANG_ORIGNR write FANG_ORIGNR;
property ANG_ANR: Integer read FANG_ANR write FANG_ANR;
property ANG_EXECNR: Integer read FANG_EXECNR write FANG_EXECNR;
property ANG_VONNR: Integer read FANG_VONNR write FANG_VONNR;
property ANG_KNR: Integer read FANG_KNR write FANG_KNR;
property KUN_NUMMER: TKUNDE read GetKUN_NUMMER write SetKUN_NUMMER;

end;

[Entity]
[Table('KUNDE')]
[Id('FKUN_TYP', TIdGenerator.None)]
TKUNDE = class
private
[Column('KUN_INDEX', [TColumnProp.Required], 30)]
FKUN_INDEX: string;

[Column('KUN_FILIALE', [TColumnProp.Required])]
FKUN_FILIALE: Integer;

[Column('KUN_LAND', [TColumnProp.Required])]
FKUN_LAND: Integer;

[Column('KUN_REGION', [TColumnProp.Required])]
FKUN_REGION: Integer;

[Column('KUN_KONTO', [TColumnProp.Required])]
FKUN_KONTO: Integer;

[Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('KUN_TYP', [TColumnProp.Required], 'ADD_TYP')]
[JoinColumn('KUN_NUMMER', [TColumnProp.Required], 'ADD_NUMMER')]
FKUN_TYP: Proxy<TADR_DATA>;
function GetKUN_TYP: TADR_DATA;
procedure SetKUN_TYP(const Value: TADR_DATA);

property KUN_INDEX: string read FKUN_INDEX write FKUN_INDEX;
property KUN_FILIALE: Integer read FKUN_FILIALE write FKUN_FILIALE;
property KUN_LAND: Integer read FKUN_LAND write FKUN_LAND;
property KUN_REGION: Integer read FKUN_REGION write FKUN_REGION;
property KUN_KONTO: Integer read FKUN_KONTO write FKUN_KONTO;

end;

implementation

function TANGHEAD.GetKUN_INDEX : TKUNDE;

begin

result := FKUN_INDEX.Value;

end;

procedure TANGHEAD.SetKUN_INDEX(const Value: TKUNDE);

begin

FKUN_INDEX.Value := Value;

end;

THE SERVICE IMP
function TCOrderService.GetCorderlist: TObjectList;
var
Manager: TObjectManager;
begin
Result := TXDataOperationContext.Current.GetManager.Find
.Select(
TProjections.ProjectionList
.Add( Linq['ANG_ANR'].As_('Order_No') )
.Add( Linq['ANG_TEXT'].As_('Text') )
.Add( Linq['ANG_KLASSIFIZIERUNG'].As_('Classification') )
.Add( Linq['ANG_VONNR'].As_('Delivery_By') )
.Add( Linq['ANG_KNR'].As_('Customer_No') )
.Add( Linq['KUN_NUMMER'].As_('Customer_Name') )
)
.Where((Linq.Eq('ANG_ANR', 1006))
and (Linq.Eq('ANG_KNR', 2))
and (Linq.Eq('ANG_OK', 0))
and (Linq.Eq('ANG_KLASSIFIZIERUNG', '')))
.OrderBy('ANG_ANR')
.Listvalues;

end;

RESULT
image

Thank you for all your assistance.

8

Aurelius works with associations. Please understand that the associations are objects, not primitive types. Maybe this topic of XData documentation might help you in understand how associated objects are represented in JSON: