Fetch first available compliance pipeline when multiple frameworks

What does this MR do and why?

This MR fixes a bug and adds one feature to the compliance pipeline feature.

Bug:

Earlier we were fetching list of associated compliance frameworks with the project and getting the compliance pipeline path of the first framework as per the creation of that framework rather than the order of association of that framework with the project. Explained in detail in #483035 (comment 2102792041). Now, we are fetching the associated frameworks in the order they were associated with the project.

Feature:

Earlier we were just checking for the compliance pipeline for the first framework associated with the project and if that is nil and even though other associated frameworks has compliance pipeline we were returning nil. Now, we will be checking all the associated frameworks in the order of association and will be returning the first available compliance pipeline path.

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.

Screenshots or screen recordings

Bug

#483035 (comment 2102792041)

After bug fix

CompliancePipelineMultipleFrameworks720

Database

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31887/commands/98614

Query:

SELECT 
  "pipeline_configuration_full_path" 
FROM 
  "project_compliance_framework_settings" 
  INNER JOIN "compliance_management_frameworks" "compliance_management_framework" ON "compliance_management_framework"."id" = "project_compliance_framework_settings"."framework_id" 
WHERE 
  "project_compliance_framework_settings"."project_id" = 60139275 
  AND "compliance_management_framework"."pipeline_configuration_full_path" IS NOT NULL 
ORDER BY 
  "project_compliance_framework_settings"."id" ASC 
LIMIT 
  1;

How to set up and validate locally

1. For testing this, you need to have a group with Ultimate licence.
2. Create 2 projects under the group, one with name pipeline-project and other with name test-project.
3. Under pipeline-project, add .gitlab-ci.yml file with following contents, this will be your compliance pipeline config:

variables:
    global_var: wololo
image: alpine:latest

job1:
    script: echo this is a test injecting a variable

include:
    project: $CI_PROJECT_PATH
    ref: main
    file: '.gitlab-ci.yml'

4. Under test-project, add .gitlab-ci.yml with following contents, here we are adding 2 jobs and one is consuming a global variable being assigned in compliance pipeline.

other_job:
    script: echo "this is the actual project"

yet_another_job:
    script: echo "$global_var was injected"

5. Now visit the compliance center of the group by visiting Secure -> Compliance center in the left sidebar and then open the Frameworks tab.
6. Create at least 2 compliance frameworks, one with pipeline and one without pipeline. For configuring pipeline in the framework, you can add .gitlab-ci.yml@<group_name>/pipeline-project in the compliance pipeline configuration field as mentioned in https://docs.gitlab.com/ee/user/group/compliance_pipelines.html#configure-a-compliance-pipeline.
7. Visit the Projects tab in compliance center opened in step 5.
8. Assign the framework with pipeline to the test-project and then run the pipeline for test-project, the pipeline should have the job introduced by compliance pipeline, in this case job with name job1.
9. Now assign the framework without pipeline to the project in addition to the existing framework and run the pipeline for test-project, it should have the job job1.
10. You can try with assigning framework without pipeline first and then add the framework with pipeline and again the job1 should run in pipeline.
11. You can also try with multiple frameworks with pipelines and so on.

Related to #483035 (closed)