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

On Tue, Mar 30, 2021, at 16:25, Rod Taylor wrote:
> On Sat, 27 Mar 2021 at 16:28, Joel Jacobson <joel(at)compiler(dot)org> wrote:
>> __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.

Interesting idea, but not sure I like it, since you would need twice as many columns,
and you would still need the foreign keys, right?

/Joel