Mapping tree data structure/ self-referencing / recursive tables

[DanielWake]

Describe the feature

Map all nodes of a tree to the top level node of the tree.

Detail

Result set 0 contains a top level record Result set 1 contains a multiple records of the same type The goal is to map all records that are connected from record set 1, into the record in record set 0

Examples

Id - Name - ParentId 1 USA NULL 2 Pennsylvania 1 3 Texas 1 4 Florida 1 5 Erie 2 6 Scranton 2 7 Philadelphia 8 Houston 3 9 Austin 3 10 Miami 4

Example 1

RecordSet0 1 USA NULL RecordSet1 (All of the above records) Outcome USA contains Pennsylvania, Texas and Florida Pennsylvania contains Erie, Scranton and Philadelphia Texas contains Houston and Austin Floria contains Miami

Example 2

RecordSet0 2 Pennsylvania 1 RecordSet1 (All of the above records) Outcome Pennsylvania contains Erie, Scranton and Philadelphia All other records that aren't descendants are ignored.

Is this feature related to a problem, describe

I am using a self referencing table to map infinite 'levels' of an organisation structure and wish to expose the results via an API.

The table is called CompanyEntity and it contains a PK Id, a FK ParentId which maps to its own Id, and a FK of TenantId. The top level record has a null ParentId.

I have a query which will return the top level record for a tenant, and all of its descendants, or if I pass a companyEntityId parameter it will return that record and its own descendants.

Extra detail: the query passes in the TenantId as a mandatory parameter and CompanyId as an optional parameter. The union query has a where clause comparing the TenantId to the TenantId parameter, and (CompanyId = @CompanyId OR (@CompanyId IS NULL AND @ParentId IS NULL)).

At present I can only map the parent's children, and cannot map any further.

If this could be done returning just the one recordset that may make things easier, but either way would suit me. Perhaps if you could return multiple top level records in recordset0 and have them mapped.

Acceptance Criteria

Given you are modelling an organisation structure with a table containing a PK column of Id and a FK column of ParentId, which self-references the Id column When calling a query/stored procedure that returns two result sets, the first being the parent record and its children, and its children's children recursively until there are no more children (achievable with a union) Then record set 0 has its children mapped And the mapped children have their children mapped And their children - all the way until all children are mapped

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[jonwagner]

This is handled by the result structures API. Details are here: https://github.com/jonwagner/Insight.Database/wiki/Specifying-Result-Structures