11 ms·

1. SQL has tables, not relations... 2. I may be misinterpreting you, but you seem to be talking about composition of data, while the rest of us are talking abo

1. SQL has tables, not relations... 2. I may be misinterpreting you, but you seem to be talking about composition of data, while the rest of us are talking about the composition of the language. But, hopefully I've misinterpreted you. Perhaps you could demonstrate how query builder composition is best replaced in pure SQL?

ndriscoll 2y ago
I'm not sure what distinction you're making besides allowing duplicate rows (which don't affect composition and you can remove with `distinct `). I'm also not sure how to answer your question. Obviously a query builder is just spitting out queries that you can just write yourself. In the best case, they're a thin wrapper to give different names to SQL (e.g. where vs filter, select vs map, join vs flatMap). Perhaps an example would be how frequently, ORMs encourage you to do a load-modify-save pattern that turns into multiple statements to execute. This is usually more code, more error-prone, and worse performing than just doing an UPDATE WHERE. If you need complex reporting with a dozen joins and several window functions, you'll also see how difficult it is to write similar code in an application. I'm not sure what you mean with composition of the language. The language consists of operators which you can chain together, and you can substitute expressions into each other to form larger expressions. E.g. you can join to a (select from where group by) expression in place of a table, and you can factor such things out into CTEs. What's not composable?
- randomdata 2y ago
  > I'm not sure what distinction you're making besides allowing duplicate rows Duplicate rows, NULLs, ordering, etc. But there is no distinction to be made here, just calling attention to your grievous error so you don't make it again. We don't want to look like a fool again next time. Viva la education! > I'm also not sure how to answer your question. You put a lot of effort into a rather detailed response not once, but twice. You obviously know how to answer the question at a technical level, but perhaps you don't understand the nuance of my question? What I mean is: Show us some example code that demonstrates query builder composition and the pure SQL that you would use to replace it.
  - ndriscoll 2y ago
    So to be clear, my choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables", but you can of course use these things in SQL expressions. So tables are relations (not in the mathematical sense, but in the sense that e.g. postgresql documentation uses), but not all relations are tables. In that sense, the things that compose are relations and their operations. I'm not sure what you have in mind either for query builders or their composition. Like I said, some builders are really just wrappers to rename SQL operations and have a method chaining syntax. Those are always going to compile to obvious, analogous sql (e.g. `Users.filter(_.id==id).map(_.name)` will compile to `select name from users where id=?`. For the most part I think these are fine but maybe redundant. Then there are ORMs that do a bunch of state tracking and might produce multiple statements from one expression. These are usually what people get opinionated about. What's an example of query builder composition that you think can't be written in SQL?
    - randomdata 2y ago
      > choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables" The T in CTE literally stands for table. Even if you're right about the others, how could someone not think of that one as being a table? Regardless, now they can! Isn't education a wonderful thing? > postgresql documentation uses In fairness, Postgres originally implemented QUEL, which is relational. It didn't move to SQL until quite late in its life. It often takes longer to update documentation than to write code, especially in open source projects where the contributors tend to much prefer writing code over updating documentation. > and have a method chaining syntax. And this is often how composition is implemented. Not strictly so, but a common way to do it. Consider: users = select("*").from("users") admin_users = users.where("role = 'admin'") recent_admin_users = admin_users.where("created > LAST_WEEK()") And now imagine having tens, maybe even hundreds, of slight variations on the same query in the same vein. Each used in different parts of the application, which is quite common in any kind of line of business application. I'll save your bandwidth and not spell them all out as this is just a contrived example anyway, and I'm sure your imagination can fill in the blanks. Of course, you could do the obvious and write out 100 completely separate almost identical SQL queries, but that's not exactly maintainable and it's not the 1940s anymore. You are going to quickly hate everything about your existence as soon as those queries need to change. This is the reason people turn to query builders. If you only had to ever write one or two queries there'd be no point, but that never happens within the domain where these tools are used. But perhaps there is a better way. This is where you would give us example code to show how you would replace that query builder code with a pure SQL solution.

ndriscoll 2y ago

I'm not sure what distinction you're making besides allowing duplicate rows (which don't affect composition and you can remove with `distinct `). I'm also not sure how to answer your question. Obviously a query builder is just spitting out queries that you can just write yourself. In the best case, they're a thin wrapper to give different names to SQL (e.g. where vs filter, select vs map, join vs flatMap). Perhaps an example would be how frequently, ORMs encourage you to do a load-modify-save pattern that turns into multiple statements to execute. This is usually more code, more error-prone, and worse performing than just doing an UPDATE WHERE. If you need complex reporting with a dozen joins and several window functions, you'll also see how difficult it is to write similar code in an application. I'm not sure what you mean with composition of the language. The language consists of operators which you can chain together, and you can substitute expressions into each other to form larger expressions. E.g. you can join to a (select from where group by) expression in place of a table, and you can factor such things out into CTEs. What's not composable?

randomdata 2y ago
> I'm not sure what distinction you're making besides allowing duplicate rows Duplicate rows, NULLs, ordering, etc. But there is no distinction to be made here, just calling attention to your grievous error so you don't make it again. We don't want to look like a fool again next time. Viva la education! > I'm also not sure how to answer your question. You put a lot of effort into a rather detailed response not once, but twice. You obviously know how to answer the question at a technical level, but perhaps you don't understand the nuance of my question? What I mean is: Show us some example code that demonstrates query builder composition and the pure SQL that you would use to replace it.
- ndriscoll 2y ago
  So to be clear, my choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables", but you can of course use these things in SQL expressions. So tables are relations (not in the mathematical sense, but in the sense that e.g. postgresql documentation uses), but not all relations are tables. In that sense, the things that compose are relations and their operations. I'm not sure what you have in mind either for query builders or their composition. Like I said, some builders are really just wrappers to rename SQL operations and have a method chaining syntax. Those are always going to compile to obvious, analogous sql (e.g. `Users.filter(_.id==id).map(_.name)` will compile to `select name from users where id=?`. For the most part I think these are fine but maybe redundant. Then there are ORMs that do a bunch of state tracking and might produce multiple statements from one expression. These are usually what people get opinionated about. What's an example of query builder composition that you think can't be written in SQL?
  - randomdata 2y ago
    > choice of the word relation was because typically people don't think of things like views and CTEs and subselects as "tables" The T in CTE literally stands for table. Even if you're right about the others, how could someone not think of that one as being a table? Regardless, now they can! Isn't education a wonderful thing? > postgresql documentation uses In fairness, Postgres originally implemented QUEL, which is relational. It didn't move to SQL until quite late in its life. It often takes longer to update documentation than to write code, especially in open source projects where the contributors tend to much prefer writing code over updating documentation. > and have a method chaining syntax. And this is often how composition is implemented. Not strictly so, but a common way to do it. Consider: users = select("*").from("users") admin_users = users.where("role = 'admin'") recent_admin_users = admin_users.where("created > LAST_WEEK()") And now imagine having tens, maybe even hundreds, of slight variations on the same query in the same vein. Each used in different parts of the application, which is quite common in any kind of line of business application. I'll save your bandwidth and not spell them all out as this is just a contrived example anyway, and I'm sure your imagination can fill in the blanks. Of course, you could do the obvious and write out 100 completely separate almost identical SQL queries, but that's not exactly maintainable and it's not the 1940s anymore. You are going to quickly hate everything about your existence as soon as those queries need to change. This is the reason people turn to query builders. If you only had to ever write one or two queries there'd be no point, but that never happens within the domain where these tools are used. But perhaps there is a better way. This is where you would give us example code to show how you would replace that query builder code with a pure SQL solution.