4 ms·
Is there a way to do this in SQL? I don’t think there’s a way to deal with low-level data structures like that within SQL, especially since SQL, as a declarativ
by willj 6y ago
Is there a way to do this in SQL? I don’t think there’s a way to deal with low-level data structures like that within SQL, especially since SQL, as a declarative language, is meant to hide these details. I think what makes this article interesting is that it is about how to do this in SQL.
- _pastel 6y agoSure. Add columns "previous" and "next"; both can also be foreign keys into this table. Querying is a bit less straightforward, though; you need a recursive query to traverse the pointers. (Or traverse in application logic, at the cost of a bunch of unnecessary round-trips.)
- n4r9 6y agoWriting a select statement to return the results in order sounds tricky [I think the top-level post mentions this]. Even doing it in the application code would give me pause for thought.
- _pastel 6y agoIt's a recursive query, but it's basically the simplest recursive query. It's not that bad. WITH recursive_traversal(depth, id) AS ( -- base case: the root has no previous SELECT 0, id FROM todo WHERE previous IS NULL AND user_id = $1 UNION ALL -- recursive case: traverse to next row SELECT depth + 1, todo.id FROM todo JOIN recursive_traversal r ON r.next = todo.id WHERE depth < $2 ) -- we only accumulated the ids, so join one more time to get the rest of the columns SELECT todo.* FROM todo JOIN recursive_traversal USING (id);
- n4r9 6y agoYou're right, it's not that bad! I'm going to study recursive queries, seems like they can be really helpful for some things.
- deleted 6y ago[deleted]