Hierarchical queries in MySQL

Question

I'm trying to find all the parents, grandparents, etc. of a particular field with any depth. For example, given the below structure, if I provide 5, the values returned should be 1, 2, 3 and 4.

| a  | b  |
-----------
| 1  | 2  |
| 2  | 3  |
| 3  | 4  |
| 4  | 5  |
| 3  | 6  |
| 4  | 7  |

How would I do this?

You have to change the schema for this. Read [@BillKarwin](http://stackoverflow.com/users/20860/bill-karwin)'s presentation titled [Models for Hierarchical Data with SQL and PHP](http://www.slideshare.net/billkarwin/models-for-hierarchical-data), on the different models and how to implement them. — Shef, Nov 12 '11 at 12:16
@Shef: What changes should i make in schema and how do i write the query. — , Nov 12 '11 at 14:50
@BillKarwin:In your ppt,you've mentioned that above logic can n't be done using mysql.Is it so? — , Nov 12 '11 at 15:07
Not in a single plain SQL query, no. Unless you use user-variable tricks like @Quassnoi's answer. — Bill Karwin, Nov 14 '11 at 07:51

Quassnoi · Accepted Answer · 2011-11-14T06:10:47.163

27

SELECT  @id :=
        (
        SELECT  senderid
        FROM    mytable
        WHERE   receiverid = @id
        ) AS person
FROM    (
        SELECT  @id := 5
        ) vars
STRAIGHT_JOIN
        mytable
WHERE   @id IS NOT NULL

edited Nov 14 '11 at 06:10

answered Nov 13 '11 at 13:13

Quassnoi

413,100
91
616
614

1

waw... can be dangerous if you use it too much, like in a big website? – Wiliam Jun 05 '12 at 12:51
2

@Wiliam: it's not upgrade-safe because `MySQL` does not clearly define session variable behaviour. However, it's the only way to deal with adjacency lists in a timely fashion in-query. – Quassnoi Jun 05 '12 at 12:53
1

Just for reference (as "dangerous" can mean about anything) can someone elaborate on what about this makes it dangerous? And what would cause it to be/not be dangerous? – Mike Jan 15 '15 at 19:36
4

@MichaelJMulligan: this query can stop working right any time, because it relies on certain behaviors (re-evaluation of `@id` for the `WHERE` condition on each row, single evaluation of `@id := 5` in the beginning of the query etc.) which are not specified in MySQL documentation and might change with any minor version upgrade. Even without version upgrades you can't be sure how those work. This whole query is basically a hack. – Quassnoi Jan 15 '15 at 19:40
Is there a better solution? I imagined dangerous as it could do something bad. Failure is one thing, but one of the commenters (@Wiliam) was specific about a "big website". So I figured it was something more than just "might stop working." Which is bad too, don't get me wrong.. – Mike Jan 15 '15 at 19:41
@MichaelJMulligan: I doubt it would actually destroy your data. I once managed to wrote a session variable query optimized so well that it would eat up lots of memory and eventually starve the session so that it would die, but this I think is about as dangerous as it can get. – Quassnoi Jan 15 '15 at 19:55
What is the query to find all decendants? I tried swap `senderid` and `receiverid` in the query but it didn't work. Thanks. – Shawn Jun 17 '16 at 19:58
@Shawn: this is a hack. Recent versions of MySQL do not run this at all. That said, if your query works one way, then just swapping the fields in the subquery should make it work another way. – Quassnoi Jun 17 '16 at 20:40
I get your query work only one way for me. I'm guessing it doesn't work the other way because one child only have one parent, but one parent can have many children? In that case, how do I find all decendants? Thanks. – Shawn Jun 17 '16 at 21:35
1

@Shawn: https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ (I'm the author). Please be aware it's an ugly hack which can and probably will stop working in future releases of MySQL. – Quassnoi Jun 17 '16 at 22:46

score -8 · Answer 2 · answered Jan 24 '14 at 08:58

The following answer is not MYSQL-only, but uses PHP. This answer can be useful for all those that end up on this page during their search (as I did) but are not limited to using MYSQL only.

If you have a database with a nested structure of unknown depth, you can print out the contents using a recursive loop:

function goDownALevel($parent){
     $children = $parent->getChildren(); //underlying SQL function
     if($children != null){
          foreach($children as $child){
                //Print the child content here
                goDownALevel($child);
          }
     }
}

This function can also be rewritten in any other language like Javascript.

Hierarchical queries in MySQL

2 Answers2

Linked

Related