Advanced Query with complex structures

Advanced Query with complex structures


I have a structure Family with the following attributes:
Mother - datatype human
Father - datatype human
Child1 - datatype human
Child2 - datatype human

and the structure human has the following attributes
name - text
surname - text

I need to return a list of families, which at the moment I can only do by
doing an advanced query with 4 human structures as output,
then a for-loop to assign the humans to the family and then listappend it.

why can't I use family as output structure?
so I don't have to use a for-loop?

(context I need to json the result hence the structure of structures)

Just as food for thought, can't you do a second query with Family as output structure by getting the result structures of the initial query, instead of the for loop?
The basic problem is that your Structure format is nested but SQL returns flat output. :(

Some approaches:

1. 4 queries that return Human, then assign to the right attributes of Family.
2. A query that does something like:

SELECT AS MotherFirstName, m.surname AS MotherLastName, AS FatherFirstName, f.surname AS FatherLastName, AS Child1FirstName, c1.surname AS Child1LastName, AS Child2FirstName, c2.surname AS Child2LastName FROM [Humans] m LEFT JOIN [Humans] f ON m.ID = f.SpouseId LEFT JOIN [Humans] c1 ON m.ID = c1.ParentId LEFT JOIN [Humans] c2 ON m.ID = c2.ParentId WHERE m.Gender = 'F'


I've never tried #2 but I think it may work.

yeah I know it's nested, but tbh, I don't think it's that rare :)

Getting more common by the day.
If this is not just one case scenario, maybe it's worth while taking a look at this link:

But there's no easy way of doing it in OutSystems yet...
I just had to deal with this a few weeks ago, I ended up looping over the results and sorting them into the appropriate sub-bins, because my structures had lists within them of the same type (parent/child relationship). :(