How to choose immediate children and ancestors in one request

Question

I work with a tree structure in MySQL that is represented using a nested set model.

I hope some of you sql experts can help me create a SELECT query.

I would like to be able to map a set of nodes using LIKE. For each associated node, I also need a comma list of the ancestors of this node and a comma list for the immediate children of this node.

I’m not sure where to start if this is possible even in one request. (I am currently doing this with a query inside a loop.) What I hope for is a set of results that might look something like this.

Starting with the string "qu" and querying the table "Body", I get ...

Node | Parent Nodes | Immediate Children Quads Leg, Lower Body, Muslces Vastus Lateralus, Vastus Medialis, Rectus Femoris Obliques Core, Trunk, Muscles Inner obliques, outer obliques 

Any suggestions on how to accomplish this without loops would be greatly appreciated.

Answer 1

As long as I agree with nickf that it's bad and dirty, it's still interesting, so here:

 SELECT base.left_id, base.ancestors, GROUP_CONCAT(children.left_id) children FROM ( SELECT base.left_id , GROUP_CONCAT(ancestors.left_id) ancestors FROM nested_set base LEFT JOIN nested_set ancestors ON base.left_id BETWEEN ancestors.left_id AND ancestors.right_id WHERE base.name LIKE '%criteria%' GROUP BY base.left_id ) base LEFT JOIN nested_set children ON children.left_id BETWEEN base.left_id AND base.right_id LEFT JOIN nested_set inbetween ON inbetween.left_id BETWEEN base.left_id AND base.right_id AND children.left_id BETWEEN inbetween.left_id AND inbetween.right_id WHERE inbetween.left_id IS NULL GROUP BY base.left_id 

Basically, the trick is to solve it in two stages: firstly, solve the ancestral problem and crush the ancestors into a list, then use this result to solve it for children.

Some of the ancestors are relatively simple, this is a subquery in the from clause from my solution. Children are a little more complicated. It works by taking all descendants, and then requires that no nodes exist between the node base and descendants, which basically limits descendants to only children.

There are other options for this strategy to solve this problem - for example, you can first have children and allow ancestors to use the subquery in the SELECT list.

Answer 2

In one request? I would not bother. SQL would be awful and probably not even that efficient. Break each bit into smaller logical queries: first find all the relevant nodes, and then for each of them - additional information that you need.

Answer 3

I am not 100% sure what you want, but if I understand correctly, you can achieve this using a normalized database schema and subqueries.

For example:

Nodes table Nodeparents table

In the table "nodes" all nodes will be stored, and "node_parents" will display the relations between different nodes.

Therefore, when you select LIKE for a specific node, you can capture all of your parents and children from node_parents.

You can get more information using joins or subqueries.

Answer 4

This question is much more complicated than I expected in your other post, but I do not agree with the answer of the first posters.

I am sure this is possible with a single request.

You need to use SUBQUERIES and choose. Have you ever watched a really good demo on the mySQL website about the Adjacency list model.

Since you can LIKE for "Node", you can use Sub queries for your SQL query to get all parents and parent. One of my queries, which I did something like this, was absolutely massive! But it worked.

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

This is just a small piece of code that shows how the closest children are found.

 SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth <= 1 ORDER BY node.lft