Respect relation scope in latest_pipeline_per_commit

What does this MR do?

Solves #291092 (closed)

Ci::Pipeline#latest_pipeline_per_commit gets the latest pipeline per commits.

We currently use a LEFT OUTER JOIN on the ci_pipelines table with itself. If this relation is scoped prior to invoking this method, the scope isn't respected.

An example of where this caused issues was if #latest_pipeline_per_commit was invoked like project.ci_pipelines.latest_pipeline_per_commit - this means that the child pipeline would be the latest, but the scope filters it out so we'd get nothing instead of the latest pipeline that's not a child pipeline.

---

Example SQL query of previous implementation

SELECT "ci_pipelines".*
FROM "ci_pipelines"
    LEFT OUTER JOIN "ci_pipelines" "ci_pipelines_2" ON "ci_pipelines"."sha" = "ci_pipelines_2"."sha"
        AND "ci_pipelines"."project_id" = "ci_pipelines_2"."project_id"
        AND "ci_pipelines"."id" < "ci_pipelines_2"."id"
        AND "ci_pipelines"."source" IN (NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
        AND "ci_pipelines_2"."source" IN (NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
        AND "ci_pipelines"."ref" = "ci_pipelines_2"."ref"
WHERE "ci_pipelines"."project_id" = 22
    AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL)
    AND "ci_pipelines"."sha" IN ('2cf512ed40fddcdb1ecfc9c996e9ffa8a2cf913a', '10344382ba3574f8e5837f5bfde1
711c281e2282', '1d297a359694eca52c6669ac3c18bc0031d2b249', '0c076feb7dc5ee591577ff38f15fdbf253e88b58')
    AND "ci_pipelines_2"."id" IS NULL
    AND "ci_pipelines"."source" IN (NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
    AND "ci_pipelines"."ref" = 'master'

Fortunately Postgres DISTINCT ON window function can fix all the problems, and reduces query time from 9.678ms to 5.552ms.

Example SQL query for new implementation:

SELECT DISTINCT ON ("sha") * FROM "ci_pipelines"
WHERE "ci_pipelines"."project_id" = 22
AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL)
AND "ci_pipelines"."sha" IN ('2cf512ed40fddcdb1ecfc9c996e9ffa8a2cf913a', '10344382ba3574f8e5837f5bfde1711c281e2282', '1d297a359694eca52c6669ac3c18bc0031d2b249', '0c076feb7dc5ee591577ff38f15fdbf253e88b58')
AND "ci_pipelines"."ref" = 'master'
ORDER BY "ci_pipelines"."sha", "ci_pipelines"."id" DESC;

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • I have not included a changelog entry because _____.
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content