3 ms·
Not OP, but I find the syntax to be arcane and bizzarely restrictive. Every query has to be SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY; for some reason the
by wolfgang42 10y ago
Not OP, but I find the syntax to be arcane and bizzarely restrictive. Every query has to be SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY; for some reason the table comes after its fields and the ORDER BY can't be expressed using field aliases defined at the start of the query.
Instead, I've been toying with the idea of a language where queries are expressed as a pipeline of operations on result sets, like this:
FROM employee
/* result set: all employees */
LEFT JOIN department ON employee.DepartmentID = department.DepartmentID
/* result set: all employees + their department */
ORDER BY employee.last
/* result set: all employees + their department, sorted */
WHERE department.name IN ('Sales', 'Engineering')
/* result set: Sales and Engineering employees + their department, sorted */
FIELDS employee.first, employee.last, department.name
/* result set: Sales and Engineering employees' first, last, and department names, sorted */
This is a SELECT, but you can for example turn it into an update just by adding a clause to the end:
UPDATE employee.salary = employee.salary * 1.05
This is a much more regular language; there's nothing enforcing you to write these operations in a certain order, and you can add or remove clauses as necessary. For complex queries, I find that I write in this style anyway, using a chain of WITH clauses as a pipeline with a final SELECT on the end to get the results.
- jrochkind1 10y agoSo it's just like SQL but with the order of statements being more flexible? Does not seem entirely unreasonable.
- wolfgang42 10y agoBasically yes, though I'd also make it so if you alias a field it's referred to by the alias from then on (e.g. FIELDS foo AS bar ORDER BY bar instead of SELECT foo AS bar ORDER BY foo). I'd probably be inclined to rename some of the keywords as well (SORT, FILTER, MAP, REDUCE perhaps). There are some query builder libraries that let you specify clauses in a different order, like "query.from(table).where(col, value).select([...fields])", but I have yet to find one that lets you specify the query as a series of transformations on tables. (I think the word I'm looking for is "isomorphic" but that doesn't seem right somehow.)
- stephenhuey 10y agoHave you ever seen Arel? https://github.com/rails/arel https://github.com/rails/arel
- libria 10y agoThis reads very much like LINQ query syntax: from e in employee join d in department on e.DepartmentID equals d.DepartmentID where new string[]{ 'Sales', 'Engineering' }.Contains(d.name) orderby e.last select e.first, e.last, d.name Forgive any errors, it's been a while.
- remotehack 10y ago> I've been toying with the idea of a language where queries are expressed as a pipeline of operations on result sets That is what the relational database engine does in the first place. In fact, many really smart people have figured out how to make the engine really good at it and SQL is the best for working with it.
- combatentropy 10y ago> for some reason the table comes after its fields I agree it would have been better to start with the FROM clause and have the fields listed near the end. > I write in this style anyway, > using a chain of WITH clauses That sounds like it could make it better. I will try that more often. > the ORDER BY can't be expressed using field aliases > defined at the start of the query In Postgres you can!