Join Extension Syntax

[mtoy-googly-moogly]

Where We Are ...

We currently have two join types, which suffice for almost every use of joining in real world data:

Fanning join, uses the primary key of the outer explore to pick many rows in the joined table

join: exploreName on exploreName.keyName. -- no rename
join: joinName is EXPLORE on joinName.keyName -- rename

Non-fanning join, uses the primary key of the joined explore to pick one row in the joined table

join: exploreName on myField -- no rename
join: joinName is EXPLORE on myField -- rename

We want to add the following join types (note this is not all the join types in SQL, just the ones useful for Malloy that we don't have)

• Fanning using a boolean expression
• Non-fanning using a boolean expression
• cross

Here is a discussion log of the proposals ... I am intentionally not using "many-to-one" and "one-to-many" because it is confusing, unclear and unteachable which of two entities is the left side and which is the right side of that phrase.

What we are trying

• The on keyword is used only for expression based joins
• The with keyword us used to indicate a primary key based join
• All joins must now be described as join_one: or join_many: because the fanning / non-fanning nature of a join is as important is "is this computation a dimension or a measure"

Non fanning joins, key or expression based ...

  explore: orders ... {
    join_one: user is users with user_id
    // or
    join_one: user is users on user_id = users.id

Fanning joins ...

  explore: users ... {
    join_many: orders on orders.order_id = users.id

Cross joins

 explore: white ... {
  join_cross: black

What we considered

Here are some examples of ways we might phrase these new joins in a Malloy-like way

REJECTED (mostly) First Suggestion

Declaration	Meaning
join: one stuff on someExpression	Non-fanning
join: one container is thing on container.id = id_of_container	Non-fanning, rename
join: many stuff on someExpression	Fanning
join: many contents is thing on id = contents.id_of_container	Fanning, rename
join: something is somethingElse	Cross rename
join: somethingElse	Cross

This is rejected because Malloy wants named things to be written type: newName is ...definition... and putting the join type before the name breaks that pattern.

The cross-join seems ok though, so we are probably going with "no ON means a cross join"

Type goes left of the colon ...

JOIN underscore

So we first try and push the join type to the left of the colon ...

Declaration	Meaning
join_one: stuff on someExpression	Non-fanning
join_one: container is thing on container.id = id_of_container	Non-fanning, rename
join_many: stuff on someExpression	Fanning
join_many: contents is thing on id = contents.id_of_container	Fanning, rename

JOIN space

... or resurrect the idea that the thing left of the colon can be more than one word ...

Declaration	Meaning
join one: stuff on someExpression	Non-fanning
join one: container is thing on container.id = id_of_container	Non-fanning, rename
join many: stuff on someExpression	Fanning
join many: contents is thing on id = contents.id_of_container	Fanning, rename

This would then make primary key: and group by: ... so that's a bigger discussion.

Expression joins using {}

Some syntax with joins as bags of properties ... something like:

Declaration	Meaning
join: container is { one: thing on: container.id = id_of_container }	Non-fanning, rename
( no way to write )	Non-fanning
join: contents is { many: thing on: id = contents.id_of_container }	Fanning, rename
( no way to write )	Fanning

Join type after IS

Declaration	Meaning
join: container is many thing on container.id = id_of_container	Non-fanning, rename
( no way to write )	Non-fanning
join: contents is one thing on id = contents.id_of_container	Fanning, rename
( no way to write )	Fanning

Type goes next to preposition

Declaration	Meaning
join: stuff one on someExpression	Non-fanning
join: container is thing one on container.id = id_of_container	Non-fanning, rename
join: stuff many on someExpression	Fanning
join: contents is thing many on id = contents.id_of_container	Fanning, rename

Type replaces on

Declaration	Meaning
join: stuff one someExpression	Non-fanning
join: container is thing one container.id = id_of_container	Non-fanning, rename
join: stuff many someExpression	Fanning
join: contents is thing many id = contents.id_of_container	Fanning, rename

Preposition implies type

Could pick different words than using and having, but something like ...

Declaration	Meaning
join: stuff using someExpression	Non-fanning
join: container is thing using container.id = id_of_container	Non-fanning, rename
join: stuff having someExpression	Fanning
join: contents is stuff having id = contents.id_of_container	Fanning, rename

[] or other non-word to indicate fanning

Declaration	Meaning
join: stuff on someExpression	Non-fanning
join: container is thing on container.id = id_of_container	Non-fanning, rename
join: stuff[] on someExpression	Fanning
join: contents is thing[] on id = contents.id_of_container	Fanning, rename