Great post! I've worked on a few query generating systems of varying degrees of complexity, and SQL is pretty rough as a compilation target. I've spent more time than I'd have liked wrestling with queries as comparably ungodly as the beauty you have presented.
One trick that I always find helpful is to unroll the subqueries into a long sequence of CTEs. You can do a depth first traversal of whatever data structure produces your subqueries and build a list of CTEs as you go (and track of all the CTE names of course). The ordering 'just works'!
Slightly nicer SQL doesn't seem important at first for stuff like this, but it's actually a godsend for debugging. Every operation in the query gets a name that's accessible in the outermost scope, so you can slap a `select * from cochoice_abc123` on the end and see what's going on.
Figuring out what's going on in those subqueries was no doubt hellish at times.
> Arrows are slightly the wrong abstraction because they require an operation arr :: (a -> b) -> (a ~> b)—which requires you to be able to embed Haskell functions in your category, something which is almost never possible.
Yeah, this is such a shame. It would make arrows actually usable.
Great post! I've worked on a few query generating systems of varying degrees of complexity, and SQL is pretty rough as a compilation target. I've spent more time than I'd have liked wrestling with queries as comparably ungodly as the beauty you have presented.
One trick that I always find helpful is to unroll the subqueries into a long sequence of CTEs. You can do a depth first traversal of whatever data structure produces your subqueries and build a list of CTEs as you go (and track of all the CTE names of course). The ordering 'just works'!
Slightly nicer SQL doesn't seem important at first for stuff like this, but it's actually a godsend for debugging. Every operation in the query gets a name that's accessible in the outermost scope, so you can slap a `select * from cochoice_abc123` on the end and see what's going on.
Figuring out what's going on in those subqueries was no doubt hellish at times.
> Arrows are slightly the wrong abstraction because they require an operation arr :: (a -> b) -> (a ~> b)—which requires you to be able to embed Haskell functions in your category, something which is almost never possible.
Yeah, this is such a shame. It would make arrows actually usable.