Graphjin Graphql

users.sql

/* 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