On Sat, 27 Mar 2021 at 16:28, Joel Jacobson <j...@compiler.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
