Achieve Hierarchy, Parent/Child Relationship in an Effective and Easy Way

Achieve hierarchy, Parent/Child Relationship in an effective and easy way

Unfortunately, if you can't change the data model, and you're using MySQL, you're stuck in a situation where you need recursive queries and you're using a DBMS that doesn't support recursive queries.

Quassnoi wrote an interesting series of blog articles, showing techniques for querying hierarchical data. His solutions are quite clever, but very complex.
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

PostgreSQL is another open-source RDBMS, which does support recursive queries, so you could fetch a whole tree stored in the way you show. But if you can't change the data model, I'd assume you can't switch to a different RDBMS.

There are several alternative data models that make it much easier to fetch arbitrarily-deep trees:

Closure Table
Nested Sets aka Modified Preorder Tree Traversal
Path Enumeration aka Materialized Path

I cover these in my presentation Models for Hierarchical Data with SQL and PHP, and in my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Finally, there's another solution that I've seen used in the code for Slashdot, for their comments hierarchies: They store "parent_id" like in Adjacency List, but they also store a "root_id" column. Every member of a given tree has the same value for root_id, which is the highest ancestor node in its tree. Then it's easy to fetch a whole tree in one query:

SELECT * FROM site WHERE root_id = 123;

Then your application fetches all the nodes back from the database into an array, and you have to write the code to loop over this array, inserting the nodes into a tree data structure in memory. This is a good solution if you have many separate trees, and each tree has relatively few entries. It's good for Slashdot's case.

Create dynamic parent-child hierarchy?

Here is a simple tree-building algorithm that does what you need.

Note that this algorithm is not optimized in terms of performance. If you manage large amounts of data, you may want to revise some aspects, for example use SORTED or HASHED rather than STANDARD tables to improve the lookup READ TABLEs.

The algorithm is also not refactored for optimum code style. For example, clean code suggests we may want to extract a couple of methods to improve readability.

CLASS hierarchy_builder DEFINITION
    PUBLIC FINAL CREATE PUBLIC.

  PUBLIC SECTION.

    TYPES:
      BEGIN OF ts_input,
        color    TYPE string,
        producer TYPE string,
        weight   TYPE string,
        airplane TYPE string,
      END OF ts_input.

    TYPES tt_input TYPE
      STANDARD TABLE OF ts_input
        WITH EMPTY KEY.

    TYPES tt_attributes TYPE string_table.

    TYPES:
      BEGIN OF ts_output,
        id        TYPE string,
        attribute TYPE string,
        value     TYPE string,
        level     TYPE i,
        parent_id TYPE string,
      END OF ts_output.

    TYPES tt_output
      TYPE STANDARD TABLE OF ts_output
        WITH EMPTY KEY.

    CLASS-METHODS build_hierarchy
      IMPORTING
        it_data          TYPE tt_input
        it_hierarchy     TYPE tt_attributes
      RETURNING
        VALUE(rt_result) TYPE tt_output.

ENDCLASS.

CLASS hierarchy_builder IMPLEMENTATION.

  METHOD build_hierarchy.

    DATA(lv_parent_attribute) = ``.
    DATA(lv_next_id) = 1.

    LOOP AT it_hierarchy INTO DATA(lv_child_attribute).

      DATA(lv_level) = sy-tabix.

      LOOP AT it_data INTO DATA(ls_data).

        DATA(lv_parent_id) = ``.

        ASSIGN COMPONENT lv_child_attribute
          OF STRUCTURE ls_data
          TO FIELD-SYMBOL(<lv_child_value>).

        IF lv_parent_attribute IS NOT INITIAL.

          ASSIGN COMPONENT lv_parent_attribute
            OF STRUCTURE ls_data
            TO FIELD-SYMBOL(<lv_parent_value>).

          READ TABLE rt_result
            INTO DATA(ls_parent)
            WITH KEY
              attribute = lv_parent_attribute
              value = <lv_parent_value>.

          lv_parent_id = ls_parent-id.

        ENDIF.

        READ TABLE rt_result
          TRANSPORTING NO FIELDS
          WITH KEY
            attribute = lv_child_attribute
            value = <lv_child_value>
            parent_id = lv_parent_id.

        IF sy-subrc <> 0.

          INSERT VALUE #(
              id = |{ lv_next_id }|
              attribute = lv_child_attribute
              value = <lv_child_value>
              level = lv_level
              parent_id = lv_parent_id )
            INTO TABLE rt_result.

          lv_next_id += 1.

        ENDIF.

      ENDLOOP.

      lv_parent_attribute = lv_child_attribute.

    ENDLOOP.

  ENDMETHOD.

ENDCLASS.

Verified with the following unit test. It represents the example you gave:

CLASS ltc_unit_tests DEFINITION
    FOR TESTING RISK LEVEL HARMLESS DURATION SHORT.

  PRIVATE SECTION.

    METHODS builds_example FOR TESTING.

ENDCLASS.

CLASS ltc_unit_tests IMPLEMENTATION.

  METHOD builds_example.

    DATA(lt_data) =
      VALUE hierarchy_builder=>tt_input(
        ( color = 'green' producer = 'CompanyA' weight = '330' airplane = 'A350' )
        ( color = 'green' producer = 'CompanyA' weight = '222' airplane = 'A320' )
        ( color = 'green' producer = 'CompanyB' weight = '222' airplane = 'B450' )
        ( color = 'yellow' producer = 'CompanyA' weight = '330' airplane = 'H450' ) ).

    DATA(lt_hierarchy) =
      VALUE hierarchy_builder=>tt_attributes(
        ( `PRODUCER` )
        ( `WEIGHT` )
        ( `AIRPLANE` ) ).

    DATA(lt_result) =
      hierarchy_builder=>build_hierarchy(
        it_data = lt_data
        it_hierarchy = lt_hierarchy ).

    DATA(lt_expected) =
      VALUE hierarchy_builder=>tt_output(
        ( id = '1' attribute = 'PRODUCER' value = 'CompanyA' level = 1 parent_id = '' )
        ( id = '2' attribute = 'PRODUCER' value = 'CompanyB' level = 1 parent_id = '' )
        ( id = '3' attribute = 'WEIGHT' value = '330' level = 2 parent_id = '1' )
        ( id = '4' attribute = 'WEIGHT' value = '222' level = 2 parent_id = '1' )
        ( id = '5' attribute = 'WEIGHT' value = '222' level = 2 parent_id = '2' )
        ( id = '6' attribute = 'AIRPLANE' value = 'A350' level = 3 parent_id = '3' )
        ( id = '7' attribute = 'AIRPLANE' value = 'A320' level = 3 parent_id = '4' )
        ( id = '8' attribute = 'AIRPLANE' value = 'B450' level = 3 parent_id = '4' )
        ( id = '9' attribute = 'AIRPLANE' value = 'H450' level = 3 parent_id = '3' ) ).

    cl_abap_unit_assert=>assert_equals(
        act = lt_result
        exp = lt_expected ).

  ENDMETHOD.

ENDCLASS.

MySQL Query for Parent Child relationship

Try this query

SELECT *
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_code FROM mytable WHERE unique_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 9, @l := 0) vars,
        mytable m
    WHERE @r <> 0) T1
JOIN mytable T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

Parent-Child relationship in Talend

Here's the solution I put together:

Sample Image
You need to split your rows into parents and children based on their MemberCode. You write the parents to file with DependentLastName and DependentFirstName being empty, while saving the parent info to global variables (ParentLastName and ParentFirstName) in a tSetGlobalVar.

When you move to the next row, which is a child row, your parent has already been saved as it's always the first in the group. So you can retrieve its first and last name using the global variables in the children output, and write this to the same physical file.

Both tFileOutputDelimited components have identical settings; they are in append mode, and have the option Custom the flush buffer size set to 1 (this is important in order to keep the rows sorted in the right order).

Preserve parent-child relationships when copying hierarchical data

A CTE works nicely with MERGE, but is problematic in SQL Server 2005. Sorry for the misleading comment earlier.

The following shows how to clone a project (with multiple trees) and fix up up the parentage to separate the new forest from the old. Note that it does not depend on any particular arrangement of Id's, e.g. they need not be dense, monotonically increasing, ... .

-- Sample data.
declare @Projects as Table
  ( Id Int Identity, ProjectId Int, Value VarChar(16), ParentId Int Null );
insert into @Projects ( ProjectId, Value, ParentId ) values
  ( 611, 'Animal', 0 ),
  ( 611, 'Frog', 1 ),
  ( 611, 'Cow', 1 ),
  ( 611, 'Jersey Cow', 3 ),
  ( 611, 'Plant', 0 ),
  ( 611, 'Tree', 5 ),
  ( 611, 'Oak', 6 );
-- Display the raw data.
select * from @Projects;

-- Display the forest.
with IndentedProjects ( Id, ProjectId, Value, ParentId, Level, Path ) as
  ( -- Start with the top level rows.
  select Id, ProjectId, Value, ParentId, 0, Convert( VarChar(1024), Right( '000' + Convert( VarChar(4), Id ), 4 ) )
    from @Projects
    where ParentId = 0
  union all
  -- Add the children one level at a time.
  select P.Id, P.ProjectId, P.Value, P.ParentId, IP.Level + 1, Convert( VarChar(1024), IP.Path + '<' + Right( '000' + Convert( VarChar(4), P.Id ), 4 ) )
    from IndentedProjects as IP inner join
      @Projects as P on P.ParentId = IP.Id
  )
  select Space( Level * 2 ) + Value as [IndentedValue], Id, ProjectId, Value, ParentId, Level, Path
    from IndentedProjects
    order by Path;

-- Clone the project.
declare @OldProjectId as Int = 611;
declare @NewProjectId as Int = 42;
declare @Fixups as Table ( OldId Int, [NewId] Int );
begin transaction -- With suitable isolation since the hierarchy will be invalid until we apply the fixups!
insert into @Projects
  output Inserted.ParentId, Inserted.Id
    into @Fixups
  select @NewProjectId, Value, Id -- Note that we save the old Id in the new ParentId.
    from @Projects as P
    where ProjectId = @OldProjectId;
-- Apply the fixups.
update PNew
  set ParentId = IsNull( FNew.[NewId], 0 )
  -- Output the fixups just to show what is going on.
  output Deleted.Id, Deleted.ParentId as [ParentIdBeforeFixup], Inserted.ParentId as [ParentIdAfterFixup]
  from @Fixups as F inner join
    @Projects as PNew on PNew.Id = F.[NewId] inner join -- Rows we need to fix.
    @Fixups as FOld on FOld.OldId = PNew.ParentId inner join
    @Projects as POld on POld.Id = FOld.OldId left outer join
    @Fixups as FNew on FNew.OldId = POld.ParentId;
commit transaction;

-- Display the forest.
with IndentedProjects ( Id, ProjectId, Value, ParentId, Level, Path ) as
  ( -- Start with the top level rows.
  select Id, ProjectId, Value, ParentId, 0, Convert( VarChar(1024), Right( '000' + Convert( VarChar(4), Id ), 4 ) )
    from @Projects
    where ParentId =0
  union all
  -- Add the children one level at a time.
  select P.Id, P.ProjectId, P.Value, P.ParentId, IP.Level + 1, Convert( VarChar(1024), IP.Path + '<' + Right( '000' + Convert( VarChar(4), P.Id ), 4 ) )
    from IndentedProjects as IP inner join
      @Projects as P on P.ParentId = IP.Id
  )
  select Space( Level * 2 ) + Value as [IndentedValue], Id, ProjectId, Value, ParentId, Level, Path
    from IndentedProjects
    order by Path;

How to find position from recursive hierarchy, Parent-Child Relationship mysql

I show your problem related to @RolandoMySQLDBA answer and I done small change, parentid to parent_id into memberinfo table.
My it helps you :

DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;
    DECLARE cid INT;
    DECLARE label VARCHAR(24);


    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    SET cid = 0;
    SET label = '';
    WHILE ch > 0 DO
    SET cid = ch;
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT * FROM pctable WHERE id = ch) A;

    SELECT pos INTO label FROM
        (

SELECT x.id, x.parent_id, x.pos
FROM 
(
SELECT t.id, t.parent_id, @rownum := @rownum + 1 AS pos
FROM pctable t
JOIN (SELECT @rownum := 0) r 
where t.parent_id = ch order by id
) x where x.id = cid

    ) P;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,label);
            SET cm = '-';
    else
            SET rv = CONCAT(rv,cm,label);
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

And call here like :

select id,GetAncestry(id) from memberinfo

and answer gets like :

id position
1      1
2      1-1
3      1-1-1
4      1-1-2
5      2
6      1-1-1-1

Achieve Hierarchy, Parent/Child Relationship in an Effective and Easy Way