WIP: Refactor Group#all_projects to use recursive CTE
What does this MR do?
Currently Group#all_projects
will run the following query for the gitlab-org group
SELECT projects.*
FROM projects
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = ‘Project’
WHERE (rs.path LIKE ‘gitlab-org/%’)
Plans - (somewhat) cold cache and hot cache.
This MR changes the implementation to use recursive CTE (given since 12.1 we no longer have to support MySQL). The new query is this:
SELECT "projects".*
FROM
(WITH RECURSIVE groups AS
(SELECT id,
parent_id
FROM namespaces
WHERE id = 9970
UNION SELECT namespaces.id,
namespaces.parent_id
FROM namespaces
INNER JOIN groups ON groups.id = namespaces.parent_id) SELECT projects.*
FROM groups
JOIN projects ON projects.namespace_id = groups.id) AS projects
Plans - cold cache and hot cache.
Edited by Krasimir Angelov