Skip to content

Instantly share code, notes, and snippets.

@rafales
Last active October 24, 2017 11:54
Show Gist options
  • Save rafales/1cdc6c31b37adffae2e7e1a417c9f1d6 to your computer and use it in GitHub Desktop.
Save rafales/1cdc6c31b37adffae2e7e1a417c9f1d6 to your computer and use it in GitHub Desktop.
Retrieving nested graphs from Postgres using JSON
BEGIN TRANSACTION;
CREATE TABLE "user"
(
"id" SERIAL PRIMARY KEY,
"name" VARCHAR NOT NULL,
"email" VARCHAR NOT NULL
);
CREATE TABLE "post"
(
"id" SERIAL PRIMARY KEY,
"text" VARCHAR NOT NULL,
"image" VARCHAR NOT NULL,
"author_id" INTEGER NOT NULL REFERENCES "user" ("id")
);
CREATE TABLE "comment"
(
"id" SERIAL PRIMARY KEY,
"post_id" INTEGER NOT NULL REFERENCES "post" ("id"),
"author_id" INTEGER NOT NULL REFERENCES "user" ("id"),
"text" VARCHAR NOT NULL
);
CREATE TABLE "post_like"
(
"post_id" INTEGER NOT NULL REFERENCES "post" ("id"),
"user_id" INTEGER NOT NULL REFERENCES "user" ("id"),
PRIMARY KEY ("post_id", "user_id")
);
INSERT INTO
"user"
VALUES
(1, 'Rafal Stozek', 'rafal@gmail.com'),
(2, 'John Doe', 'john.doe@gmail.com');
INSERT INTO
"post"
VALUES
(1, 'This is awesome', 'awesome.jpg', 1),
(2, 'Some graphs', 'graphs.jpg', 1),
(3, 'Hello world', 'hello.jpg', 2);
INSERT INTO
"comment"
VALUES
(1, 1, 2, 'It is awesome, indeed'),
(2, 1, 1, 'Thanks bro!'),
(3, 2, 2, 'Nice graph!');
INSERT INTO
"post_like"
VALUES
(1, 2),
(2, 1);
COMMIT;
-- load simple relation as JSON object
SELECT
post.*,
row_to_json(author.*) AS author
FROM
post
JOIN author ON (author.id = post.author_id);
-- load sub-objects with simple aggregation
-- this sucks if you want more than one relation, as you must add a lot to GROUP BY (whole relations)
SELECT
post.*,
json_agg(row_to_json(comment.*)) as comments
FROM
post
JOIN comment ON (comment.post_id = post.id)
GROUP BY post.id;
-- Load m2m relations as sub-queries.
-- Either load foreign key relations as json object with row_to_json/jsonb_build_object
-- or use aliases like 'author.id' and run returned object through something like dottie.js transformer.
-- m2m relations will be loaded as JSON arrays.
SELECT jsonb_pretty(jsonb_agg(r)) AS result
FROM (
SELECT
post.id,
post.text,
post.image,
author.id AS "author.id",
author.name AS "author.name",
(post_like.user_id IS NOT NULL) as "viewerLiked",
-- row_to_json(author.*) AS author,
-- jsonb_build_object(
-- 'id', author.id,
-- 'name', author.name
-- ) AS author,
(
SELECT json_agg(row_to_json(d)) AS comments
FROM (
SELECT
comment.*,
comment_author.id AS "author.id",
comment_author.name AS "author.name"
-- jsonb_build_object(
-- 'id', author.id,
-- 'name', author.name
-- ) AS author
FROM comment
JOIN "user" comment_author ON (comment.author_id = comment_author.id)
WHERE post_id = post.id
LIMIT 10 -- fetch no more than 10 comments!
) d
)
FROM post
JOIN "user" author ON (author.id = post.author_id)
LEFT JOIN "post_like" ON (post.id = post_like.post_id AND post_like.user_id = 1)
) r;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment