Selecting linked list from MySQL

Question

I have a MySQL table looking like this:

> describe books;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| title         | varchar(255) | YES  |     | NULL    |                |
| after         | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Now, I want to select all rows from the table, but as sorted linked list. after is an id of row it should be after in query result. Of course I could do that after query in my script using this table, but I'd like to know if there is a way to get the rows sorted in such way.

Example of data in table:

(1, "title #1", 0)
(2, "title #2", 3)
(3, "title #3", 1)
(4, "title #4", 2)

Expected result:

(1, "title #1", 0)
(3, "title #3", 1)
(2, "title #2", 3)
(4, "title #4", 2)

Answer 1

If for all rows, your after values are smaller (or all larger) than your id values, the posted ORDER BY solution will do.

If not, you will need to traverse the after->id relation iteratively, and the original SQL does not support this at all. Modern SQLs do allow you to do this within a SELECT clause, e.g. Oracle has CONNECT BY, and recursive subqueries will do the trick as well. MySQL doesn't apparently have an equivalent.

An alternative is to do the iteration in the procedural language you can wrap around SQL queries, like in the article Skrol29 links to.

However, this iteration, regardless of how you perform it, will always be slow.

Another approach is to redesign the way in which you are representing this relationship in the database - see e.g. the nested set model.

Answer 2

you can't do this standard in mysql you could write something like

Select * from books b_root
   Left join books as b_child1 ON b_root.after = b_child1.id
      Left join books as b_child2 ON b_child1.after = b_child2.id
Where b_root.after = 0

You will get something like (1, "title #1", 0,3, "title #3", 1,2, "title #2", 3)

thats 3 deep or try linking it in a script or programming language

Example of a python script that solves this problem

books = [
{'id':1, 'title' :"title #1", 'after':0},
{'id':2, 'title' :"title #2", 'after':3},
{'id':3, 'title' :"title #3", 'after':1},
{'id':4, 'title' :"title #4", 'after':2},]

def recursive(book):
    global books

    for i in books:
        if book['id'] == i['after']:

            print i
            recursive(i)

for i in books:
        if i['after'] == 0:
            print i
            recursive(i)

Answer 3

SELECT * FROM my_table ORDER BY after;

does not work. The problem as described is like a queue of persons, where everyone just knows the single person directly ahead.

IMHO the best approach is to have a separate sorting table representing the queue:

> describe books_order;
+---------------+--------------+------+-----+---------+
| Field         | Type         | Null | Key | Default |
+---------------+--------------+------+-----+---------+
| order_no      | int(11)      | NO   |     |         |
| books_id      | int(11)      | NO   |     |         |
+---------------+--------------+------+-----+---------+

SELECT * FROM books WHERE id = (SELECT books_id FROM books_order ORDER BY order_no);

Answer 4

This is in fact a hierarchical query problem. Your need is in fact to sort the rows considering that each row has for parent the row with "child_row.after=parent_row.id".

If your hierarchy has only one level, it can be done with a simple query. If your hierarchy has a free number of level, then it is quite difficult the solve it since MySQL doesn't has hierarchical features.

But here is an article to solve the problem:

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

Answer 5

Having this table:

create table list(id int primary key not null, previous int, description varchar(10));

You can obtain elements by:

select l2.* from (select @r as _parent, @r := (select id from list where previous = _parent) as id, @n := @n + 1 as n from (select @r := 0, @n := 0) vars, list) k, list l2 where k.id = l2.id order by n;

Answer 6

This should give you the expected result.

SELECT * FROM books ORDER BY after;

Visit the following link for more details: Mysql Select Tutorial