3 ms·
For the query to retrieve the items back in order, logically it goes like: select * from (items) order by `prev` --`next` doesn't help -- also hope that
by bfung 9y ago
For the query to retrieve the items back in order, logically it goes like:
select * from (items) order by `prev` --`next` doesn't help
-- also hope that NULL is at the beginning
So it's no better than Approach 1. in the article, but is actually worse with the extraneous column in storage and head scratching for the next maintainer of the system.
Exercise for the reader: what happens when item id #3 is moved in between item id #1 and #2, how many update statements are needed?
- FLUX-YOU 9y agoPagination will also be complicated if not spaghetti code.
- dzamo_norton 9y agoNo, this query doesn't retrieve the items in the correct order in general. It only works for the particular data in GP because the row ordering coincides with the user's ordering there. Try ordering by `prev` on the table below, which is a different valid representation of "steal underpants" -> "???" -> "profit". id | text | prev | next 1 | "profit" | 2 | NULL 2 | "???" | 3 | 1 3 | "steal underpants" | NULL | 2
- bfung 9y agoYep, that's fine - highlights the complexity of implementing the ordering in a linked list. The fundamental thing is that both columns aren't needed, only one column is needed to determine the order.