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.
E.g.in postgres:
SELECT u.*, ( SELECT JSON_AGG(p) FROM ( SELECT p.* FROM purchases p WHERE p.user_id = u.id LIMIT 10 ) p ) AS purchases FROM users u JOIN purchases p ON p.user_id = u.id GROUP BY u.id LIMIT 100;
Starting queries with from - what is the benefit of that beside personal preference?
And you can simply use a cte or a view instead of a field set.
The "start queries with from" is because you get easier and better auto complete and error detection for queries when the tables being used are declared. When you start typing "select some_field" there's basically no way to have good auto complete or correct until the from clause. IMO there are more philosophical reasons too.
Views and CTEs have their place, but also have their shortcomings. That I don't think this solves. But I also don't think they really fill the gap I'm thinking about either.
Typical pattern us you find a list of ids and pull on them with a particular view that you want them in.