From b817ba029ffde18e09ed6b4f994faec10737e9bb Mon Sep 17 00:00:00 2001
From: lukasz-jarocki-sonarsource <lukasz.jarocki@sonarsource.com>
Date: Thu, 28 Mar 2024 16:40:24 +0100
Subject: [PATCH] SONAR-19324 rely on project_branches instead of components
 when calling api/projects/search endpoint

---
 .../org/sonar/db/component/SnapshotMapper.xml | 74 +++++++------------
 1 file changed, 28 insertions(+), 46 deletions(-)

diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml
index f64fbfe2139..a35d3a97399 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/SnapshotMapper.xml
@@ -54,52 +54,34 @@
       and coalesce(pb.project_uuid, p.branch_uuid) = #{projectUuid,jdbcType=VARCHAR}
   </select>
 
-  <select id="selectLastAnalysisDateByProjectUuids" resultType="org.sonar.db.component.ProjectLastAnalysisDateDto">
-    select
-      result_with_duplicates.project_uuid as project_uuid,
-      max(result_with_duplicates.last_analysis_date) as last_analysis_date
-    FROM
-      (
-      <!-- cases of an analysis of a project or app, with entries in project_branches -->
-      SELECT project_uuid, last_analysis_date
-      FROM (
-        SELECT
-          COALESCE(pb.project_uuid, c.branch_uuid) AS project_uuid,
-          s.created_at AS last_analysis_date
-        FROM snapshots s
-          INNER JOIN components c ON s.root_component_uuid = c.branch_uuid
-          LEFT JOIN project_branches pb ON pb.uuid = c.branch_uuid
-        WHERE
-          s.islast = ${_true}
-          AND pb.uuid IS NOT NULL
-          AND pb.project_uuid IN
-          <foreach collection="projectUuids" item="projectUuid" separator="," open="(" close=")">
-            #{projectUuid,jdbcType=VARCHAR}
-          </foreach>
-      ) project_case
-
-      UNION
-
-      <!-- cases of an analysis of a portfolio, where there are no branches -->
-      SELECT project_uuid, last_analysis_date
-      FROM (
-        SELECT
-          COALESCE(pb.project_uuid, c.branch_uuid) AS project_uuid,
-          s.created_at AS last_analysis_date
-        FROM snapshots s
-          INNER JOIN components c ON s.root_component_uuid = c.branch_uuid
-          LEFT JOIN project_branches pb ON pb.uuid = c.branch_uuid
-        WHERE
-          s.islast = ${_true}
-          AND pb.branch_type IS NULL
-          AND pb.uuid IS NULL
-          AND c.branch_uuid IN
-          <foreach collection="projectUuids" item="projectUuid" separator="," open="(" close=")">
-            #{projectUuid,jdbcType=VARCHAR}
-          </foreach>
-      ) portfolio_case
-    ) result_with_duplicates
-    GROUP BY project_uuid
+   <select id="selectLastAnalysisDateByProjectUuids" resultType="org.sonar.db.component.ProjectLastAnalysisDateDto">
+      select
+        pb.project_uuid as project_uuid,
+        max(s.created_at) as last_analysis_date
+      from
+        snapshots s
+      inner join project_branches pb on
+        pb.uuid = s.root_component_uuid
+      where
+        s.islast = ${_true} and pb.project_uuid in
+        <foreach collection="projectUuids" item="projectUuid" separator="," open="(" close=")">
+          #{projectUuid,jdbcType=VARCHAR}
+        </foreach>
+      group by pb.project_uuid
+      union
+      select
+        p.uuid as project_uuid,
+        max(s.created_at) as last_analysis_date
+      from
+        snapshots s
+      inner join portfolios p on
+        p.uuid = s.root_component_uuid
+      where
+        s.islast = ${_true} and p.uuid in
+        <foreach collection="projectUuids" item="projectUuid" separator="," open="(" close=")">
+          #{projectUuid,jdbcType=VARCHAR}
+        </foreach>
+      group by p.uuid
   </select>
 
   <select id="selectLastSnapshotByRootComponentUuid" resultType="Snapshot">
-- 
2.39.5