Postgraphile: Question: Error coming from table condition extension

[tayloraucoin]

• [X] question

Hello! Apologies, I have an extension question stemming from #1349 :)



It appears as if I have it all set up correctly, and getting a weird error in response:

"syntax error at or near "'
      )) and (TRUE) and (TRUE)
order by __local_1__."name" ASC,__local_1__."id" ASC
limit 15
) __local_1__), __local_5__ as (select json_agg(to_json(__local_0__)) as data from __local_0__) select coalesce((select __local_5__.data from __local_5__), '""

From what I can tell, the syntax error is originating from the return of the condition. But it appears that I am sending a clean return from the plugin though. Therefore I am unclear on where this syntax issue is stemming from.

I have an error catch below the condition extension and nothing pops up from there.

Background:

The graphql query I am using has been tested in use for several months aside from this new condition extension.


query getCreators(
    $after: Cursor
    $count: Int
    $orderBy: [CreatorsOrderBy!]
    $types: String
  ) {
    creators(
      after: $after
      condition: { containsTypesByJsonbIndex: $types }
      first: $count
      orderBy: $orderBy
    ) {
      nodes {
        id
        name
      }
      totalCount
    }
  }


The table in question:

CREATOR: {
    ...
    types: {
      classType: UUID,
      types: [UUID],
      subTypes: [UUID]
    }
    ...
  }

The Code:

const CreatorContainsTypesByJsonbGinIndex = makeAddPgTableConditionPlugin(
  "app_public",
  "creators",
  "containsTypesByJsonbIndex",
  build => ({
    description:
      "Allows for GIN indexing of JSONB type field",
    type: build.graphql.GraphQLString,
  }),
  (value, helpers, build) => {
    try {
      const { sql, sqlTableAlias } = helpers;
      const sqlIdentifier = sql.identifier(Symbol("creator"));

      // parse string input to a JSON object
      const parsed = JSON.parse(value);
      console.log(parsed);
      /*
        {
          classType: '0e6a37a0-11e8-48b1-9e14-c82256ed0915',
          types: [ '311eaacd-34f4-450d-8e43-666f92dff729' ],
          subTypes: [
            'b05cfbf4-3f31-4e3c-977f-f118bb7adfa6',
            '2e5377de-90a1-42e5-8e9f-142263edb270'
          ]
        }
      */

      const conditions = [];

      /* This SQL code has been proven to work in CLI psql */
      if (parsed) {
        if (parsed.classType) {
          const classTypeSqlItem = sql.value(parsed.classType);
          conditions.push(sql.fragment`${sqlIdentifier}.types->'classType' @> ${classTypeSqlItem}`);
        }

        if (parsed.types && parsed.types.length) {
          parsed.types.forEach(typeId => {
            conditions.push(sql.fragment`${sqlIdentifier}.types->'types' @> ${sql.value(typeId)}`);

            // ALT conditions.push(sql.fragment`${sqlIdentifier}.types->'types' @> ${sql.value([typeId])}`);
          });
        }

        if (parsed.subTypes && parsed.subTypes.length) {
          parsed.subTypes.forEach(subTypeId => {
            conditions.push(sql.fragment`${sqlIdentifier}.types->'subTypes' @> ${sql.value(subTypeId)}`);

             // ALT conditions.push(sql.fragment`${sqlIdentifier}.types->'subTypes' @> ${sql.value([subTypeId])}`);
          });
        }
      }

      const queryString = conditions.length ? sql.fragment`WHERE (${sql.join(conditions, ') OR (')})` : sql.blank;

      // sql.compile compiles the query into an SQL statement and a list of values
      const { text, values } = sql.compile(queryString);

      console.log(text);
      /*
        WHERE (__local_0__.types->'classType' @> $1) OR (__local_0__.types->'types' @> $2) OR (__local_0__.types->'subTypes' @> $3) OR (__local_0__.types->'subTypes' @> $4)
      */

      console.log(values);
      /*
        [
          '0e6a37a0-11e8-48b1-9e14-c82256ed0915',
          '311eaacd-34f4-450d-8e43-666f92dff729',
          'b05cfbf4-3f31-4e3c-977f-f118bb7adfa6',
          '2e5377de-90a1-42e5-8e9f-142263edb270'
        ]

       ALT
        [
          '0e6a37a0-11e8-48b1-9e14-c82256ed0915',
          [ '311eaacd-34f4-450d-8e43-666f92dff729' ],
          [ 'b05cfbf4-3f31-4e3c-977f-f118bb7adfa6' ],
          [ '2e5377de-90a1-42e5-8e9f-142263edb270' ]
        ]
      */

      return sql.fragment`exists(
        select *
        from app_public.creators as ${sqlIdentifier} ${queryString}'
      )`;
    } catch (e) {
      /* No error log seen from this */
      console.log('ERROR', e);
      throw e;
    }
  }
);

I have not applied INDEXING to my table yet, as I want to measure the response time differences between different indexing methods. Since @> covers my use-case jsonb_path_ops will be the method I utilize (performance benefits!).

Thank you for any insight you may provide 🙏

[benjie]

Step one would be to find out the full generated SQL statement; you can do so with a debugger or probably with one of the DEBUG envvars; see:

https://www.graphile.org/postgraphile/debugging/

[tayloraucoin]

Right, thank you @benjie for reminding me of that feature. I'll be sure to utilize that more when troubleshooting.

I was able to clearly identify the original syntax issue after seeing it in that form.

The error now reads "invalid input syntax for type json"

Please excuse my sql greenness, but to me this appears to be alright. Do you see anything that it may consider invalid?

Here is the block pertaining to the condition extension:

from (
    select __local_1__.*
    from "app_public"."creators" as __local_1__
    where (
      exists(
        select *
        from app_public.creators as __local_4__ WHERE (
          __local_4__.types->'classType' @> $1
        ) OR (
          __local_4__.types->'types' @> $2
        ) OR (
          __local_4__.types->'subTypes' @> $3
        ) OR (
          __local_4__.types->'subTypes' @> $4
        )
      )
    ) and (TRUE) and (TRUE)
    order by __local_1__."name" ASC,
    __local_1__."id" ASC
    limit 15
  ) __local_1__

And here is the huge return block ...

select to_json(
    (
      json_build_object(
        '__identifiers'::text,
        json_build_array(__local_1__."id"),
        '@node'::text,
        (
          json_build_object(
            'id'::text,
            (__local_1__."id")
          )
        )
      )
    )
  ) as "@edges",
  to_json(
    (
      json_build_object(
        'adminLabel'::text,
        (__local_1__."admin_label"),
        'attributes'::text,
        (__local_1__."attributes"),
        'createdAt'::text,
        (__local_1__."created_at"),
        'grade'::text,
        (__local_1__."grade"),
        'id'::text,
        (__local_1__."id"),
        'location'::text,
        (__local_1__."location"),
        'name'::text,
        (__local_1__."name"),
        '@owner'::text,
        (
          select json_build_object(
            'id'::text,
            (__local_2__."id"),
            'firstName'::text,
            (__local_2__."first_name"),
            'lastName'::text,
            (__local_2__."last_name")
          ) as object
          from "app_public"."users" as __local_2__
          where (__local_1__."owner_id" = __local_2__."id") and (TRUE) and (TRUE)
        ),
        'profileImage'::text,
        (__local_1__."profile_image"),
        'socialHandle'::text,
        (__local_1__."social_handle"),
        'styleDescription'::text,
        (__local_1__."style_description"),
        'types'::text,
        (__local_1__."types"),
        '@uploadedBy'::text,
        (
          select json_build_object(
            'id'::text,
            (__local_3__."id"),
            'firstName'::text,
            (__local_3__."first_name"),
            'lastName'::text,
            (__local_3__."last_name")
          ) as object
          from "app_public"."users" as __local_3__
          where (__local_1__."uploaded_by_id" = __local_3__."id") and (TRUE) and (TRUE)
        )
      )
    )
  ) as "@nodes",
  to_json(
    json_build_array(
      'name_asc',
      json_build_array(
        __local_1__."name",
        __local_1__."id"
      )
    )
  ) as "__cursor"
  from (
    select __local_1__.*
    from "app_public"."creators" as __local_1__
    where (
      exists(
        select *
        from app_public.creators as __local_4__ WHERE (
          __local_4__.types->'classType' @> $1
        ) OR (
          __local_4__.types->'types' @> $2
        ) OR (
          __local_4__.types->'subTypes' @> $3
        ) OR (
          __local_4__.types->'subTypes' @> $4
        )
      )
    ) and (TRUE) and (TRUE)
    order by __local_1__."name" ASC,
    __local_1__."id" ASC
    limit 15
  ) __local_1__
),
__local_5__ as (
  select json_agg(
    to_json(__local_0__)
  ) as data
  from __local_0__
)
select coalesce(
  (
    select __local_5__.data
    from __local_5__
  ),
  '[]'::json
) as "data",
(
  select json_build_object(
    'totalCount'::text,
    count(1)
  )
  from "app_public"."creators" as __local_1__
  where (
    exists(
      select *
      from app_public.creators as __local_4__ WHERE (
        __local_4__.types->'classType' @> $5
      ) OR (
        __local_4__.types->'types' @> $6
      ) OR (
        __local_4__.types->'subTypes' @> $7
      ) OR (
        __local_4__.types->'subTypes' @> $8
      )
    )
  )
) as "aggregates"

[benjie]

That'll probably be one of the values you're feeding in ($1, $2, etc) rather than the SQL itself.

[tayloraucoin]

I've sorted that out. My issue is now that everything is being returned instead of the condition matches (including after changing my select * to select 1), which is not on the premise of this issue.

Thank you for helping me in what turned out to be simple troubleshooting.

[benjie]

Your condition currently is sort of like this pseudocode:

SELECT ...
FROM creators __local_1__
WHERE (
  EXISTS(
    SELECT *
    FROM creators __local_4__
    WHERE __local_4__.stuff
  )
)

Postgres will look at this, notice that the subquery has no dependency on outer data (it doesn't reference __local_1__) so it'll evaulate it up front:

  EXISTS(
    SELECT *
    FROM creators __local_4__
    WHERE __local_4__.stuff
  )

^ Returns true, because there is a record matching that

So now it's

SELECT ...
FROM creators __local_1__
WHERE (
  TRUE
)

So that's why your collection is unfiltered.

You need to re-write your condition so that it references __local_1__ (aka sqlTableAlias).

That might be something like:

SELECT ...
FROM creators __local_1__
WHERE (
  EXISTS(
    SELECT *
    FROM creators __local_4__
    WHERE __local_4__.stuff
    AND __local_4__.parent_creator = __local_1__.id
  )
)

or, more likely, you don't mean EXISTS at all, and the query is more like:

SELECT ...
FROM creators __local_1__
WHERE (
  __local_1__.stuff
)

[tayloraucoin]

You're a saint @benjie 🙏

That was exactly what was missing! That explanation really helped my understanding as well. Little level up for sure ⬆