SQL node path reconstruction

Question

I have a table which contains data about which node has been visited. It is possible that a node can be visited several times. For this I have another table which contains data of the visited node, node visited before and the node visited after. I would now like to reconstruct the path in order of visitation using MySQL. I can't seem to figure out how to make a query for this, so I'm asking here for help.

Example

Let's say someone visited these nodes in this order:

4->5->6->7->4->6->10->12->7->15

The tables would look like this:

Visits

+---------+-------------------------------+----------+------------+
| id      | user                          | node     | view_count |
+---------+-------------------------------+----------+------------+
| 1       | l3lie1frl77j135b3fehbjrli5    | 4        | 2          |
+---------+-------------------------------+----------+------------+
| 2       | l3lie1frl77j135b3fehbjrli5    | 5        | 1          |
+---------+-------------------------------+----------+------------+
| 3       | l3lie1frl77j135b3fehbjrli5    | 6        | 2          |
+---------+-------------------------------+----------+------------+
| 4       | l3lie1frl77j135b3fehbjrli5    | 7        | 2          |
+---------+-------------------------------+----------+------------+
| 5       | l3lie1frl77j135b3fehbjrli5    | 10       | 1          |
+---------+-------------------------------+----------+------------+
| 6       | l3lie1frl77j135b3fehbjrli5    | 12       | 1          |
+---------+-------------------------------+----------+------------+
| 7       | l3lie1frl77j135b3fehbjrli5    | 15       | 1          |
+---------+-------------------------------+----------+------------+

Revisits

+---------+-------------------------------+-------+----------------+-----------------+
| id      | user                          | node  | after_visiting | before_visiting |
+---------+-------------------------------+-------+----------------+-----------------+
| 1       | l3lie1frl77j135b3fehbjrli5    | 4     |       7        |        6        |
+---------+-------------------------------+-------+----------------+-----------------+
| 2       | l3lie1frl77j135b3fehbjrli5    | 6     |       4        |       10        |
+---------+-------------------------------+-------+----------------+-----------------+
| 3       | l3lie1frl77j135b3fehbjrli5    | 7     |      12        |       15        |
+---------+-------------------------------+-------+----------------+-----------------+

I would like to construct a query that would return the path in the form of a string or a list of nodes like this:

4,5,6,7,4,6,10,12,7,15

or

+---------+--------+
| index   | node   |
+---------+--------+
|    1    |   4    |
+---------+--------+
|    2    |   5    |
+---------+--------+
|    3    |   6    |
+---------+--------+
|    4    |   7    |
+---------+--------+
|    5    |   4    |
+---------+--------+
|    6    |   6    |
+---------+--------+
|    7    |   10   |
+---------+--------+
|    8    |   12   |
+---------+--------+
|    9    |   7    |
+---------+--------+
|    10   |   15   |
+---------+--------+

Any help will be much appreciated.

Answer 1

change your design to have 1 table visits:

+----+------+------+
| id | user | node |
+----+------+------+
|  1 | xx   |    4 |
|  2 | xx   |    5 |
|  3 | xx   |    6 |
|  4 | xx   |    7 |
|  5 | xx   |    4 |
|  6 | xx   |    6 |
|  7 | xx   |   10 |
|  8 | xx   |   12 |
|  9 | xx   |    7 |
| 10 | xx   |   15 |
+----+------+------+

---

you can then select view_count like this:

select node, count(*) view_count
from visits
where user = :user
group by node

and path like this:

select group_concat(node order by id separator ',') path
from visits
where name = :name