4 ms·

OP means combining two different queries or clauses into a bigger query/clause You cannot easily "modify" a sql statement. There are type-safe DSLs in certain

by platz 8y ago

OP means combining two different queries or clauses into a bigger query/clause You cannot easily "modify" a sql statement. There are type-safe DSLs in certain languages (if they have an expressive enough type system) that let you do this, but they are uncommon

1stranger 8y ago
They're not that uncommon. You can do this with SQLAlchemy in Python or jOOQ in Java.
vp8989 8y ago
You could use views. Although personally I dislike adding this layer of obfuscation. Generally devs are very underskilled in SQL (relative to it's importance) and ORMs hiding the SQL IMO is one of the reasons why. There is great value in having the entire SQL easily visible in your source code. If there is a performance issue with a specific query my app is running, I can very quickly pull it out and into a database IDE and see what's going on using the more detailed tools available (REPL, execution plan/statistics usage analysis etc). If the query is composed from a bunch of ORM function calls, I have to step through the program to first generate it. Reducing friction around debugging allows your devs to become more capable in fixing things or making them work better, and SQL is no exception to that.
- ako 8y ago
  Views are not a layer of obfuscation. They are a layer of abstraction...
karmakaze 8y ago
Thanks for the explanation. I've never found adding to an SQL query to be any effort. The only place I'd put any value into a DSL is that it doesn't miss including all the conditions for a join.
lugg 8y ago
Nested queries? Joins? Stored procedures? Views? This is literally everything SQL was made for. Modify a query? Literally just code. Like what do you people think SQL is? Select * from table?
- platz 8y ago
  We are talking about programmatically constructing a query at runtime by stictching together bits of sql, in a way that which bits of sql you use are actually dependent on the runtime state, and in a way that is type-safe. You could do something similar with either dynamic queries in a stored procedure, but without a DSL those dynamic queries are brittle, and the runtime composition using them is hard and error prone.
  - lugg 8y ago
    Right, how often do you actually need to do this? In my experience query building to this degree is usually a sign of over optimising for code reuse where it isn't really needed. The times it does make sense, it's usually a search problem that is better solved by something like elastic search. That isn't always feasible so what you end up with is a naive approach and just build the damn string. Or if it's needed you decompose it into the parts that are needed elsewhere table_headers() is usually all you need to stay DRY. It's not as pretty as an ORM, it shouldn't be used for every little query "because you might need it in the future". But the cases it is needed are rare, so does it really matter? Like you've got to adjust your frame of reference. Your database schema is static, it's not going to completely change out from under you at runtime, it's just a non problem.
    - flukus 8y ago
      >Right, how often do you actually need to do this? In my experience query building to this degree is usually a sign of over optimising for code reuse where it isn't really needed. I need to very often and it's rarely about code reuse. You requirements like "on the client list screen we want to be able to search by client id or client name and we want to be able to sort by last login time or name"? What about writing an API, that's 90% handling things like this. And then there's the other cases ORM's solve, like representing data as something more than a 2D table.
deleted 8y ago
[deleted]

by platz 8y ago

1stranger 8y ago

They're not that uncommon. You can do this with SQLAlchemy in Python or jOOQ in Java.

vp8989 8y ago

You could use views. Although personally I dislike adding this layer of obfuscation. Generally devs are very underskilled in SQL (relative to it's importance) and ORMs hiding the SQL IMO is one of the reasons why. There is great value in having the entire SQL easily visible in your source code. If there is a performance issue with a specific query my app is running, I can very quickly pull it out and into a database IDE and see what's going on using the more detailed tools available (REPL, execution plan/statistics usage analysis etc). If the query is composed from a bunch of ORM function calls, I have to step through the program to first generate it. Reducing friction around debugging allows your devs to become more capable in fixing things or making them work better, and SQL is no exception to that.

ako 8y ago
Views are not a layer of obfuscation. They are a layer of abstraction...

karmakaze 8y ago

Thanks for the explanation. I've never found adding to an SQL query to be any effort. The only place I'd put any value into a DSL is that it doesn't miss including all the conditions for a join.

lugg 8y ago

Nested queries? Joins? Stored procedures? Views? This is literally everything SQL was made for. Modify a query? Literally just code. Like what do you people think SQL is? Select * from table?

platz 8y ago
We are talking about programmatically constructing a query at runtime by stictching together bits of sql, in a way that which bits of sql you use are actually dependent on the runtime state, and in a way that is type-safe. You could do something similar with either dynamic queries in a stored procedure, but without a DSL those dynamic queries are brittle, and the runtime composition using them is hard and error prone.
- lugg 8y ago
  Right, how often do you actually need to do this? In my experience query building to this degree is usually a sign of over optimising for code reuse where it isn't really needed. The times it does make sense, it's usually a search problem that is better solved by something like elastic search. That isn't always feasible so what you end up with is a naive approach and just build the damn string. Or if it's needed you decompose it into the parts that are needed elsewhere table_headers() is usually all you need to stay DRY. It's not as pretty as an ORM, it shouldn't be used for every little query "because you might need it in the future". But the cases it is needed are rare, so does it really matter? Like you've got to adjust your frame of reference. Your database schema is static, it's not going to completely change out from under you at runtime, it's just a non problem.
  - flukus 8y ago
    >Right, how often do you actually need to do this? In my experience query building to this degree is usually a sign of over optimising for code reuse where it isn't really needed. I need to very often and it's rarely about code reuse. You requirements like "on the client list screen we want to be able to search by client id or client name and we want to be able to sort by last login time or name"? What about writing an API, that's 90% handling things like this. And then there's the other cases ORM's solve, like representing data as something more than a 2D table.

deleted 8y ago

[deleted]