4 ms·
"one way to get tree-structured data from a relational database with a SQL query is to write a recursive CTE (Common Table Expressions), which are just as fun a
by g8oz 3y ago
"one way to get tree-structured data from a relational database with a SQL query is to write a recursive CTE (Common Table Expressions), which are just as fun as they sound."
I promise you that CTEs, even recursive ones, are not scary and that once you get the hang of them are actually fun.
- forgetfulness 3y agoI don't find CTEs particularly fun, copying and pasting the whole tower of CTEs into another SQL window to debug the one I'm interested in is decidedly not a form of entertainment I pursue.
- barrkel 3y agoYou only need one recursive CTE typically and you can encode it into a view that you join against. It just sucks for performance when you have millions of nodes.
- fs_tab 3y agoYes, CTEs are fine. The author could have used one to generate a view containing formatted names instead of baking this information into the table.
- barrkel 3y agoI found using recursive CTEs incredibly slow for assembling tree data from a normalized representation. It would make getting the results of a query at least d times slower for assembling the path of nodes d deep in the hierarchy. The upside was cheap tree edit operations but those were a lot less frequent than reads.