Skip to content

Instantly share code, notes, and snippets.

@rattrayalex
Last active Apr 30, 2021
Embed
What would you like to do?
Postgraphile SQL Rewrite Example
query MyQuery {
films(
filter: {and: [
{title: {startsWith: "A"}},
{releaseYear: {equalTo: 2006}}
]},
first: 3
) {
nodes {
title
releaseYear
actors(first: 3) {
nodes {
actor {
firstName
lastName
}
}
}
}
}
}
select coalesce(json_agg(__filmsQuery.data), '[]'::json) as "data" from (
select json_build_object(
'@nodes', json_build_object(
'title', __film."title",
'releaseYear', __film."release_year",
'@actors', json_build_object(
'data', (select coalesce(json_agg(__actorsQuery.data), '[]'::json) from (
select json_build_object(
'@nodes', json_build_object(
'@actor', (
select json_build_object(
'firstName', __actor."first_name",
'lastName', __actor."last_name"
) as object
from "public"."actor" as __actor
where __film_actor."actor_id" = __actor."actor_id"
)
)
) as data
from "public"."film_actor" as __film_actor
where __film_actor."film_id" = __film."film_id"
order by __film_actor."actor_id" asc,
__film_actor."film_id" asc
limit 3
) __actorsQuery
))
)
) as data
from "public"."film" as __film
where __film."title" like $1
and __film."release_year" = $2
order by __film."film_id" asc
limit 3
) __filmsQuery
with __local_0__ as (
select to_json(
(
json_build_object(
'title'::text,
(__local_1__."title"),
'releaseYear'::text,
(__local_1__."release_year"),
'@actors'::text,
(
with __local_2__ as (
select to_json(
(
json_build_object(
'@actor'::text,
(
select json_build_object(
'firstName'::text,
(__local_3__."first_name"),
'lastName'::text,
(__local_3__."last_name")
) as object
from "public"."actor" as __local_3__
where (__local_4__."actor_id" = __local_3__."actor_id") and (TRUE) and (TRUE)
)
)
)
) as "@nodes"
from (
select __local_4__.*
from "public"."film_actor" as __local_4__
where (__local_4__."film_id" = __local_1__."film_id") and (TRUE) and (TRUE)
order by __local_4__."actor_id" ASC,
__local_4__."film_id" ASC
limit 3
) __local_4__
),
__local_5__ as (
select json_agg(
to_json(__local_2__)
) as data
from __local_2__
)
select json_build_object(
'data'::text,
coalesce(
(
select __local_5__.data
from __local_5__
),
'[]'::json
)
)
)
)
)
) as "@nodes"
from (
select __local_1__.*
from "public"."film" as __local_1__
where (
(
(
(
(
__local_1__."title" LIKE $1
)
)
)
and (
(
(
__local_1__."release_year" = $2
)
)
)
)
) and (TRUE) and (TRUE)
order by __local_1__."film_id" ASC
limit 3
) __local_1__
),
__local_6__ as (
select json_agg(
to_json(__local_0__)
) as data
from __local_0__
)
select coalesce(
(
select __local_6__.data
from __local_6__
),
'[]'::json
) as "data"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment