SQL node path reconstruction
I have a table that contains the data on which node was visited. It is possible that a node can be visited multiple times. For this I have another table that contains the data of the node visited, the node visited earlier and the node visited after. Now I would like to restore the path in the visited order using MySQL . I can't figure out how to make a request for this, so I'm asking for help here.
Example
Let's say someone visited these nodes in the following order:
4->5->6->7->4->6->10->12->7->15
The tables 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 |
+---------+-------------------------------+----------+------------+
will revise
+---------+-------------------------------+-------+----------------+-----------------+
| 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 build a query that will return a path as 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 would be much appreciated.
source to share
change your design to have 1 visit table :
+ ---- + ------ + ------ + | 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 | + ---- + ------ + ------ +
<h / "> you can select view_count like this:
select node, count(*) view_count
from visits
where user = :user
group by node
and the path is like this:
select group_concat(node order by id separator ',') path
from visits
where name = :name
source to share