3 ms·

This was enlightening. I've often wondered why ORMs exist, and it turns out a bunch of devs don't understand tabular data. I was going to say they don't underst

This was enlightening. I've often wondered why ORMs exist, and it turns out a bunch of devs don't understand tabular data. I was going to say they don't understand SQL, but apparently it goes deeper than that. Like, left joins have been around since SQL-92, so the query not returning a result when there is no director is a skill issue. Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format.

deleted 1y ago
[deleted]
kragen 1y ago
The author of this blog post writes in https://www.scattered-thoughts.net/writing/reflections-on-a-decade-of-coding/ https://www.scattered-thoughts.net/writing/reflections-on-a-...: > - In a small team (averaging ~3 people, I think?) in a little under a year, we built a SQL query planner that passed the >6 million tests in the sqlite logic test suite. > - I figured out how to decorrelate arbitrary SQL subqueries (I wasn't the first person to figure this out, but I got there independently). > - I demonstrated a mode of consistency failure in various popular streaming systems that I believe was not widely understood at the time. I think it is likely that the reason you disagree with him is not that he "don't understand tabular data" and has "a skill issue". Unless you're secretly Michael Stonebraker? You write: > Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format. You may not have noticed this, but the post you are commenting on explains how to run a JSON query and have the DB return your results in a hierarchical format.
- mcdonje 1y ago
  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.
  - kragen 1y ago
    I don't think his final query is more complex than necessary? Maybe there's a better approach that I'm not seeing.
    - mcdonje 1y ago
      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. https://www.postgresql.org/docs/current/functions-json.html https://www.postgresql.org/docs/current/functions-json.html I don't have time to write something out right now, but there are definitely options.
      - kragen 1y ago
        I'm not sure there's a better option in Postgres, and I'm not convinced you understand his main argument. How would you restate it?
        mcdonje 1y ago
        You're not sure there's a better option than JSONifying a bunch of correlated subqueries individually?
        kragen 1y ago
        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.
        mcdonje 1y ago
        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.

deleted 1y ago

[deleted]

kragen 1y ago

The author of this blog post writes in https://www.scattered-thoughts.net/writing/reflections-on-a-decade-of-coding/ https://www.scattered-thoughts.net/writing/reflections-on-a-...: > - In a small team (averaging ~3 people, I think?) in a little under a year, we built a SQL query planner that passed the >6 million tests in the sqlite logic test suite. > - I figured out how to decorrelate arbitrary SQL subqueries (I wasn't the first person to figure this out, but I got there independently). > - I demonstrated a mode of consistency failure in various popular streaming systems that I believe was not widely understood at the time. I think it is likely that the reason you disagree with him is not that he "don't understand tabular data" and has "a skill issue". Unless you're secretly Michael Stonebraker? You write: > Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format. You may not have noticed this, but the post you are commenting on explains how to run a JSON query and have the DB return your results in a hierarchical format.

mcdonje 1y ago
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.
- kragen 1y ago
  I don't think his final query is more complex than necessary? Maybe there's a better approach that I'm not seeing.
  - mcdonje 1y ago
    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. https://www.postgresql.org/docs/current/functions-json.html https://www.postgresql.org/docs/current/functions-json.html I don't have time to write something out right now, but there are definitely options.
    - kragen 1y ago
      I'm not sure there's a better option in Postgres, and I'm not convinced you understand his main argument. How would you restate it?
      - mcdonje 1y ago
        You're not sure there's a better option than JSONifying a bunch of correlated subqueries individually?
        kragen 1y ago
        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.
        mcdonje 1y ago
        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.