Implement a Query with Join

Dei_Flavio · April 10, 2018, 7:08pm

Hello,

I"m in trouble to implement a query between two tables,

I report the structure of the tables below.

CREATE TABLE public.movimentimaster (

id BIGINT NOT NULL,

datamovimenti TIMESTAMP WITHOUT TIME ZONE NOT NULL,

note VARCHAR(255),

idsede BIGINT NOT NULL,

idutente BIGINT NOT NULL,

CONSTRAINT pk_movimentimaster PRIMARY KEY(id),

)

WITH (oids = false);

CREATE TABLE public.movimentidetail (

id BIGINT NOT NULL,

datamovimento TIMESTAMP WITHOUT TIME ZONE NOT NULL,

descrizione VARCHAR(255),

idcausalemovimento BIGINT NOT NULL,

idapparato BIGINT NOT NULL,

idutente BIGINT NOT NULL,

idsede BIGINT NOT NULL,

idmovimentomaster BIGINT NOT NULL,

CONSTRAINT pk_movimentidetail PRIMARY KEY(id),

CONSTRAINT fk_movimentidetail_movimentimaster_idmovimentomaster FOREIGN KEY (idmovimentomaster)
REFERENCES public.movimentimaster(id)

ON DELETE NO ACTION

ON UPDATE NO ACTION

NOT DEFERRABLE,
)

WITH (oids = false);

I would like to realize the query natively in aurelius:

select

mm.note, mm.datamovimenti,

md.descrizione

from movimentimaster mm

left OUTER join movimentidetail md on

(md.idmovimentomaster = mm.id) and ((md.descrizione = "TEST") or (md.descrizione is null))

order by mm.id

This query must report all the movimentimaster records and only the movement records that contain "TEST" in the description field or

Best Regards

wlandgraf · April 11, 2018, 11:32pm

Hello Flavio,

You can simply create a class TMovimentDetail with an association to TMovimentMaster:


TMovimentDetail = class
  FMaster: TMovimentMaster;

then do a search on MovimentDetail using your criteria:


Details := Manager.Find<TMovimentDetail>.
  Where(Linq[‘descrizione’]  = ‘TEST’ or Linq[‘descrizione’].IsNull)

Then you can get the master using the association:


for Detail in Details do
  Detail.Master // use master here

Dei_Flavio · April 12, 2018, 4:35pm

Hello Wagner,

Thanks for your explanations, another questions... how can I upload Datails in a TAureliusDataset?

Thanks in advance

wlandgraf · April 12, 2018, 7:10pm

What do you mean by upload? Do you want to list the Details? In this case just use one of available AureliusDataset methods, for example SetSourceList and pass the Details list to it.

Dei_Flavio · April 16, 2018, 7:23pm

Sorry, I did not understand, can you give me an example code?

Thk.

wlandgraf · April 16, 2018, 7:59pm

Here is the link of documentation that explains how to provide objects to the Aurelius Dataset:

http://www.tmssoftware.biz/business/aurelius/doc/web/providing_objects.html