Skip to content

Allows filtering of projects by negated compliance framework id

What does this MR do and why?

Adds not id filter for filtering projects by compliance framework.

How to set up and validate locally

  1. Create a group
  2. Create 3 projects, project_a project_b project_c
  3. Create two compliance frameworks for the group. refer https://docs.gitlab.com/ee/user/group/compliance_frameworks.html
  4. Add 1 compliance framework to project_a and another to project_b
  5. Test group search query with relevant gids
query SearchGroup {
  group(fullPath: "YOUR_GROUP_PATH") {
    name
    projects(complianceFrameworkFilters: {not: {id: "VALID_GID"}}) {
      nodes {
        id
        fullPath
        complianceFrameworks {
          edges {
            node {
              id
            }
          }
        }
        name
      }
      pageInfo {
        endCursor
        startCursor
        hasNextPage
      }
    }
  }
}

Query Details

command: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15214/commands/52835

Query

SELECT 
  projects.* 
FROM 
  projects 
  LEFT OUTER JOIN project_compliance_framework_settings compliance_framework_setting ON compliance_framework_setting.project_id = projects.id 
WHERE 
  projects.namespace_id = 88 
  AND (
    compliance_framework_setting.framework_id != 6 
    OR compliance_framework_setting.framework_id IS NULL
  )

Plan

 Nested Loop Left Join  (cost=0.73..105.05 rows=22 width=761) (actual time=1.533..3.705 rows=4 loops=1)
   Filter: ((compliance_framework_setting.framework_id <> 6) OR (compliance_framework_setting.framework_id IS NULL))
   Rows Removed by Filter: 0
   Buffers: shared hit=13 read=5
   I/O Timings: read=3.553 write=0.000
   ->  Index Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.44..33.51 rows=22 width=761) (actual time=1.476..3.622 rows=4 loops=1)
         Index Cond: (projects.namespace_id = 88)
         Buffers: shared hit=2 read=5
         I/O Timings: read=3.553 write=0.000
   ->  Index Scan using index_project_compliance_framework_settings_on_project_id on public.project_compliance_framework_settings compliance_framework_setting  (cost=0.29..3.24 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=4)
         Index Cond: (compliance_framework_setting.project_id = projects.id)
         Buffers: shared hit=11
         I/O Timings: read=0.000 write=0.000

MR acceptance checklist

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

Related to #388188 (closed)

Edited by Harsimar Sandhu

Merge request reports