JPDev@programming.dev to Programmer Humor@programming.dev · 2 months agoJSON Query Languageprogramming.devimagemessage-square25fedilinkarrow-up129arrow-down11
arrow-up128arrow-down1imageJSON Query Languageprogramming.devJPDev@programming.dev to Programmer Humor@programming.dev · 2 months agomessage-square25fedilink
minus-squareVlyn@lemmy.ziplinkfedilinkarrow-up0·2 months agoIf you only join on indexed columns and filter it down to a reasonable number of results it’s easily fast enough. For true hierarchical structures there’s tricks. Like using an extra Path table, which consists of AncestorId, DescendentId and NumLevel. If you have this structure: A -> B -> C Then you have: A, A, 0 A, B, 1 A, C, 2 B, B, 0 B, C, 1 C, C, 0 That way you can easily find out all children below a node without any joins in simple queries.
minus-squareColonelThirtyTwo@pawb.sociallinkfedilinkarrow-up1·2 months agoThe fact that you’d need to keep this structure in SQL and make sure it’s consistent and updated kinda proves my point. It’s also not really relevant to my example, which involves a single level parent-child relationship of completely different models (posts and tags).
If you only join on indexed columns and filter it down to a reasonable number of results it’s easily fast enough.
For true hierarchical structures there’s tricks. Like using an extra Path table, which consists of AncestorId, DescendentId and NumLevel.
If you have this structure:
A -> B -> C
Then you have:
A, A, 0
A, B, 1
A, C, 2
B, B, 0
B, C, 1
C, C, 0
That way you can easily find out all children below a node without any joins in simple queries.
The fact that you’d need to keep this structure in SQL and make sure it’s consistent and updated kinda proves my point.
It’s also not really relevant to my example, which involves a single level parent-child relationship of completely different models (posts and tags).