I think like 85% of the pain points could be solved by (a) allowing statements to start with a `from` clause, (b) some kind of shorthand for field sets, and (c) having some kind of syntax for returning some things as nested rows/objects. Without thinking deeply at all:
from users u join purchases p on p.user_id = u.id
select u{defaults}, u[p{defaults} as purchases]
limit 100 [purchases: 10]
returning rows that make `purchases` into a row-nested collection of up to 10 items instead of creating that many more overall rows and repeating the same user info multiple times.
from users u join purchases p on p.user_id = u.id select u{defaults}, u[p{defaults} as purchases] limit 100 [purchases: 10]
returning rows that make `purchases` into a row-nested collection of up to 10 items instead of creating that many more overall rows and repeating the same user info multiple times.