4 ms·
One thing that would be nice is if SQL provided first class support for sub records. So instead of "SELECT name, (SELECT GROUP_CONCAT(CONCAT_WS(',', post_id, p
by mjrpes 12y ago
One thing that would be nice is if SQL provided first class support for sub records.
So instead of "SELECT name, (SELECT GROUP_CONCAT(CONCAT_WS(',', post_id, post) SEPARATOR ';') FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1",
you could do "SELECT name, (SELECT post_id, post FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1".
and the query result would be { name : 'Todd', posts : [ { post_id : 1, post : 'My Comment' } ] }.
Obviously this is a simple example and could have been rewritten as a query on the posts table, inner joined on the user table, and duplicating the user's name in the result. But it becomes much nicer to have as queries get more complex.
A query that supports sub records would gives you flexibility to structure data like a JSON object and simplify the server end of REST apis.
- qooleot 12y agoIf you're just trying to get JSON out (for a simple query to REST api, or in a node.js environment), have you considered converting the record/recordset to json in the subquery? The json functions in 9.3+ are pretty handy for that sort of thing. Andrew (core developer who wrote most of that functionality) and I decided to keep the api pretty lightweight, as its easy to also add your own functions to suit your needs.
- mjrpes 12y agoJSON functions do look pretty helpful and go a long way, even if not first class. As you can tell from syntax I'm stuck in the MySQL/MariaDB world, so not up with everything in PostgreSQL. This and the features in the slideshare are an eye opener to me. Thanks!
- zapov 12y agoPostgres/Oracle supports such constructs. And Revenj has been using it for years: https://github.com/ngs-doo/revenj https://github.com/ngs-doo/revenj And yes, Revenj now comes with an offline compiler ;)
- ddorian43 12y agohow can you do that with postgres ? a simple example / documentation link ?
- zapov 12y agohttps://github.com/ngs-doo/revenj/blob/master/tutorials/revenj-tutorial-linq-basics.md https://github.com/ngs-doo/revenj/blob/master/tutorials/reve... SELECT p, array_agg(SELECT c FROM "MasterDetail"."Child_entity" c WHERE c."parentID" = p."ID") as c FROM "MasterDetail"."Parent_entity" p
- exogen 12y agoWith Postgres: SELECT u.name, json_agg(p) AS posts FROM users u, posts p WHERE p.user_id = u.user_id GROUP BY u.user_id;
- mintplant 12y agoThank you so much for this! I spent a good chunk of today trying to work out how to do this, and had given up concluding that it wasn't possible.
- exogen 12y agoGlad it helped! :) It honestly took me a while to boil it down to that – the docs for those JSON functions are poorly explained and illustrated, IMO.
- mjrpes 12y agoI was playing around with postgres and was able to get a query that puts everything in JSON: select json_agg(sub) from (select u.username, (select array_agg(p) from posts p where u.id = p.user_id) posts from users u) sub;
- danneu 12y agoUsing json_build_object (Postgres 9.4) to map each username to an array of posts: SELECT json_build_object( u.username, (SELECT json_agg(p) FROM posts p WHERE u.id = p.user_id) ) FROM users u Output: [ {"chuck": null}, {"blair": [ {"id": 1, "markup": "hello"}, {"id": 4, "markup": "world"} ]}, {"serena": [{"id": 5, "markup": "testing"}]} ] At least I think you were trying to do that.
- deleted 12y ago[deleted]
- mjrpes 12y agoEDIT: Nevermind my last post. Your query makes sense now and would indeed work well.