Question: How to use t.connection to behave like t.relatedConnection

[emroot]

Hey there,

I'm been using t.relatedConnection, but because of prisma's limitation on order on a JSONB field, I need to build my query by hand.

Before

items: t.relatedConnection('items', {
      cursor: 'id',
      query: {
        where: {
          isDeleted: false,
        },
        orderBy: [
          {
            createdAt: 'desc',
          },
          {
            id: 'desc',
          },
        ],
      },
      edgesNullable: false,
      nodeNullable: false,
    }),

I rewrote my code in this way:

items: t.connection({
      type: CollectionItem,
      resolve: (parent, args) =>
        resolveCursorConnection(
          {
            args,
            toCursor: (item) => item.id,
          },
          // Manually defining the arg type here is required
          // so that typescript can correctly infer the return value
          async ({ before, after, limit, inverted }: ResolveCursorConnectionArgs) => {
            const itemId = before ?? after;

            const items = await db.$queryRaw<ICollectionItem[]>`
              SELECT ci.*
                FROM "CollectionItem" ci
                WHERE ci."collectionId" = ${parent.id}::uuid
                  AND ci."isDeleted" = false
                  ${
                    itemId
                      ? Prisma.sql`AND ci."id" > ${Prisma.raw(`'${itemId}'::uuid`)} `
                      : Prisma.empty
                  }
                ORDER BY
                  -- Prioritize items based on the existence of the specific property
                  (SELECT
                    CASE WHEN jsonb_object_agg(prop->>'property', prop->>'value') ? 'qhu58ls5ww' THEN 0 ELSE 1 END
                  FROM jsonb_array_elements(ci.properties) AS prop),
                  -- Handle sorting by the property value, placing empty strings last
                  (SELECT
                    CASE
                      WHEN prop->>'value' = '' THEN 'zzzzzzzz' -- Assume 'zzzzzzzz' is a value that sorts after any expected value
                      WHEN prop->>'value' IS NULL THEN 'zzzzzzzz'
                      ELSE prop->>'value'
                    END
                  FROM jsonb_array_elements(ci.properties) AS prop
                  WHERE prop->>'property' = 'qhu58ls5ww'
                  ORDER BY CASE
                              WHEN prop->>'value' = '' THEN 2
                              WHEN prop->>'value' IS NULL THEN 1
                              ELSE 0
                            END,
                            prop->>'value'
                  LIMIT 1) NULLS LAST
                  ${limit !== null ? Prisma.sql`OFFSET ${limit}` : Prisma.empty}
              `;

            return items ?? [];
          }
        ),
    }),

Still need to figure out how to optimize my query, but I was curious how to return my cursor, startCursor, endCursor in the same format as t.relatedConnection. Also should I be using resolveCursorConnection or do you recommend using a different function? Seems like toCursor is required, maybe there's another way to do this by using relatedConnection and keep using cursor: 'id'

Thanks

[emroot]

Result before:

"getCollection": {
      "items": {
        "pageInfo": {
          "endCursor": "R1BDOlM6ZWUyOGY1NDktNmUxOS00ZDQ5LWE5N2EtYjUzZmFiMzMyZWY4",
          "hasNextPage": true,
          "hasPreviousPage": false,
          "startCursor": "R1BDOlM6ZmQ5YzE5YWUtYTg1MS00YjQzLWI3NDItMGU1NjFiMDY1MGI4"
        },
        "edges": [
          {
            "cursor": "R1BDOlM6ZmQ5YzE5YWUtYTg1MS00YjQzLWI3NDItMGU1NjFiMDY1MGI4",
            "node": {
              "properties": [
                {
                  "value": "Test"
                },
                {
                  "value": ""
                },
                {
                  "value": ""
                }
              ],
              "id": "Q29sbGVjdGlvbkl0ZW06ZmQ5YzE5YWUtYTg1MS00YjQzLWI3NDItMGU1NjFiMDY1MGI4"
            }
          },

Result after:

{
  "data": {
    "getCollection": {
      "items": {
        "pageInfo": {
          "endCursor": "fd9c19ae-a851-4b43-b742-0e561b0650b8",
          "hasNextPage": true,
          "hasPreviousPage": false,
          "startCursor": "3dc81413-9b1f-4039-8380-3cf026bb770c"
        },
        "edges": [
          {
            "cursor": "3dc81413-9b1f-4039-8380-3cf026bb770c",
            "node": {
              "properties": [
                {
                  "value": "Test"
                },
                {
                  "value": ""
                },
                {
                  "value": ""
                }
              ],
              "id": "Q29sbGVjdGlvbkl0ZW06M2RjODE0MTMtOWIxZi00MDM5LTgzODAtM2NmMDI2YmI3NzBj"
            }
          },

[hayes]

You can use the format/parsePrismaCursor helpers defined here: https://github.com/hayes/pothos/blob/main/packages/plugin-prisma/src/util/cursors.ts#L33-L47

[emroot]

awesome thank you