Comment by munk-a
4 years ago
I like the flow direction compared to standard SQL. SQL is supposed to read like a sentence I suppose but I have many times looked at it and really wanted things to be in a more logical order.
My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve readability. Additionally the aliasing could use work, I'd suggest reversing the assignment to be something closer to `use salary + payroll_tax as gross salary`. In terms of firm formatting, unless I'm missing something there isn't any reason to allow a filter statement before any aliases - so you can force two fixed positions for filter clauses which would make it always legal to reference aliases in filters.
On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler - those silly do nothing queries like "SELECT * FROM customer WHERE deleted='f'` are written once[1] in a moments time and never inspected again. The complex queries are what you want to optimize for.
1. If they even are - with ORMs available a lot of those dead simple queries just end up being done through an ORM.
> On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler
Hallelujah! But, to your footnote, this is a major reason why I despise ORMs. In my mind they make writing simple code slightly easier, but they make complicated SQL statements, especially when you get some weird issue under load and you're trying to debug why your DB is falling over, a ton more difficult and you spend so much time just battling your ORM.
On ORMs, the best use I see of them is for “transparent” queries that you don’t define.
Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter.
That’s 98% of what we do against the DB, and I’m all for having it basically invisible.
Then let’s just bypass the ORM altogether the minute we think about joining or grouping things together. There are libs in most language that help just sanitize queries, so it’s no difficult really.
With a middle ground like a micro ORM those transparent queries are barely visible anyway, literally a line or two lines of embededed sql strings. Especially micro ORMs that can handle dynamic filters. They're generally write once and only get looked at again when modifications are necessary, so they're not worth "optimizing" by adding the complexity of an ORM.
A common pattern seems to be over engineering these simple scenarios though. Someone decides that embedded sql is evil and needs to be extracted out of normal code, often to stored procs. Then these simple queries have enough friction that an ORM starts to look good, then you end up with an ORM generating simple queries dynamically in the same place that used to have a simple embedded string.
The fundamental problem with an ORM is that you're using a lower level language to compile to a higher level language. This is completely backwards. It's like having a framework in your assembly to generate Java code for you, so you don't have to bother with all that "weird" Java, and can just stay in your comfort zone.
Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out? Using an ORM I can get reusable parts of a query, while writing complex joins, I’m not sure why skipping that part is good?
10 replies →
I like the flow direction specifically for intellisense/autocomplete. I'm sure it would be easier to provide hints when the table name is known immediately.
This is exactly why LINQ uses a similar ordering.
I'd love for the next release of SQL to have optional alternative ordering of clauses
This is great feedback, and I agree with you re de-prioritizing terseness.
And I agree with you on both the assignments and `split` being a bit awkward. Kusto just uses `by`, WDYT?
Not the original commenter, but just using `by` makes total sense to me.
I've made this change [1]. Thank you!
[1] https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce...
6 replies →
Group by seems good enough and not changing terms where there isn’t good reason seems like a good goal. Is PRQL’s split the same as SQL’s group by?
Yes Kusto's `by` is excellent!
By actually sounds great to me to, yea. In this case it's short but it's extremely communicative!