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 ->
id parent
2 1
3 1
4 2
5 2
10 5
6 10
7 10
I need this Type of output . I need help out in this regard .
id parent
2 1
3 1
4 2
5 2
10 5
6 10
7 10
8 6
9 6
11 7
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;
}
}
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).
