Resolve License Sorting issue on Dependencies - Project level

What does this MR do and why?

Currently, when sorting on dependencies.licenses in ASC or DESC direction, the sorting isn't working the way it should. In this MR we focus on fixing this on the Project level. In a separate MR we will focus on the fix for Group level.

References

• Parent Issue: #550256 (closed)
• Related Discussion: #527254 (closed)
• Related MR: !189010 (merged)

Database Review Details

Current SQL Query:

SELECT
    sbom_occurrences.*,
    EXISTS (
        SELECT
            1
        FROM
            sbom_graph_paths
        WHERE
            sbom_graph_paths.descendant_id = sbom_occurrences.id
        LIMIT 1
    ) AS has_dependency_paths
FROM
    sbom_occurrences
    LEFT JOIN sbom_sources ON sbom_sources.id = sbom_occurrences.source_id
WHERE
    sbom_occurrences.project_id = 278964 AND
    sbom_occurrences.traversal_ids = '{9970}' AND
    sbom_occurrences.archived = false AND
    sbom_occurrences.project_id = 278964 AND
    (
        sbom_sources.source_type IN ( 0, 1 ) OR
        sbom_sources.source_type IS NULL
    )
ORDER BY
    ( licenses #> '{0,spdx_identifier}' )::text DESC,
    ( licenses #> '{1,spdx_identifier}' )::text DESC
LIMIT 20
OFFSET 0;

Postgres AI link

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/41890/commands/128483

New SQL Query:

SELECT
    sbom_occurrences.*,
    EXISTS (
        SELECT
            1
        FROM
            sbom_graph_paths
        WHERE
            sbom_graph_paths.descendant_id = sbom_occurrences.id
        LIMIT 1
    ) AS has_dependency_paths
FROM
    sbom_occurrences
    LEFT JOIN sbom_sources ON sbom_sources.id = sbom_occurrences.source_id
WHERE
    sbom_occurrences.project_id = 278964 AND
    sbom_occurrences.traversal_ids = '{9970}' AND
    sbom_occurrences.archived = false AND
    sbom_occurrences.project_id = 278964 AND
    (
        sbom_sources.source_type IN ( 0, 1 ) OR
        sbom_sources.source_type IS NULL
    )
ORDER BY
    COALESCE( ( licenses -> 0 ) ->> 'spdx_identifier', ( licenses -> 0 ) ->> 'name' ) COLLATE "C" DESC,
    COALESCE( ( licenses -> 1 ) ->> 'spdx_identifier', ( licenses -> 1 ) ->> 'name' ) COLLATE "C" DESC
LIMIT 20
OFFSET 0;

Postgres AI link

https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/41890/commands/128486

How to set up and validate locally

1. Import this project to your GDK: https://gitlab.com/gitlab-org/govern/threat-insights-demos/frontend/security-reports-with-dependency-paths
2. Run a pipeline on the imported project to populate the dependency list
3. Since the dependency licenses are empty by default, we need to add test data. Open your Rails console and run the following commands:

# Step 1: Assign the project and its occurrences
project = Project.find_by(id: xxx) # use the ID of the project you've just imported
occurrences = Sbom::Occurrence.where(project_id: project.id)

# Step 2: Create License Data
licenses = {
  mit: {
    "name" => "MIT License",
    "spdx_identifier" => "MIT",
    "url" => "https://spdx.org/licenses/MIT.html"
  },
  apache: {
    "name" => "Apache License 2.0", 
    "spdx_identifier" => "Apache-2.0",
    "url" => "https://spdx.org/licenses/Apache-2.0.html"
  },
  bsd3: {
    "name" => "BSD 3-Clause \"New\" or \"Revised\" License",
    "spdx_identifier" => "BSD-3-Clause", 
    "url" => "https://spdx.org/licenses/BSD-3-Clause.html"
  },
  bsd2: {
    "name" => "BSD 2-Clause \"Simplified\" License",
    "spdx_identifier" => "BSD-2-Clause",
    "url" => "https://spdx.org/licenses/BSD-2-Clause.html"
  },
  unknown: {
    "name" => "Unknown License",
    "spdx_identifier" => "NOASSERTION",
    "url" => nil
  },
  null_spdx: {
    "name" => "Custom License",
    "spdx_identifier" => nil,
    "url" => "https://example.com/custom-license"
  },
  name_only: {
    "name" => "Proprietary License",
    "spdx_identifier" => nil,
    "url" => nil
  }
}

# Step 3: Populate licenses 
occurrences.limit(50).each_with_index do |occ, index|
  license = case index % 4
  when 0
    licenses[:apache]  
  when 1  
    licenses[:bsd3]   
  when 2
    licenses[:mit]     
  else
    next # Keep some empty
  end
  
  occ.update!(licenses: [license])
end

4. Go to http://gdk.test:3000/gitlab-org/dependency-validation/-/dependencies. You should see the licenses are now populated. Since the default sorting is by Severity , the dependencies and their licenses appear mixed together.
5. Click to sort by License. The default sort direction is DESC, so you should see dependencies with lower alphabetical license names first.* See note
6. Change the sort direction to ASC by clicking the sort arrow again. You should now see dependencies with Apache License 2.0 appearing first.
7. Let's verify this works in the GraphQL explorer as well. Go to http://gdk.test:3000/-/graphql-explorer and run:

   {
  project(fullPath: "gitlab-org/dependency-validation") {
    dependencies(sort: LICENSE_ASC) {
      nodes {
        name
        licenses {
          name
          url
          spdxIdentifier
        }
      }
    }
  }
}

Your result should show dependencies with spdxIdentifier: "Apache-2.0" appearing first, sorted alphabetically by license identifier.

8. Now change the sort direction to LICENSE_DESC:

{
  project(fullPath: "gitlab-org/dependency-validation") {
    dependencies(sort: LICENSE_DESC) {
      nodes {
        name
        licenses {
          name
          spdxIdentifier
        }
      }
    }
  }
}

You should see the reverse order, with dependencies now sorted by spdxIdentifier in descending alphabetical order.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.