Draft: Add keyset pagination for serializers

What does this MR do and why?

This MR enables the WithPagination concern to perform keyset pagination, with Groups::DependenciesController#index being the first controller action to use it. This enables serializers to implement keyset pagination in a repeatable way.

💾 Database queries

Order by ID

WITH our_occurrences AS MATERIALIZED (
    SELECT
        DISTINCT ON ( sbom_occurrences.component_version_id )
        sbom_occurrences.*,
        count( sbom_occurrences.id ) OVER ( PARTITION BY sbom_occurrences.component_version_id ) AS occurrence_count,
        dense_rank() OVER (
            PARTITION BY sbom_occurrences.component_version_id
            ORDER BY project_id
        ) AS project_count
    FROM
        sbom_occurrences
    WHERE
        sbom_occurrences.project_id IN (
            SELECT
                projects.id
            FROM
                projects
            WHERE
                projects.namespace_id IN (
                    SELECT
                        namespaces.traversal_ids[array_length(
                            namespaces.traversal_ids,
                            1
                        )] AS id
                    FROM
                        namespaces
                    WHERE
                        namespaces.type = 'Group' AND
                        traversal_ids @> '{9970}'
                ) AND
                projects.marked_for_deletion_at IS NULL AND
                projects.pending_delete = false
        ) AND
        sbom_occurrences.component_version_id IS NOT NULL
    ORDER BY
        sbom_occurrences.component_version_id DESC,
        project_count DESC,
        sbom_occurrences.id DESC
)
SELECT
    sbom_occurrences.id,
    sbom_occurrences.created_at,
    sbom_occurrences.updated_at,
    sbom_occurrences.component_version_id,
    sbom_occurrences.project_id,
    sbom_occurrences.pipeline_id,
    sbom_occurrences.source_id,
    sbom_occurrences.commit_sha,
    sbom_occurrences.component_id,
    sbom_occurrences.uuid,
    sbom_occurrences.package_manager,
    sbom_occurrences.component_name,
    sbom_occurrences.input_file_path,
    sbom_occurrences.licenses,
    sbom_occurrences.highest_severity,
    sbom_occurrences.vulnerability_count
FROM
    our_occurrences AS sbom_occurrences
WHERE
    sbom_occurrences.id > 3201
ORDER BY
    sbom_occurrences.id ASC
LIMIT 21;

Order by name

WITH our_occurrences AS MATERIALIZED (
    SELECT
        DISTINCT ON ( sbom_occurrences.component_version_id )
        sbom_occurrences.*,
        count( sbom_occurrences.id ) OVER ( PARTITION BY sbom_occurrences.component_version_id ) AS occurrence_count,
        dense_rank() OVER (
            PARTITION BY sbom_occurrences.component_version_id
            ORDER BY project_id
        ) AS project_count
    FROM
        sbom_occurrences
    WHERE
        sbom_occurrences.project_id IN (
            SELECT
                projects.id
            FROM
                projects
            WHERE
                projects.namespace_id IN (
                    SELECT
                        namespaces.traversal_ids[array_length(
                            namespaces.traversal_ids,
                            1
                        )] AS id
                    FROM
                        namespaces
                    WHERE
                        namespaces.type = 'Group' AND
                        traversal_ids @> '{9970}'
                ) AND
                projects.marked_for_deletion_at IS NULL AND
                projects.pending_delete = false
        ) AND
        sbom_occurrences.component_version_id IS NOT NULL
    ORDER BY
        sbom_occurrences.component_version_id DESC,
        project_count DESC,
        sbom_occurrences.id DESC
)
SELECT
    sbom_occurrences.id,
    sbom_occurrences.created_at,
    sbom_occurrences.updated_at,
    sbom_occurrences.component_version_id,
    sbom_occurrences.project_id,
    sbom_occurrences.pipeline_id,
    sbom_occurrences.source_id,
    sbom_occurrences.commit_sha,
    sbom_occurrences.component_id,
    sbom_occurrences.uuid,
    sbom_occurrences.package_manager,
    sbom_occurrences.component_name,
    sbom_occurrences.input_file_path,
    sbom_occurrences.licenses,
    sbom_occurrences.highest_severity,
    sbom_occurrences.vulnerability_count
FROM
    our_occurrences AS sbom_occurrences
WHERE
    sbom_occurrences.component_name > '@babel/helper-plugin-utils'
ORDER BY
    sbom_occurrences.component_name ASC
LIMIT 21;

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Screen_Recording_2023-11-28_at_3.28.16_PM

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. Get the path of a group that has data populated in the dependency list
2. Open your browsers developer tools and go to the network tab
3. Go to http://<gdk_host>/groups/<group_path>/-/dependencies.json?pagination=keyset
4. Verify that there is a link header in the response with the cursor param
5. Bonus points:
   1. Follow the link and verify that it goes to the next page
   2. Verify that it works with other sorting options (e.g. http://gdk.local:3000/groups/enterprise/-/dependencies.json?pagination=keyset&order_by=name&sort=asc)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.