Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Simplifying Join Syntax

github.com

Post reply on HN
Print view View original

11–20 of 28 posts

Re: Simplifying Join Syntax

#11

Post by fjfaase »

I think it is important to realize that relational, object-oriented and structured data (such as XML and JSON) are all implementation of an often far more richer data model. Would it not be better to implement something for the richer data model, such that querying relational data models to produce JSON (for example) would become trivial?

A long time ago, I made some attempts about this: https://www.iwriteiam.nl/AoP_spec_stat.html Not so long ago, I worked a bit on a data oriented language with cursors, compounds and components: https://github.com/FransFaase/DataLang.

Re: Simplifying Join Syntax

#12

Post by funcDropShadow »

The presented idea shortens the given examples, but is not composable. What happens if you have 1:N instead of 1:1 relation? Or even a N:M relation. Where do you specify whether you want an innner / outer / left / right join? This proposal works for some simple queries but fails to capture the generality of the relational model.

So, from a language development point of view, one has to ask: Is this special case worth the extra syntactical sugar? It has the downside, that when a query evolves and falls out of the special case you have to reformulate the syntactical sugar yourself. This creates friction, and it is still necessary to understand the relational model.

Other commenters mentioned Neo4j as an example where similar ideas have been implemented. From my limited experience with Neo4j, I'd say it makes a lot of sense there, because graph queries will often fall into the sub class of queries, that can benefit from the syntactical sugar.

All in all, I would not call this a simplification. Syntactical sugar never is a simplification. It is an "easification". It makes certain examples easy and hides what is going on, without really abstracting it away.

Re: Simplifying Join Syntax

#14

Post by shiandow »

funcDropShadow wrote: post #12

The presented idea shortens the given examples, but is not composable. What happens if you have 1:N instead of 1:1 relation? Or even a N:M relation. Where do you specify whether you want an innner / outer / left / right join? This proposal works for some simple queries but fails to capture the generality of the relational model. So, from a language development point of view, one has to ask: Is this special case worth…

They explain 1:N relations in the article, so I won't repeat that here. A N:M relation will be presented the same way as in all other relational databases, it requires a separate table with rows containing both keys.

And if I'm honest I think this captures more of the relational model than SQL does, you might be confusing the two. This synctactic sugar is actually using the properties relations have, rather than SQL which allows you to compute all kinds of things whether they make sense or not.

Note that they talk about using foreign keys for this purpose. Really what this is doing is turning the relation formed by this column and the primary key into a function (see [1] for an article I wrote on the subject), which allows for some nicer syntax because functions are nicer than general relations. This means most of the problems you mention can be resolved through simply enforcing that constraint. In a sense this moves the problem, but it does mean you can't accidentally invalidate the query. And frankly having some syntactic sugar for foreign keys in SQL is a feature that's long overdue.

The main downside is the inability to do anything other than equijoins, and the inability to specify new relations on the fly. The latter is a bit of a problem, but not insurmountable. I can't figure out a reasonable way to do anything other than equijoins, but that might be for the best.

Also, ironically what I'm really missing is how to do an actual join, it's nice to explicitly specify functions but if you've got two foreign keys to the same table (functions with the same codomain) how do you calculate the join (pullback)?

[1]: https://pragmathics.nl/2023/10/24/putting-the-relational-bac...

Re: Simplifying Join Syntax

#16

Post by aidos »

david_p wrote: post #8

I love the idea of a simplified SQL syntax for joins. I have been working with graph databases for years now: these databases had to solve this problem from day one, because of the focus on relationships between entities. I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again: the Cypher query language. Neo4 and other industry players have spent years working o…

I struggled to find the actual language at the link you posted - is there an obvious example somewhere?

Given that graphql is often shortened to gql is feel this is going to get confusing!

Re: Simplifying Join Syntax

#18

Post by kirmerzlikin »

aidos wrote: post #16
david_p wrote: post #8

I love the idea of a simplified SQL syntax for joins. I have been working with graph databases for years now: these databases had to solve this problem from day one, because of the focus on relationships between entities. I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again: the Cypher query language. Neo4 and other industry players have spent years working o…

I struggled to find the actual language at the link you posted - is there an obvious example somewhere? Given that graphql is often shortened to gql is feel this is going to get confusing!

This paper [1] explains some basics

[1] https://arxiv.org/abs/2112.06217

Re: Simplifying Join Syntax

#19

Post by alexisread »

Didn't https://www.odata.org/ do this already? If the Odata server understands the table relations, you can navigate through table relations in the REST query. I found that most parts of the business prefer filtering more than joining, learning another DSL other than SQL was a real barrier, and that many BI tools only support a subset of Odata, so are pretty much useless.

I definitely prefer concatenative (monadic) syntax a la Linq though, as it allows better scoping of efficient joins without a planner - it allows you to duck-tape (allusion intended) together a platform service easily.

Re: Simplifying Join Syntax

#20

Post by twic »

david_p wrote: post #8

I love the idea of a simplified SQL syntax for joins. I have been working with graph databases for years now: these databases had to solve this problem from day one, because of the focus on relationships between entities. I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again: the Cypher query language. Neo4 and other industry players have spent years working o…

> I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again

Was it the first? Does it predate path expressions in Hibernate Query Language / Java Persistence Query Language?

Post reply on HN