My take was charitable because if he knows better, then he's making a bad faith argument, and I didn't want to accuse him of that.
I didn't read beyond the article. I only responded to what was in the post.
He didn't use capabilities SQL has had for decades, made a bad query, and used that to build his argument about how bad SQL is.
The ORM tables could've been written in one query that returns a tabular format of hierarchical data, which the front end could use.
What does that look like? It looks like the kind of underlying tabular format JSON & XML queries require. He didn't do that with his non-JSON examples.
Yes, the framework would need to turn it into an object. Yes, there is data duplication in some fields. While not ideal, it's still better than the ORM example because you're not splitting it up into multiple queries.
His JSON example undercuts his main argument. The DB is fully capable of returning the representation he wants. It's also more complex than necessary with all the JSONB_AGGs in the subqueries. Then he says you can see the duct tape.
So yeah, it reads like a front end person who never really learned modern SQL. Or maybe it's someone who knows better who is making a bad faith argument. Either way, it's just a bad take.
Well for one thing, he's using a bunch of correlated subqueries, which is a bad idea. He even links to an article about why it's a bad idea and then does it anyway. Generally easily avoidable.
For another, postgres has more JSON functions than just jsonb_agg.
That's right: I'm not sure there's a better option in Postgres than JSONifying a bunch of correlated subqueries individually. How would you do it?
It's kind of a red flag, though, that you didn't answer my question about how you would restate his main argument. It suggests to me that you're just here to flame instead of to have a productive discussion.
I already said I don't have time to rewrite the query right now, but I'd definitely get rid of the correlated subqueries, which should be obvious from my comments. I may or may not use an intermediate representation, like a CTE, and I'd probably make use of JSONB_OBJECT_AGG.
I kind of thought it was a red flag that you were accusing me of not understanding the post, which is about how SQL is bad at hierarchical data, while defending the bad SQL he used to defend his argument. But I didn't make a big deal out of it.
I know. I answered that in my second paragraph. They also asked how I'd approach writing the query after I already mentioned the issues and sent them the postgres doc page.
You would restate his main argument as "I kind of thought it was a red flag that you were accusing me of not understanding the post, which is about how SQL is bad at hierarchical data, while defending the bad SQL he used to defend his argument. But I didn't make a big deal out of it."? That doesn't make any sense.
I think you don't understand what the post is about, you don't understand what the query is trying to achieve, you don't know a better way to do it, and you're just engaging in ego defense instead of contributing anything.
Seems to me, from the bit you quoted, that their answer would be something along the lines of: "I would restate the point of the post as talking about how SQL is bad at hierarchical data, while using bad SQL to defend this argument." What they actually wrote is so close to this that your claim not to be able to see that feels a tad disingenious.
I didn't read beyond the article. I only responded to what was in the post.
He didn't use capabilities SQL has had for decades, made a bad query, and used that to build his argument about how bad SQL is.
The ORM tables could've been written in one query that returns a tabular format of hierarchical data, which the front end could use.
What does that look like? It looks like the kind of underlying tabular format JSON & XML queries require. He didn't do that with his non-JSON examples.
Yes, the framework would need to turn it into an object. Yes, there is data duplication in some fields. While not ideal, it's still better than the ORM example because you're not splitting it up into multiple queries.
His JSON example undercuts his main argument. The DB is fully capable of returning the representation he wants. It's also more complex than necessary with all the JSONB_AGGs in the subqueries. Then he says you can see the duct tape.
So yeah, it reads like a front end person who never really learned modern SQL. Or maybe it's someone who knows better who is making a bad faith argument. Either way, it's just a bad take.