Postgraphile SQL Rewrite Example

query.gql

query MyQuery {
  films(
    filter: {and: [
      {title: {startsWith: "A"}}, 
      {releaseYear: {equalTo: 2006}}
    ]},
    first: 3
  ) {
    nodes {
      title
      releaseYear
      
      actors(first: 3) {
        nodes {
          actor {
            firstName
            lastName
          }
        }
      }
    }
  }
}

query.sql

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

x-query-original.sql

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"