CODEWITHSUNDEEP
mysqltree
Naftali
Naftali

Reputation: 146302

Is there any way for me to get a whole tree with one query while only knowing the parent id?

I am looking for a non-recursive solution to make a MySQL query to select all of the leaf nodes (children, grandchildren etc) of a node while only knowing who the direct child of the node is.

Currently I have the following tables:

Nodes:

  • id (INT)
  • data (VARCHAR)

Relationships:

  • parentId (INT)
  • childId (INT)
  • childNodeOrder (INT)

The way I have it currently I can only select the direct child nodes of a parent node (for this example let the parent Id = 1):

SELECT * FROM  Nodes n
JOIN Relationships r ON r.childId = n.id
WHERE r.parentId = 1
ORDER BY r.childNodeOrder;

Is there any way for me to change this database around easily to not use a recursive call (on my server side code) and to be able to get all of the descendant leaves of a parent?

I so far have looked at questions like this one which would seem like a radical change, and not very easy to switch over...

Upvotes: 1

Views: 877

Answers (1)

Marc Alff
Marc Alff

Reputation: 8395

See the NESTED SET data model, it probably can help here.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Edit: since more context is needed, here are the details.

A parent node will have a left and right attributes that cover a range [left, right].

All the children nodes will be included in that range, so that:

parent.left <= child.left <= child.right <= parent.right.

All leaf nodes have a range of 1, so that left + 1 = right only for leaves.

To get all the leaves from a parent, use a where clause similar to this:

WHERE (left + 1 = right) AND (left >= parent.left) AND (right <= parent.right)

Upvotes: 1

Related Questions