Add sorting to member roles GraphQL query

What does this MR do and why?

Add optional orderBy and sort parameters to the memberRoles GraphQL query.

The orderBy parameter accepts the following values:

• NAME (default)
• CREATED_AT
• ID

The sort parameter accepts the following values:

• ASC (default)
• DESC

Issue: #417755 (closed)

Queries

Ordering by name within a namespace

SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."namespace_id" = 9970 ORDER BY "member_roles"."name" ASC

Plan

https://console.postgres.ai/shared/c6c329cf-f14d-45a1-90e3-b688679bb8e8

 Sort  (cost=3.30..3.31 rows=1 width=70) (actual time=1.630..1.631 rows=0 loops=1)
   Sort Key: member_roles.name
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=6 read=2
   I/O Timings: read=1.564 write=0.000
   ->  Index Scan using index_member_roles_on_namespace_id on public.member_roles  (cost=0.28..3.29 rows=1 width=70) (actual time=1.606..1.607 rows=0 loops=1)
         Index Cond: (member_roles.namespace_id = 9970)
         Buffers: shared hit=3 read=2
         I/O Timings: read=1.564 write=0.000

Ordering by name for an instance

SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."namespace_id" IS NULL ORDER BY "member_roles"."name" ASC

Plan

https://console.postgres.ai/shared/f11e2fdb-70d9-4faf-bc27-a5549f7af19d

 Sort  (cost=2.85..2.86 rows=1 width=70) (actual time=0.051..0.052 rows=0 loops=1)
   Sort Key: member_roles.name
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=5
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_member_roles_on_namespace_id on public.member_roles  (cost=0.28..2.84 rows=1 width=70) (actual time=0.017..0.017 rows=0 loops=1)
         Index Cond: (member_roles.namespace_id IS NULL)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000

Ordering by id within a namespace

SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."namespace_id" = 9970 ORDER BY "member_roles"."id" ASC

Plan

https://console.postgres.ai/shared/31b12113-a953-469a-a02a-6aa280efa098

 Sort  (cost=3.30..3.31 rows=1 width=70) (actual time=0.174..0.176 rows=0 loops=1)
   Sort Key: member_roles.id
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=8
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_member_roles_on_namespace_id on public.member_roles  (cost=0.28..3.29 rows=1 width=70) (actual time=0.146..0.147 rows=0 loops=1)
         Index Cond: (member_roles.namespace_id = 9970)
         Buffers: shared hit=5
         I/O Timings: read=0.000 write=0.000

Ordering by id for an instance

SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."namespace_id" IS NULL ORDER BY "member_roles"."id" ASC

Plan

https://console.postgres.ai/shared/d4030b25-c027-4734-a2c2-08d95f2f3fd8

 Sort  (cost=2.85..2.86 rows=1 width=70) (actual time=0.052..0.054 rows=0 loops=1)
   Sort Key: member_roles.id
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=5
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_member_roles_on_namespace_id on public.member_roles  (cost=0.28..2.84 rows=1 width=70) (actual time=0.021..0.021 rows=0 loops=1)
         Index Cond: (member_roles.namespace_id IS NULL)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000

How to set up and validate locally

1. Create some member_roles in the console:

   MemberRole.create(name: 'A', base_access_level: 10)
   MemberRole.create(name: 'C', base_access_level: 10)
   MemberRole.create(name: 'B', base_access_level: 10)

2. Visit http://localhost:3000/-/graphql-explorer and execute the query with varying orderBy and sort parameters:

    {
      memberRoles(orderBy:NAME, sort:DESC) {
        nodes {
          id
          name
        }
      }
    }

3. Verify the result is something like:

    {
      "data": {
        "memberRoles": {
          "nodes": [
            {
              "id": "gid://gitlab/MemberRole/2",
              "name": "C"
            },
            {
              "id": "gid://gitlab/MemberRole/3",
              "name": "B"
            },
            {
              "id": "gid://gitlab/MemberRole/1",
              "name": "A"
            }
          ]
        }
      }
    }