MYSQL - Get all children from the parents

Question

I have stuck in getting all children's query where parent id greater than the customer id

table test

  id    name    parent
    1   test1   0
    2   test2   1
    3   test3   1
    4   test4   2
    5   test5   2
    6   test6   10
    7   test7   10
    8   test8   6
    9   test9   6
    10  test10  5
    11  test10  7

Currently I am using this recursive query but it shows children till the 10 parent but not able to give children of 6 and 7 and further

SELECT id , parent FROM (SELECT  id , parent from (SELECT * FROM test order by
parent , id) testdata_sorted, (SELECT @pv := '1') initialisation where 
find_in_set(parent , @pv) > 0 and @pv := concat(@pv, ',', id)  ORDER BY 
parent ASC) AS tt

Current Output is ->

I need this Type of output . I need help out in this regard .

Can you please provide your schema so we can see the various tables in question? — SupaMonkey, Aug 14 '18 at 13:26
If you need the records where parent id is greater than id, it shouldn't be showing the record with id=2 as it's parent is 1 which is smaller. (and many more can be found) — Cemal, Aug 14 '18 at 13:27
What version of MySQL (or MariaDB) do you use? This makes a difference because MySQL 8+ and MariaDB 10.2+ have recursive common table expressions. (Look those up.) — O. Jones, Aug 14 '18 at 14:08
@O.Jones HI buddy i am using 5.2 server not supports version 8 — Sunil Jaisinghani, Aug 16 '18 at 08:38
@Cemal but i have records in thousands and its breaks somewhere again and again so do u have any permanent solution — Sunil Jaisinghani, Aug 16 '18 at 08:43
Your desired output is not consistent with what you ask. Either you are asking the wrong question, or providing wrong desired output. You need to correct one of them. Only then a correct solution may be provided. — Cemal, Aug 16 '18 at 09:22
You're trying to do something for which MySQL before version 8 *is not designed.* You should expect it to be difficult to program and test. This may help you. https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query Your best bet: upgrade to a more recent version. — O. Jones, Aug 16 '18 at 10:31

score 0 · Answer 1 · edited Jan 27 '19 at 11:24

You are using a fragile way to simulate a recursive query. It specifically requires that a parent row has to be sorted before the child.

Your base rowset is using order by parent, id:

id  parent
----------------------
1   0
2   1         -- fine
3   1         -- fine 
4   2         -- fine
5   2         -- fine 
10  5         -- fine 
8   6         -- parent 6 comes later!
9   6         -- parent 6 comes later! 
11  7         -- parent 7 comes later!
6   10        -- fine
7   10        -- fine

You see that those are exactly the rows that are missing from your result.

There is no simple fix to this, as to order your rows on the fly to be able to be used in your recursive query, you need a recursive query. You may be able to enter your data in a way that fulfills that condition though. While I assume that the part where parent id greater than the customer id in your question is actually not a condition (as your expected output does not align with that): if you have such a condition that constraints parent and child, it could give you a possible order.

For alternative ways to model your data or write your query, see How to create a MySQL hierarchical recursive query. Actually, trincots answer includes a remark about the order requirement for your code.

Preferably, you would be using a version that supports recursive CTEs, because as long as you do not want to change your data model, every workaround for those has some limitations (e.g. row order or max depth).

A side note: order by in a subquery (specifically testdata_sorted) can be ignored by MySQL, and you may have to verify that it doesn't (which can depend on things like version, indexes or table sizes).

thanks for solution . as mysql version is 5.7 so Cte NOT supports there that why i stuck in this position . so do because default data is well aligned but after some time i have to change its parents childs data as per the need . I hope any one find solution for this — Sunil Jaisinghani, Aug 16 '18 at 08:46
It is not possible to write a single (non recursive) query for your data structure, I hope the answer made this clear for your type of query. You can a) use a different query type (with other limitation, e.g. max depth) or a different model (e.g.nested sets), see the link b) make sure your data is proper after updates (e.g. assign new ids to moved objects and all their descendants); complicates the (rare) update, but simplifies the (frequent) select c) use more than one query (e.g. a stored procedure inserting into a temptable or a loop in php) (this can/will also be used to fix the ids in b) — Solarflare, Aug 16 '18 at 09:17

Sunil Jaisinghani · Answer 2 · 2018-09-03T06:58:40.783

Thank You all for your response but from the current scenario i analyse that this problem cannot be solved by MY SQL due to version issue because in the end this recursive query breaks at some point.

So I have to made a recursive function in PHP using the same query which breaks at where Child id is greater than parent and call the same function again and again with that break id's and add data in same array. which gives my desired result.

function getallchilds($customer_parent_id){

$read = Mage::getSingleton('core/resource')->getConnection('core_read');
$downlineChilds =array();
$breakbleIds =array();

    $getallchilds = $read->fetchAll("SELECT id , parent FROM (SELECT  id , parent from (SELECT * FROM test order by
parent , id) testdata_sorted, (SELECT @pv := '".$customer_parent_id."') initialisation where 
find_in_set(parent , @pv) > 0 and @pv := concat(@pv, ',', id)  ORDER BY 
parent ASC) AS tt");

    foreach($getallchilds as $childs) {
          $downlineChilds[] =  array($customer_parent_id => $childs['id']);
          if ($childs['parent'] > $childs['id']) {
            $breakbleIds[] =  $childs['id']; 
          }
        }

    $checkbreakIDS = count($breakbleIds);
    if($checkbreakIDS > 0 ){
        foreach($breakbleIds as $breakbleId) {
            $childrens = getallchilds($breakbleId);
            if ($childrens){
                $downlineChilds = array_merge($downlineChilds,$childrens);
            }
        }
        return $downlineChilds;
    }
    else{
        return $downlineChilds;
    }

}

MYSQL - Get all children from the parents

2 Answers2