Add filter by ref to project jobs resolver

What does this MR do and why?

For #367294

This MR modifies the parameters that you can provide when getting the list of jobs for a project via GraphQL: it adds a ref filter that should allow you to get only the jobs that ran for that ref.

Screenshots or screen recordings

Screenshots from GraphiQL

jobs query without ref	jobs query with ref

How to set up and validate locally

1. git checkout 367294-project-jobs-resolver-filter-by-ref
2. find/make/import a project with a pipeline with a job that generates artifacts

you can use this CI yaml to generate some sample artifacts:

# .gitlab-ci.yml

potato:
  stage: build
  script:
    - echo 'potato' >> potato.txt
  artifacts:
    expose_as: 'potato'
    paths: ['potato.txt']

tomato:
  stage: build
  script:
    - echo 'tomato' >> tomato.txt
  artifacts:
    expose_as: 'tomato'
    paths: ['tomato.txt']

pineapple:
  stage: build
  script:
    - echo 'pineapple' >> pineapple.txt
  artifacts:
    expose_as: 'pineapple'
    paths: ['pineapple.txt']

apple:
  stage: build
  script:
    - echo 'apple' >> apple.txt
  artifacts:
    expose_as: 'apple'
    paths: ['apple.txt']

toblerone:
  stage: build
  script:
    - echo 'toblerone' >> toblerone.txt
  artifacts:
    expose_as: 'toblerone'
    paths: ['toblerone.txt']

3. run a pipeline for one branch in that project - for this you'll need to set up a local runner for GDK or enable runners in gitpod
4. run a pipeline for another branch
5. using the GraphQL explorer (http://gdk.test:3000/-/graphql-explorer), query for a project's jobs by ref:

query getJobArtifacts {
  project(fullPath: "path/to/project") {  # substitute the full path of your project
    jobs(ref: "main") {                   # substitute the names of your branches
      nodes {
        id
        status
        name
        refName
      }
    }
  }
}

6. when a ref is provided, only jobs that ran for that ref should be returned
7. when a ref is not provided, jobs for any ref should be returned

Database details

Raw SQL

If I use this as the execute method:

    def execute
      builds = init_collection.order_id_desc
      builds = filter_by_with_artifacts(builds)
      builds = filter_by_ref(builds)
      builds = filter_by_scope(builds)
      puts builds.to_sql
      builds
    rescue Gitlab::Access::AccessDeniedError
      type.none
    end

... and run bundle exec rspec spec/finders/ci/jobs_finder_spec.rb:85 (and format the SQL it outputs with pgFormatter), then I get:

SELECT
    "ci_builds".*
FROM
    "ci_builds"
WHERE
    "ci_builds"."type" = 'Ci::Build'
    AND "ci_builds"."project_id" = 23
    AND ("ci_builds"."status" NOT IN ('created'))
    AND "ci_builds"."ref" = 'dev'
ORDER BY
    "ci_builds"."id" DESC

... and as a control, if I comment out builds = filter_by_ref(builds) in the execute method and run it again, I get:

SELECT
    "ci_builds".*
FROM
    "ci_builds"
WHERE
    "ci_builds"."type" = 'Ci::Build'
    AND "ci_builds"."project_id" = 29
    AND ("ci_builds"."status" NOT IN ('created'))
ORDER BY
    "ci_builds"."id" DESC

Query plans

Adding in the gitlab-org/gitlab project id (278964) and the ref name master for testing gives us these two (with and without "ci_builds"."ref" = 'master'):

query	link to plan
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."project_id" = 278964 AND ("ci_builds"."status" NOT IN ('created')) AND "ci_builds"."ref" = 'master' ORDER BY "ci_builds"."id" DESC	https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13900/commands/48651
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."project_id" = 278964 AND ("ci_builds"."status" NOT IN ('created')) ORDER BY "ci_builds"."id" DESC	https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13900/commands/48652

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.

app/finders/ci/jobs_finder.rb

@@ -17,6 +17,7 @@ def initialize(current_user:, pipeline: nil, project: nil, runner: nil, params:
     def execute
       builds = init_collection.order_id_desc
       builds = filter_by_with_artifacts(builds)
+      builds = filter_by_ref(builds) if project.present?
       filter_by_scope(builds)
     rescue Gitlab::Access::AccessDeniedError
       type.none
@@ -81,6 +82,12 @@ def filter_by_with_artifacts(builds)
       end
     end
 
+    def filter_by_ref(builds)
+      return builds unless params[:ref].present?
+
+      builds.with_ref(params[:ref])
+    end
+
     def filter_by_statuses!(builds)
       unknown_statuses = params[:scope] - ::CommitStatus::AVAILABLE_STATUSES
       raise ArgumentError, 'Scope contains invalid value(s)' unless unknown_statuses.empty?

[Albert]

@mfluharty could you try creating a scope Ci::Build.with_ref(ref) and test that scope in isolation? Maybe it'll help to unearth the problem.

[Miranda Fluharty]

Sure - scope created in a new commit