Skip to content

Instantly share code, notes, and snippets.

@StephanSchmidt
Created December 20, 2022 14:20
Show Gist options
  • Save StephanSchmidt/92a47986a8ccf9a7a90ef406c41cb74d to your computer and use it in GitHub Desktop.
Save StephanSchmidt/92a47986a8ccf9a7a90ef406c41cb74d to your computer and use it in GitHub Desktop.
Graphjin Graphql
/* action='GetTasks',controller='graphql',framework='graphjin' */
/** GraphQL query:
GetTasks {
tasks(limit: 1000, where: { user_id: $userId } ){
id
title
user {
id
name
}
status {
id
status
}
}
*/
/** By hand **/
/** not the same result b/c left outer join vs. join **/
WITH tasks AS ( select json_agg(row_to_json(t)) from (
SELECT
t.id id,
t.title title,
u.name name,
s.status status
FROM tasks t, users u, status s
WHERE t.user_id=u.id AND t.status_id=s.id
AND t.user_id=$1
) t )
select json_build_object(
'tasks', (SELECT * from tasks)
);
SELECT jsonb_build_object('tasks', __sj_0.json) AS __root
FROM ((SELECT true)) AS __root_x
LEFT OUTER JOIN LATERAL (SELECT COALESCE(jsonb_agg(__
sj_0.json), '[]') AS json
FROM (SELECT to_jsonb(__sr_0.*) AS json
FROM (SELECT "tasks_0"."id" AS "id",
"tasks_0"."title" AS "title",
__sj_1.json AS "status",
__sj_2.json AS "user"
FROM (SELECT "tasks"."id",
"ta
sks"."title",
"tasks"."status_id",
"tasks"."user_id"
FROM "public"."tasks" AS "tasks"
WHERE (("tasks"."user_id") = $1)
LIMIT 1000) AS "tasks_0"
LEFT OUTER JOIN LATERAL (SELECT to_jsonb(__sr_2.*) AS json
FROM (
SELECT "users_2"."id" AS "id", "users_2"."name" AS "name" FROM
(SELECT "users"."id", "users"."name" FROM "public"."users" AS "users" WHERE (("users"."id") = ("tasks_0"."user_id")) LIMIT 1)
AS "users_2")
AS "__sr
_2") AS "__sj_2" ON true
LEFT OUTER JOIN LATERAL (SELECT to_jsonb(__sr_1.*) AS json
FROM (SELECT "status_1"."id" AS "id", "status_1"."status" AS "status"
FROM (SELECT "status"."id", "status"."status"
FROM "public"."status" AS "status"
WHERE (("status"."id") = ("tasks_0"."status_id"))
LIMIT 1) AS "status_1") AS "__sr_1") AS "__sj_1"
ON true) AS "__sr_0") AS "__sj_0") AS "__sj_0"
ON true
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment