4 ms·

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/transfo

by jitl 4y ago

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 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 examples lack a bit of the "relation" part; we could just as easily be compiling a DSL to a chain of `array.filter`, `array.reduce`, `array.map` calls. Joins are what makes relational modeling interesting! I would love to see Datalog/SPARQL-style implicit joins to make graph traversals like "which users have edited documents I own?" less verbose.

maximilianroos 4y ago
Great point, we'll add that. I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.
- maximilianroos 4y ago
  Added: https://github.com/prql/prql/pull/697 https://github.com/prql/prql/pull/697
- igorkraw 4y ago
  I looked at the book after this and have to say, I'd heavily recommend spending the next dew months just improving joins (and complex joins especially). Like GP says, relational modelling is the interesting bit about SQL and I don't feel exaggerative in saying the only reason I use SQL are joins, and so the only reason I'd introduce the complexity of your project into my stack would be if it makes handling joins, views and other aspects of relational modeling and slicing nicer - one example could be many to many relationships, or the gradient between graph/document based and normalised table based modeling
  - switchbak 4y ago
    I second this. However, it's important that we don't make it so easy that we hide the cost of the join itself.
- Sankozi 4y ago
  The biggest failure of SQL joins is not using declared foreign keys. For example it should be something like "JOIN USING fk_invoice_customer c" instead of repeating each time the relationship between invoice and customer entries already defined in foreign key
  - neamar 4y ago
    Most engines support NATURAL JOIN, which isn't perfect and has drawbacks, but allows for shorter joins.
  - deleted 4y ago
    [deleted]
- riezebos 4y ago
  I always found it surprising that joining on foreign key is not possible in SQL. I'm no expert, but looking at PRQL this feels like it should fit in quite well with the philosophy.
psychoslave 4y ago
I always found that side:left/right should also be expressible as rapport:antecedent/consequent as in propositional logic, rather than limiting these relationships to the geometric representation of Venn diagram. And maybe a shorter alternative might be tie:arm/leg.
- aerzen 4y ago
  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.
  - psychoslave 4y ago
    I didn’t mean to make a joke here, what was the funny part? Reading it again, maybe the tie:arm/leg sounded too much of a BDSM stuff? Not my initial idea though: I was just looking at short words that might hold the analogy need, from "relationship" you easily come to "tie", and then "arm/leg" for "anterior/posterior" seems pretty straight forward and analogous to "antecedent/(consequent|postcedent|succedent)".
    - psychoslave 4y ago
      I transformed this informal suggestion into an issue, see https://github.com/prql/prql/issues/718 https://github.com/prql/prql/issues/718
  - throwaway236324 4y ago
    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
deleted 4y ago
[deleted]
asavinov 4y ago
> Joins are what makes relational modeling interesting! 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_Links_or_Relational_Join_Considered_Harmful https://www.researchgate.net/publication/301764816_Joins_vs_... [2] https://github.com/asavinov/prosto 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 https://prosto.readthedocs.io/en/latest/text/column-sql.html

by jitl 4y ago

maximilianroos 4y ago

Great point, we'll add that. I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.

maximilianroos 4y ago
Added: https://github.com/prql/prql/pull/697 https://github.com/prql/prql/pull/697
igorkraw 4y ago
I looked at the book after this and have to say, I'd heavily recommend spending the next dew months just improving joins (and complex joins especially). Like GP says, relational modelling is the interesting bit about SQL and I don't feel exaggerative in saying the only reason I use SQL are joins, and so the only reason I'd introduce the complexity of your project into my stack would be if it makes handling joins, views and other aspects of relational modeling and slicing nicer - one example could be many to many relationships, or the gradient between graph/document based and normalised table based modeling
- switchbak 4y ago
  I second this. However, it's important that we don't make it so easy that we hide the cost of the join itself.
Sankozi 4y ago
The biggest failure of SQL joins is not using declared foreign keys. For example it should be something like "JOIN USING fk_invoice_customer c" instead of repeating each time the relationship between invoice and customer entries already defined in foreign key
- neamar 4y ago
  Most engines support NATURAL JOIN, which isn't perfect and has drawbacks, but allows for shorter joins.
- deleted 4y ago
  [deleted]
riezebos 4y ago
I always found it surprising that joining on foreign key is not possible in SQL. I'm no expert, but looking at PRQL this feels like it should fit in quite well with the philosophy.

psychoslave 4y ago

I always found that side:left/right should also be expressible as rapport:antecedent/consequent as in propositional logic, rather than limiting these relationships to the geometric representation of Venn diagram. And maybe a shorter alternative might be tie:arm/leg.

aerzen 4y ago
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.
- psychoslave 4y ago
  I didn’t mean to make a joke here, what was the funny part? Reading it again, maybe the tie:arm/leg sounded too much of a BDSM stuff? Not my initial idea though: I was just looking at short words that might hold the analogy need, from "relationship" you easily come to "tie", and then "arm/leg" for "anterior/posterior" seems pretty straight forward and analogous to "antecedent/(consequent|postcedent|succedent)".
  - psychoslave 4y ago
    I transformed this informal suggestion into an issue, see https://github.com/prql/prql/issues/718 https://github.com/prql/prql/issues/718
- throwaway236324 4y ago
  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

deleted 4y ago

[deleted]

asavinov 4y ago

> Joins are what makes relational modeling interesting! 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_Links_or_Relational_Join_Considered_Harmful https://www.researchgate.net/publication/301764816_Joins_vs_... [2] https://github.com/asavinov/prosto 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 https://prosto.readthedocs.io/en/latest/text/column-sql.html