How to pull the last record from a table when one record refers to another in the same table?

Question

MySQL

Good afternoon.
There is a table with redirects:

+------+------+
| from | to   |
+------+------+
|    1 |    3 |
|    3 |    4 |
|    4 |    5 |
|    6 |    7 |
+------+------+

It stores page IDs. The problem is that it is not possible to pull out a record when the redirect itself is a redirect to another page (there can be as many as you like).
Can this be solved with mysql ?

Answer 1

Mysql doesn't know how to join recursively, so you can't just solve this problem.
There are three ways to go:
1. Determine the maximum number of redirects and join the table to itself this number of times. The solution is not very elegant, but the simplest.
2. Change the structure of the database and store the real page id for each line. This is probably the most correct solution, but it is relatively difficult to implement.
3. Process this logic with a script, making recursive queries. On the forehead, but it can be gluttonous in terms of resources.

Answer 2

you can google the phrases Adjacency list, Matherialized Path, Nested sets, Closure Table, remembering to substitute the word MySQL, you will find a bunch of descriptions and a bunch of ready-made implementations.
All of these are optimized options for storing trees in relational databases.