Create an ABAP CDS View that consumes a hierarchy CDS to get the root node ID for each node ID

Question

I created an ABAP CDS hierarchy and I want to use it in another CDS to get the root node ID as a field. How can I do that?

This is my source table:

@EndUserText.label : 'Hierarchy'
@AbapCatalog.enhancement.category : #EXTENSIBLE_CHARACTER_NUMERIC
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table hierarchy_table {
  key mandt   : mandt not null;
  key id      : char10 not null;
  parent      : char10;
}

This is my hierarchy source CDS:

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Hierarchy Source'
@Metadata.ignorePropagatedAnnotations: true
define view entity ZI_HierarchySource as select from hierarchy_table
  association [1..1] to ZI_HierarchySource as _hierarchy on $projection.parent = _hierarchy.id
{
      _hierarchy,
      key id,
      parent
}

This is my hierarchy CDS:

define hierarchy ZI_HIERARCHY
  as parent child hierarchy( source ZI_HierarchySource
  child to parent association _hierarchy
  siblings order by id ascending )
{
  id,
  parent,
  $node.node_id               as hiera_node_id,
  $node.parent_id             as hiera_parent_id,
  $node.hierarchy_is_orphan   as hiera_is_orphan,
  $node.hierarchy_is_cycle    as hiera_is_cycle,
  $node.hierarchy_rank        as hiera_rank,
  $node.hierarchy_parent_rank as hiera_parent_rank,
  $node.hierarchy_level       as hiera_level
}

And my end goal is a view :

define view entity ZI_NODE_ID_AND_ROOT_ID
  as select from hierarchy_table 
  association [1..1] to ZI_Hierarchy as hier on $projection.id = hier.id
{
  key id,
      root //somehow get it from the hierarchy?
}

In ABAP, I could use Hierarchy Node Navigator HIERARCHY_ANCESTORS, but I don't know how to do it in another CDS.

Answer 1

I managed to solve it myself with a table function consuming the hierarchy CDS and returning all the ancestors of each given node.

Then I can associate the table function CDS with my final CDS joining by node ID + hierarchy level = 1.

Table function:

@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
define table function ZI_TF_HierarchyAncestors 
  with parameters
    @Environment.systemField: #CLIENT
    p_client : abap.clnt
returns
{
    key Client         : abap.clnt;
    key ID             : idType;
    key AncestorLevel  : abap.int2;
        AncestorID     : idType;
      
}
implemented by method
  zcl_tf_amdp=>get_ancestors;

AMDP implementation:

class zcl_tf_amdp definition
  public
  final
  create public .

  public section.
    interfaces: if_amdp_marker_hdb.
    class-methods: get_ancestors for table function ZI_TF_HierarchyAncestors .
  protected section.
  private section.
endclass.



class zcl_tf_amdp implementation.

  method get_ancestors by database function for hdb language sqlscript options read-only using ZI_HIERARCHY.

    return select
        :p_client       as Client,
        start_id        as ID,
        hierarchy_level as AncestorLevel,
        node_id         as AncestorID
    from hierarchy_ancestors (
        source ZI_HIERARCHY
        start ( select hierarchy_rank as start_rank, node_id as start_id from ZI_HIERARCHY) )
        order by start_rank asc, hierarchy_rank desc;
  endmethod.

endclass.

Final CDS:

define view entity ZI_NODE_ID_AND_ROOT_ID as select from MasterDataNodeIDTable as Node
association[1..1] to ZI_TF_HierarchyAncestors as _NodeAncestors on _NodeAncestors.ID = Node.ID and
                                                                   _NodeAncestors.AncestorLevel = 1
{


  key id,          
      _NodeAncestors(p_client:$session.client).AncestorID as root
}