Master Detail query mapping

Master Detail query mapping

  

Hello all,


I have two tables (A & B) where B has a foreign key for A. In a REST method I want to return a structure with the following format:

[

ATypeElement1 : [BTypeElement1,BTypeElement2,BTypeElement3],

ATypeElement2 : [BTypeElement4,BTypeElement5,BTypeElement6],

ATypeElement3 : [BTypeElement7,BTypeElement8,BTypeElement9]

]

So I've created a structure that consists on a ATypeElement and a list of BTypeElements. I've tried a SQL query with a left join but doesn't map how I it "should".

My question is: What is the best way (query, aggregate, etc) to map what I have on my database into this structure?


Kind regards,

Hélio Rocha


Hi Hélio,

Aggregates and SQL nodes are not able to output nested results, so you typically have two approaches:

  • The simplest is to have an Aggregate to get all A, and then build the list you want by using a ForEach over the results and doing an Aggregate to the all the Bs for the current A.
  • Have an Aggregate for the join between A and B, ordered by A, and then process the resulting list to produce a new one, creating new records every time a new value for A is found.

Does any of this work out for you?

Regards,

Paulo Ferreira