Fixing filtering of projects by compliance frameworks
What does this MR do and why?
This MR intends to fix a bug in recently introduced filtering of project by multiple compliance frameworks in !158229 (merged). The bug was that if projects are being filtered with multiple compliance frameworks then the filter operation was an or
operation, it was returning all the projects which have at least one of the frameworks in the filters but we need to list down all those projects which have all those frameworks associated with them.
The changes here are similar to what we do for filtering issues by multiple assignees, relevant code is present at https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/finders/issuables/assignee_filter.rb#L49 and https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/concerns/issuable.rb#L122.
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Database
Query 1: Filter projects with frameworks
Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/30675/commands/95233
Click to expand query
SELECT
"projects"."id",
"projects"."name",
"projects"."path",
"projects"."description",
"projects"."created_at",
"projects"."updated_at",
"projects"."creator_id",
"projects"."namespace_id",
"projects"."last_activity_at",
"projects"."import_url",
"projects"."visibility_level",
"projects"."archived",
"projects"."avatar",
"projects"."merge_requests_template",
"projects"."star_count",
"projects"."merge_requests_rebase_enabled",
"projects"."import_type",
"projects"."import_source",
"projects"."approvals_before_merge",
"projects"."reset_approvals_on_push",
"projects"."merge_requests_ff_only_enabled",
"projects"."issues_template",
"projects"."mirror",
"projects"."mirror_last_update_at",
"projects"."mirror_last_successful_update_at",
"projects"."mirror_user_id",
"projects"."shared_runners_enabled",
"projects"."runners_token",
"projects"."build_allow_git_fetch",
"projects"."build_timeout",
"projects"."mirror_trigger_builds",
"projects"."pending_delete",
"projects"."public_builds",
"projects"."last_repository_check_failed",
"projects"."last_repository_check_at",
"projects"."only_allow_merge_if_pipeline_succeeds",
"projects"."has_external_issue_tracker",
"projects"."repository_storage",
"projects"."repository_read_only",
"projects"."request_access_enabled",
"projects"."has_external_wiki",
"projects"."ci_config_path",
"projects"."lfs_enabled",
"projects"."description_html",
"projects"."only_allow_merge_if_all_discussions_are_resolved",
"projects"."repository_size_limit",
"projects"."printing_merge_request_link_enabled",
"projects"."auto_cancel_pending_pipelines",
"projects"."service_desk_enabled",
"projects"."cached_markdown_version",
"projects"."delete_error",
"projects"."last_repository_updated_at",
"projects"."disable_overriding_approvers_per_merge_request",
"projects"."storage_version",
"projects"."resolve_outdated_diff_discussions",
"projects"."remote_mirror_available_overridden",
"projects"."only_mirror_protected_branches",
"projects"."pull_mirror_available_overridden",
"projects"."jobs_cache_index",
"projects"."external_authorization_classification_label",
"projects"."mirror_overwrites_diverged_branches",
"projects"."pages_https_only",
"projects"."external_webhook_token",
"projects"."packages_enabled",
"projects"."merge_requests_author_approval",
"projects"."pool_repository_id",
"projects"."runners_token_encrypted",
"projects"."bfg_object_map",
"projects"."detected_repository_languages",
"projects"."merge_requests_disable_committers_approval",
"projects"."require_password_to_approve",
"projects"."max_pages_size",
"projects"."max_artifacts_size",
"projects"."pull_mirror_branch_prefix",
"projects"."remove_source_branch_after_merge",
"projects"."marked_for_deletion_at",
"projects"."marked_for_deletion_by_user_id",
"projects"."autoclose_referenced_issues",
"projects"."suggestion_commit_message",
"projects"."project_namespace_id",
"projects"."hidden",
"projects"."organization_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
UNNEST(
COALESCE(
(
SELECT
ids
FROM
(
SELECT
"namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM
"namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970
) cached_query
),
(
SELECT
ids
FROM
(
SELECT
ARRAY_AGG("namespaces"."id") AS ids
FROM
(
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1) ] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{9970}')
)
) namespaces
) consistent_query
)
)
) AS namespaces(id)
)
AND EXISTS (
SELECT
"project_compliance_framework_settings".*
FROM
"project_compliance_framework_settings"
WHERE
"project_compliance_framework_settings"."framework_id" = 1019512
AND (project_id = projects.id)
)
AND EXISTS (
SELECT
"project_compliance_framework_settings".*
FROM
"project_compliance_framework_settings"
WHERE
"project_compliance_framework_settings"."framework_id" = 1018575
AND (project_id = projects.id)
)
ORDER BY
"projects"."id" DESC
LIMIT
101
;
Query 2: Filter projects without given frameworks
Query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/30675/commands/95234
Click to expand query
SELECT
"projects"."id",
"projects"."name",
"projects"."path",
"projects"."description",
"projects"."created_at",
"projects"."updated_at",
"projects"."creator_id",
"projects"."namespace_id",
"projects"."last_activity_at",
"projects"."import_url",
"projects"."visibility_level",
"projects"."archived",
"projects"."avatar",
"projects"."merge_requests_template",
"projects"."star_count",
"projects"."merge_requests_rebase_enabled",
"projects"."import_type",
"projects"."import_source",
"projects"."approvals_before_merge",
"projects"."reset_approvals_on_push",
"projects"."merge_requests_ff_only_enabled",
"projects"."issues_template",
"projects"."mirror",
"projects"."mirror_last_update_at",
"projects"."mirror_last_successful_update_at",
"projects"."mirror_user_id",
"projects"."shared_runners_enabled",
"projects"."runners_token",
"projects"."build_allow_git_fetch",
"projects"."build_timeout",
"projects"."mirror_trigger_builds",
"projects"."pending_delete",
"projects"."public_builds",
"projects"."last_repository_check_failed",
"projects"."last_repository_check_at",
"projects"."only_allow_merge_if_pipeline_succeeds",
"projects"."has_external_issue_tracker",
"projects"."repository_storage",
"projects"."repository_read_only",
"projects"."request_access_enabled",
"projects"."has_external_wiki",
"projects"."ci_config_path",
"projects"."lfs_enabled",
"projects"."description_html",
"projects"."only_allow_merge_if_all_discussions_are_resolved",
"projects"."repository_size_limit",
"projects"."printing_merge_request_link_enabled",
"projects"."auto_cancel_pending_pipelines",
"projects"."service_desk_enabled",
"projects"."cached_markdown_version",
"projects"."delete_error",
"projects"."last_repository_updated_at",
"projects"."disable_overriding_approvers_per_merge_request",
"projects"."storage_version",
"projects"."resolve_outdated_diff_discussions",
"projects"."remote_mirror_available_overridden",
"projects"."only_mirror_protected_branches",
"projects"."pull_mirror_available_overridden",
"projects"."jobs_cache_index",
"projects"."external_authorization_classification_label",
"projects"."mirror_overwrites_diverged_branches",
"projects"."pages_https_only",
"projects"."external_webhook_token",
"projects"."packages_enabled",
"projects"."merge_requests_author_approval",
"projects"."pool_repository_id",
"projects"."runners_token_encrypted",
"projects"."bfg_object_map",
"projects"."detected_repository_languages",
"projects"."merge_requests_disable_committers_approval",
"projects"."require_password_to_approve",
"projects"."max_pages_size",
"projects"."max_artifacts_size",
"projects"."pull_mirror_branch_prefix",
"projects"."remove_source_branch_after_merge",
"projects"."marked_for_deletion_at",
"projects"."marked_for_deletion_by_user_id",
"projects"."autoclose_referenced_issues",
"projects"."suggestion_commit_message",
"projects"."project_namespace_id",
"projects"."hidden",
"projects"."organization_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
UNNEST(
COALESCE(
(
SELECT
ids
FROM
(
SELECT
"namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM
"namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970
) cached_query
),
(
SELECT
ids
FROM
(
SELECT
ARRAY_AGG("namespaces"."id") AS ids
FROM
(
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1) ] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{9970}')
)
) namespaces
) consistent_query
)
)
) AS namespaces(id)
)
AND NOT (
EXISTS (
SELECT
"project_compliance_framework_settings".*
FROM
"project_compliance_framework_settings"
WHERE
"project_compliance_framework_settings"."framework_id" IN (1019512, 1018575)
AND (project_id = projects.id)
)
)
ORDER BY
"projects"."id" DESC
LIMIT
101;
How to set up and validate locally
- You need to have a group with GitLab Ultimate licence for this.
- Create several compliance frameworks for the group by following steps mentioned in https://docs.gitlab.com/ee/user/compliance/compliance_center/compliance_frameworks_report.html#create-a-new-compliance-framework.
- Make sure the group has several projects in it.
- Now, add compliance frameworks to the projects by following steps mentioned in https://docs.gitlab.com/ee/user/compliance/compliance_center/compliance_projects_report.html#apply-a-compliance-framework-to-projects-in-a-group.
- Open graphql explorer by visiting http://gitlab.localdev:3000/-/graphql-explorer.
- Run following query to list down projects of the group by filtering with compliance framework ids, you should note that it should list down only those projects which are associated with all the given framework ids:
query group {
group(fullPath: "<group_full_path>") {
id
name
projects(
includeSubgroups: true,
complianceFrameworkFilters: {
ids: ["gid://gitlab/ComplianceManagement::Framework/<framework1_id>",
"gid://gitlab/ComplianceManagement::Framework/<framework2_id>"]
}
) {
nodes {
id
name
}
}
}
}
- For negating the compliance frameworks, you can run following query, it should provide a list of projects of the group which should not be associated with any of the given framework ids
query group {
group(fullPath: "<group_full_path>") {
id
name
projects(
includeSubgroups: true,
complianceFrameworkFilters: {
not: {
ids: ["gid://gitlab/ComplianceManagement::Framework/<framework1_id>",
"gid://gitlab/ComplianceManagement::Framework/<framework2_id>"]
}
}
) {
nodes {
id
name
}
}
}
}
Related to #473696 (closed)