Comment by bfung | Hacker News Reader

Comment by bfung | Hacker News Reader

Parent Full thread

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?

No, 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

Yep, 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.

Pagination will also be complicated if not spaghetti code.

Reply on news.ycombinator.com