Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Sat, 27 Mar 2021 at 16:28, Joel Jacobson <joel(at)compiler(dot)org> wrote:

> Hi,
>
> The database Neo4j has a language called "Cypher" where one of the key
> selling points is they "don’t need join tables".
>
> Here is an example from
> https://neo4j.com/developer/cypher/guide-sql-to-cypher/
>
> SQL:
>
> SELECT DISTINCT c.company_name
> FROM customers AS c
> JOIN orders AS o ON c.customer_id = o.customer_id
> JOIN order_details AS od ON o.order_id = od.order_id
> JOIN products AS p ON od.product_id = p.product_id
> WHERE p.product_name = 'Chocolade';
>
> Neo4j's Cypher:
>
> MATCH (p:product
> {product_name:"Chocolade"})<-[:PRODUCT]-(:order)<-[:PURCHASED]-(c:customer)
> RETURN distinct c.company_name;
>
> Imagine if we could simply write the SQL query like this:
>
> SELECT DISTINCT od.order_id.customer_id.company_name
> FROM order_details AS od
> WHERE od.product_id.product_name = 'Chocolade';
>

I regularly do this type of thing via views. It's a bit confusing as writes
go to one set of tables while selects often go through the view with all
the details readily available.

I think I'd want these shortcuts to be well defined and obvious to someone
exploring via psql. I can also see uses where a foreign key might not be
available (left join rather than join).

I wonder if GENERATED ... VIRTUAL might be a way of defining this type of
added record.

ALTER TABLE order ADD customer record GENERATED JOIN customer USING
(customer_id) VIRTUAL;
ALTER TABLE order_detail ADD order record GENERATED JOIN order USING
(order_id) VIRTUAL;

SELECT order.customer.company_name FROM order_detail;

Of course, if they don't reference the GENERATED column then the join isn't
added to the query.

--
Rod Taylor