mysql hierarchical sql query having unordered parentid

Question

Hello Experts i have node table which has nodeid and parentid columns and i trying to get list of all nodes in such a way that parent nodes are listed before child nodes .

In this sqlfiddle example i am not getting node id 20,21 as result

Query i have used is

select  nodeid,parentid from    
(select * from node 
order by parentid, nodeid) channel_sorted,    
(select @pv := '0') initialisation  
where   find_in_set(parentid, @pv)  and   length(@pv := concat(@pv, ',', nodeid))

side note: When it comes to hierarchical storage, parent row always comes before child. Meaning, parent_id cannot exist without it existing itself. In your case, how did you end up with (19, 'categor19', 2296) ? You are facing this issue because of a poor design. You may still achieve this with PHP as shown here. — mighty_dev
– mighty_dev, Commented Mar 13, 2019 at 9:52
Yes @vivek_23, yes that's how the data is , i need to create parent-node before child node , but query isn't returning in order that i am expecting. Isn't there a way in mysql to fix this. — Vijay
– Vijay, Commented Mar 13, 2019 at 9:56

Abd Alrahman · Accepted Answer · 2019-03-13 10:15:50Z

0

Try the following query, I've tried it on sqlfiddle example you mentioned and it worked:

SET @position := 0;
select nodeid,parentid from
  (select nodeid,node.parentid from
     (select distinct parentid,(@position := @position + 1) s
         from node) as t inner join node on t.parentid=node.nodeid
     order by s) as f

union

select nodeid,parentid
from node
where nodeid not in (select parentid from node);

edited Mar 13, 2019 at 10:15

answered Mar 13, 2019 at 9:27

Abd Alrahman

637 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

mighty_dev Over a year ago

This doesn't work. It doesn't respect parent nodes are listed before child nodes.

mighty_dev Over a year ago

order by becomes irrelevant actually. See the fiddle here. It doesn't yield as expected.

Abd Alrahman Over a year ago

Try the edited version of the answer, I think this is what you wanted.

mighty_dev Over a year ago

No. It doesn't work. Check the order of 19 and 2296.

Vijay Over a year ago

Thanks @Abdo20293 , but as a result node 19 is coming after child nodes (20,21), whereas i am looking to get 1st parentnode then child node

|

Collectives™ on Stack Overflow

mysql hierarchical sql query having unordered parentid

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related