[nborwankar]

This is related to a classic relational database problem called “bill of materials problem” - suggest you Google that. Also look for book on Graphs and networks in SQL by Joe Celko where he goes into modeling graphs in SQL tables. Naive formulations will lead to deeply nested joins which balloon in computational cost.

[moomoo11]

Thanks for the rec. Yeah that’s the issues I’m running into. What makes it worse is that users can define their own nesting so building the dynamic queries in application code is becoming really difficult. Add on the problem of needing to comply with a rigid external API for those consumers.

I’ve been setting up some test data in graph db and I feel like I can toss out my 1400 loc total to build the dynamic query and just have the client pass in the json of what they want.

Current new implementation is about 200 loc.

Personally I don’t mind if sql can do it or not. I care about being able to test easily, and detect and fix bugs without needing to bring out a whiteboard. Saving 1200 loc is already such a good feeling.