fix: Drop unnecessary x-join between orgs and project sbom occurrences
What does this MR do and why?
Fixes Resolve sbom_occurrences cross join issues to o... (#477829)
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 Review
Before
SELECT "sbom_occurrences".*
FROM "sbom_occurrences"
INNER JOIN "projects"
ON "sbom_occurrences"."project_id" = "projects"."id"
WHERE "projects"."organization_id" = 1
AND "projects"."archived" = FALSE
After
SELECT all organization project_ids
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."organization_id" = 1
AND "projects"."archived" = FALSE
SELECT sbom_occurrences by project_ids
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32972/commands/101604
SELECT "sbom_occurrences".*
FROM "sbom_occurrences"
WHERE "sbom_occurrences"."project_id" IN (19, 23)
ORDER BY "sbom_occurrences"."id" DESC
Edited by Lucas Charles