161–166 of 166 posts
I find SQL harder to work with because I'm used to reading and writing functions - functions that have explicit parameters with (hopefully) explicit types, and explicit return types. So I was hoping PRQL might have some sugar like that. It'd be nicer for my brain if I could treat any select like a function that transforms a set. Instead, even with PRQL, I'm stuck examining source tables and fully understanding them b…
Is this like what ORM for?
Earlier quoted context omitted.
I'm not sure if this is a joke, be we actually had a serious an idea to replace side:left/right with nulls_left:true and nulls_right:true This part of the join operation should be an after thought - just a flag after the central argument of the transform which should be the condition you join over.
Have you considered using "optional"? I feel that this would be a more natural syntax for joins. It could default to "left join", which is probably more frequently used than a right join. from employees join optional positions [id==employee_id] --> LEFT JOIN from employees join positions [id==employee_id] --> JOIN Then you'd use "optional right" or something similar for the "right join" case
Thank you!
Earlier quoted context omitted.
When I've needed them I've needed them for multiple statements, once is not enough. Currently have to use plpgsql for this, which is half awesome, half abomination. :-D A single simple language sounds easier to learn.
Put a comma between them, postgres has been able to do multiple CTEs in a single query for quite some time: https://stackoverflow.com/questions/35248217/multiple-cte-in... Or did you mean like using the same CTE across multiple queries? Views / materialized views are good for that.
Need to delete from multiple tables with foreign keys back to a single primary table. This before deleting from the primary table, due to consistency.
We often get a "list" of pks, then use it in multiple "delete key in" statements. A kludge, but these are for one-off tests on a dev database.
Earlier quoted context omitted.
When I've needed them I've needed them for multiple statements, once is not enough. Currently have to use plpgsql for this, which is half awesome, half abomination. :-D A single simple language sounds easier to learn.
I'm not totally sure I follow, as you can re-reference/manipulate the subquery as much as needed. Is it for some kind of dynamic programming like finding a column containing a certain value SELECT cols from table where like '%foobar%'" which would need to dynamically insert values into the query select col1 from table where col1 like '%foobar%' union select col2 from table where col2 like '%foobar%' union ... This ty…
For those interested in database query languages, it is worth knowing about Datalog, the query language behind Datomic, XTDB and Datahike: http://www.learndatalogtoday.org/ E.g. a parameterised aggregate query that retrieves the name and average rating of a film starring cast members whose names match the input names: [:find ?name (avg ?rating) :in $ [?name ...] [[?title ?rating]] :where [?p :person/name ?name] [?m :…
This is the Datomic/Clojure dialect of Datalog. I had an easier time learning a stand-alone datalog variant. I think compiling Datalog to SQL is an interesting idea. I wrote a toy Datalog -> SQLite compiler: https://percival.jake.tl/ Other Datalog -> SQL compilers I know of: - Originally from Mozilla, now independent: https://github.com/qpdb/mentat - From Google: https://logica.dev/
I'm surprised that none of the examples on Github or the website deals with join. I eventually found some in the "book" here: https://prql-lang.org/book/transforms/join.html from employees join side:left positions [id==employee_id] turns into SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON id = employee_id I would love to see joins worked into the main learning examples. Without join, the exampl…
It is the central part of RM which is difficult to model using other methods and which requires high expertise in non-trivial use cases. One alternative to how multiple tables can be analyzed without joins is proposed in the concept-oriented model [1] which relies on two equal modeling constructs: sets (like RM) and functions. In particular, it is implemented in the Prosto data processing toolkit [2] and its Column-SQL language [3]. The idea is that links between tables are used instead of joins. A link is formally a function from one set to another set.
[1] Joins vs. Links or Relational Join Considered Harmful https://www.researchgate.net/publication/301764816_Joins_vs_...
[2] https://github.com/asavinov/prosto data processing toolkit radically changing how data is processed by heavily relying on functions and operations with functions - an alternative to map-reduce and join-groupby
[3] Column-SQL https://prosto.readthedocs.io/en/latest/text/column-sql.html