Support custom ordering in pagination

[gearnode]

Currently, pagination is restricted to ordering by created_at. We aim to enhance this functionality by supporting custom ordering fields such as name, updated_at, status, etc.

Proposed Changes

1. GraphQL Schema Update

We will extend the GraphQL schema to support custom ordering. Below is an example of the updated GraphQL definition:

   enum OrderDirection {
     ASC
     DESC
   }

   enum PeopleOrderField {
     CREATED_AT
     NAME
     UPDATED_AT
     STATUS
   }

   input OrganizationOrder {
     direction: OrderDirection!
     field: PeopleOrderField!
   }

   type User implements Node {
     organizations(first: Int, last: Int, before: CursorKey, after: CursorKey, orderBy: OrganizationOrder)
   }

   type Query {
     viewer: User!
   }

2. Page package and cursor key implementation

• CursorKey:
  Transition from custom binary encoding to a JSON array. This change is necessary because the cursor key must now accommodate variable sizes, storing not only the ID and timestamp but also a custom value based on the chosen ordering field.

• Cursor:
  The SQL fragment generated by the cursor should be dynamic. It must incorporate the field name and field value, replacing the hardcoded created_at.
  The SQL should consider the orderBy direction and position, rather than relying solely on position for determining the order.