From 1233df6661da5509b526411f0adca64673799031 Mon Sep 17 00:00:00 2001
From: Duarte Meneses <duarte.meneses@sonarsource.com>
Date: Thu, 15 Jul 2021 13:35:54 -0500
Subject: [PATCH] SONAR-11411 Detection of inactive projects should take into
 account branches

---
 .../sonar/db/component/ComponentQuery.java    | 17 +++-
 .../component/ProjectLastAnalysisDateDto.java | 38 +++++++++
 .../org/sonar/db/component/SnapshotDao.java   | 17 ++++
 .../sonar/db/component/SnapshotMapper.java    |  4 +
 .../sonar/db/component/ComponentMapper.xml    | 28 ++++++-
 .../org/sonar/db/component/SnapshotMapper.xml | 23 +++++-
 .../sonar/db/component/ComponentDaoTest.java  | 49 +++++++++++-
 .../sonar/db/component/SnapshotDaoTest.java   | 78 ++++++++++++++++---
 .../server/project/ws/BulkDeleteAction.java   |  2 +-
 .../sonar/server/project/ws/SearchAction.java | 21 +++--
 .../project/ws/BulkDeleteActionTest.java      |  4 +-
 .../server/project/ws/SearchActionTest.java   | 29 +++++--
 12 files changed, 276 insertions(+), 34 deletions(-)
 create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/component/ProjectLastAnalysisDateDto.java

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java
index 437de943e97..8324a273cb2 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentQuery.java
@@ -40,6 +40,7 @@ public class ComponentQuery {
   private final Long analyzedBefore;
   private final Long anyBranchAnalyzedBefore;
   private final Long anyBranchAnalyzedAfter;
+  private final Long allBranchesAnalyzedBefore;
   private final Date createdAfter;
   private final boolean onProvisionedOnly;
 
@@ -53,6 +54,7 @@ public class ComponentQuery {
     this.analyzedBefore = builder.analyzedBefore;
     this.anyBranchAnalyzedBefore = builder.anyBranchAnalyzedBefore;
     this.anyBranchAnalyzedAfter = builder.anyBranchAnalyzedAfter;
+    this.allBranchesAnalyzedBefore = builder.allBranchesAnalyzedBefore;
     this.createdAfter = builder.createdAfter;
     this.onProvisionedOnly = builder.onProvisionedOnly;
   }
@@ -111,6 +113,11 @@ public class ComponentQuery {
     return anyBranchAnalyzedAfter;
   }
 
+  @CheckForNull
+  public Long getAllBranchesAnalyzedBefore() {
+    return allBranchesAnalyzedBefore;
+  }
+
   @CheckForNull
   public Date getCreatedAfter() {
     return createdAfter;
@@ -139,6 +146,7 @@ public class ComponentQuery {
     private Long analyzedBefore;
     private Long anyBranchAnalyzedBefore;
     private Long anyBranchAnalyzedAfter;
+    private Long allBranchesAnalyzedBefore;
     private Date createdAfter;
     private boolean onProvisionedOnly = false;
 
@@ -180,8 +188,8 @@ public class ComponentQuery {
       return this;
     }
 
-    public Builder setAnyBranchAnalyzedBefore(@Nullable Long l) {
-      this.anyBranchAnalyzedBefore = l;
+    public Builder setAllBranchesAnalyzedBefore(@Nullable Long l) {
+      this.allBranchesAnalyzedBefore = l;
       return this;
     }
 
@@ -195,6 +203,11 @@ public class ComponentQuery {
       return this;
     }
 
+    public Builder setAnyBranchAnalyzedBefore(@Nullable Long l) {
+      this.anyBranchAnalyzedBefore = l;
+      return this;
+    }
+
     public Builder setCreatedAfter(@Nullable Date l) {
       this.createdAfter = l;
       return this;
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ProjectLastAnalysisDateDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ProjectLastAnalysisDateDto.java
new file mode 100644
index 00000000000..ee6da259363
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ProjectLastAnalysisDateDto.java
@@ -0,0 +1,38 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.component;
+
+public class ProjectLastAnalysisDateDto {
+  private final String projectUuid;
+  private final Long date;
+
+  public ProjectLastAnalysisDateDto(String projectUuid, Long date) {
+    this.projectUuid = projectUuid;
+    this.date = date;
+  }
+
+  public String getProjectUuid() {
+    return projectUuid;
+  }
+
+  public Long getDate() {
+    return date;
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotDao.java
index 79f358ac4d5..ce45f1f347a 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotDao.java
@@ -59,6 +59,23 @@ public class SnapshotDao implements Dao {
     return Optional.ofNullable(mapper(session).selectLastSnapshotByComponentUuid(componentUuid));
   }
 
+  /**
+   * returns the last analysis of any branch of a project
+   */
+  public Optional<Long> selectLastAnalysisDateByProject(DbSession session, String projectUuid) {
+    return Optional.ofNullable(mapper(session).selectLastAnalysisDateByProject(projectUuid));
+  }
+
+  /**
+   * returns the last analysis of any branch for each existing project
+   */
+  public List<ProjectLastAnalysisDateDto> selectLastAnalysisDateByProjects(DbSession session, Collection<String> projectUuids) {
+    if (projectUuids.isEmpty()) {
+      return Collections.emptyList();
+    }
+    return mapper(session).selectLastAnalysisDateByProjects(projectUuids);
+  }
+
   public Optional<SnapshotDto> selectLastAnalysisByRootComponentUuid(DbSession session, String componentUuid) {
     return Optional.ofNullable(mapper(session).selectLastSnapshotByRootComponentUuid(componentUuid));
   }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotMapper.java
index 04044f4d16a..fdccc8f82a0 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/SnapshotMapper.java
@@ -54,4 +54,8 @@ public interface SnapshotMapper {
 
   List<SnapshotDto> selectFinishedByComponentUuidsAndFromDates(@Param("componentUuidFromDatePairs") List<ComponentUuidFromDatePair> pairs);
 
+  @CheckForNull
+  Long selectLastAnalysisDateByProject(String projectUuid);
+
+  List<ProjectLastAnalysisDateDto> selectLastAnalysisDateByProjects(@Param("projectUuids") Collection<String> projectUuids);
 }
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml
index 87d255caefb..97198772fbc 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml
@@ -311,7 +311,7 @@
       <if test="query.anyBranchAnalyzedAfter != null">
         and (
           exists(
-            -- branches of projects
+            -- branches of projects and applications
             select 1 from snapshots s
             inner join project_branches pb on s.component_uuid = pb.uuid
             where pb.project_uuid = p.uuid
@@ -320,7 +320,7 @@
             and s.created_at &gt;= #{query.anyBranchAnalyzedAfter,jdbcType=BIGINT}
           )
           or exists (
-            -- applications, portfolios
+            -- portfolios
             select 1 from snapshots s
             where s.component_uuid = p.uuid
             and s.status='P'
@@ -332,7 +332,7 @@
       <if test="query.anyBranchAnalyzedBefore != null">
         and (
           exists(
-          -- branches of projects
+          -- branches of projects and applications
           select 1 from snapshots s
           inner join project_branches pb on s.component_uuid = pb.uuid
           where pb.project_uuid = p.uuid
@@ -341,7 +341,7 @@
           and s.created_at &lt; #{query.anyBranchAnalyzedBefore,jdbcType=BIGINT}
           )
           or exists (
-          -- applications, portfolios
+          -- portfolios
           select 1 from snapshots s
           where s.component_uuid = p.uuid
           and s.status='P'
@@ -350,6 +350,26 @@
           )
         )
       </if>
+      <if test="query.allBranchesAnalyzedBefore != null">
+        and
+        (
+            (select max(s.created_at) from snapshots s
+            inner join project_branches pb on s.component_uuid = pb.uuid
+            where pb.project_uuid = p.uuid
+            and s.status='P'
+            and s.islast = ${_true}
+            ) &lt; #{query.allBranchesAnalyzedBefore,jdbcType=BIGINT}
+          or
+            exists (
+            -- portfolios
+            select 1 from snapshots s
+            where s.component_uuid = p.uuid
+            and p.qualifier = 'VW'
+            and s.status='P'
+            and s.islast = ${_true}
+            and s.created_at &lt; #{query.allBranchesAnalyzedBefore,jdbcType=BIGINT})
+        )
+      </if>
     <if test="query.createdAfter != null">
       and p.created_at &gt;= #{query.createdAfter,jdbcType=TIMESTAMP}
     </if>
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 aa53f2e4b82..577547b007a 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
@@ -4,7 +4,7 @@
 
   <sql id="snapshotColumns">
     s.uuid as uuid,
-    s.component_uuid as componentUuId,
+    s.component_uuid as componentUuid,
     s.created_at as createdAt,
     s.build_date as buildDate,
     s.status as status,
@@ -44,6 +44,27 @@
       and p.uuid = #{componentUuid,jdbcType=VARCHAR}
   </select>
 
+  <select id="selectLastAnalysisDateByProject" resultType="long">
+    select max(s.created_at)
+    from snapshots s
+    inner join components p on s.component_uuid = p.project_uuid
+    where
+      s.islast=${_true}
+      and coalesce(p.main_branch_project_uuid, p.project_uuid) = #{projectUuid,jdbcType=VARCHAR}
+  </select>
+
+  <select id="selectLastAnalysisDateByProjects" resultType="org.sonar.db.component.ProjectLastAnalysisDateDto">
+    select coalesce(p.main_branch_project_uuid, p.project_uuid) as project_uuid, max(s.created_at) as last_analysis_date
+    from snapshots s
+    inner join components p on s.component_uuid = p.project_uuid
+    where s.islast=${_true}
+    and coalesce(p.main_branch_project_uuid, p.project_uuid) in
+      <foreach collection="projectUuids" item="projectUuid" separator="," open="(" close=")">
+        #{projectUuid,jdbcType=VARCHAR}
+      </foreach>
+    group by coalesce(p.main_branch_project_uuid, p.project_uuid)
+  </select>
+
   <select id="selectLastSnapshotByRootComponentUuid" resultType="Snapshot">
     select <include refid="snapshotColumns" />
     from snapshots s
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java
index baae16d1a85..83c3124d05d 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java
@@ -20,7 +20,6 @@
 package org.sonar.db.component;
 
 import com.google.common.collect.ImmutableMap;
-import com.google.common.collect.ImmutableSet;
 import com.tngtech.java.junit.dataprovider.DataProvider;
 import com.tngtech.java.junit.dataprovider.DataProviderRunner;
 import com.tngtech.java.junit.dataprovider.UseDataProvider;
@@ -1484,6 +1483,44 @@ public class ComponentDaoTest {
       .containsExactlyInAnyOrder(oldProject.uuid(), recentProject.uuid());
   }
 
+  @Test
+  public void selectByQuery_filter_last_analysisof_all_branches_before() {
+    long aLongTimeAgo = 1_000_000_000L;
+    long recentTime = 3_000_000_000L;
+    // project with only a non-main and old analyzed branch
+    ComponentDto oldProject = db.components().insertPublicProject();
+    ComponentDto oldProjectBranch = db.components().insertProjectBranch(oldProject, newBranchDto(oldProject).setBranchType(BRANCH));
+    db.components().insertSnapshot(oldProjectBranch, s -> s.setLast(true).setCreatedAt(aLongTimeAgo));
+
+    // project with only a old main branch and a recent non-main branch
+    ComponentDto recentProject = db.components().insertPublicProject();
+    ComponentDto recentProjectBranch = db.components().insertProjectBranch(recentProject, newBranchDto(recentProject).setBranchType(BRANCH));
+    db.components().insertSnapshot(recentProjectBranch, s -> s.setCreatedAt(recentTime).setLast(true));
+    db.components().insertSnapshot(recentProjectBranch, s -> s.setCreatedAt(aLongTimeAgo).setLast(false));
+
+    assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(recentTime + 1_000L))).containsOnly(oldProject.uuid(), recentProject.uuid());
+    assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(aLongTimeAgo))).isEmpty();
+    assertThat(selectProjectUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(aLongTimeAgo + 1_000L))).containsOnly(oldProject.uuid());
+  }
+
+  @Test
+  public void selectByQuery_filter_last_analysisof_all_branches_before_for_portfolios() {
+    long aLongTimeAgo = 1_000_000_000L;
+    long recentTime = 3_000_000_000L;
+
+    // old portfolio
+    ComponentDto oldPortfolio = db.components().insertPublicPortfolio();
+    db.components().insertSnapshot(oldPortfolio, s -> s.setLast(true).setCreatedAt(aLongTimeAgo));
+
+    // recent portfolio
+    ComponentDto recentPortfolio = db.components().insertPublicPortfolio();
+    db.components().insertSnapshot(recentPortfolio, s -> s.setCreatedAt(recentTime).setLast(true));
+
+    assertThat(selectPortfolioUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(recentTime + 1_000_000L))).containsOnly(oldPortfolio.uuid(), recentPortfolio.uuid());
+    assertThat(selectPortfolioUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(aLongTimeAgo))).isEmpty();
+    assertThat(selectPortfolioUuidsByQuery(q -> q.setAllBranchesAnalyzedBefore(aLongTimeAgo + 1_000L))).containsOnly(oldPortfolio.uuid());
+  }
+
   @Test
   public void selectByQuery_filter_created_at() {
     ComponentDto project1 = db.components().insertPrivateProject(p -> p.setCreatedAt(parseDate("2018-02-01")));
@@ -1499,7 +1536,15 @@ public class ComponentDaoTest {
   }
 
   private List<String> selectProjectUuidsByQuery(Consumer<ComponentQuery.Builder> query) {
-    ComponentQuery.Builder builder = ComponentQuery.builder().setQualifiers(PROJECT);
+    return selectUuidsByQuery(PROJECT, query);
+  }
+
+  private List<String> selectPortfolioUuidsByQuery(Consumer<ComponentQuery.Builder> query) {
+    return selectUuidsByQuery(VIEW, query);
+  }
+
+  private List<String> selectUuidsByQuery(String qualifier, Consumer<ComponentQuery.Builder> query) {
+    ComponentQuery.Builder builder = ComponentQuery.builder().setQualifiers(qualifier);
     query.accept(builder);
     return underTest.selectByQuery(dbSession, builder.build(), 0, 5)
       .stream()
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/component/SnapshotDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/component/SnapshotDaoTest.java
index 03bb646f720..10c85739995 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/component/SnapshotDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/component/SnapshotDaoTest.java
@@ -51,6 +51,7 @@ import static java.util.stream.Collectors.toList;
 import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric;
 import static org.apache.commons.lang.math.RandomUtils.nextLong;
 import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.tuple;
 import static org.sonar.db.ce.CeActivityDto.Status.CANCELED;
 import static org.sonar.db.ce.CeActivityDto.Status.SUCCESS;
 import static org.sonar.db.component.ComponentTesting.newPrivateProjectDto;
@@ -74,7 +75,7 @@ public class SnapshotDaoTest {
   private final SnapshotDao underTest = dbClient.snapshotDao();
 
   @Test
-  public void test_selectByUuid() {
+  public void selectByUuid() {
     ComponentDto project = db.components().insertPrivateProject();
     db.components().insertSnapshot(newAnalysis(project)
       .setUuid("ABCD")
@@ -107,14 +108,14 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void selectLastSnapshotByRootComponentUuid_returns_absent_when_no_last_snapshot() {
+  public void selectLastAnalysisByRootComponentUuid_returns_absent_when_no_last_snapshot() {
     Optional<SnapshotDto> snapshot = underTest.selectLastAnalysisByRootComponentUuid(db.getSession(), "uuid_123");
 
     assertThat(snapshot).isNotPresent();
   }
 
   @Test
-  public void selectLastSnapshotByRootComponentUuid_returns_snapshot_flagged_as_last() {
+  public void selectLastAnalysisByRootComponentUuid_returns_snapshot_flagged_as_last() {
     ComponentDto project1 = db.components().insertPrivateProject();
     db.components().insertSnapshot(project1, t -> t.setLast(false));
     SnapshotDto lastSnapshot1 = db.components().insertSnapshot(project1, t -> t.setLast(true));
@@ -134,7 +135,7 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void selectLastSnapshotByRootComponentUuid_returns_absent_if_only_unprocessed_snapshots() {
+  public void selectLastAnalysisByRootComponentUuid_returns_absent_if_only_unprocessed_snapshots() {
     ComponentDto project1 = db.components().insertPrivateProject();
     db.components().insertSnapshot(project1, t -> t.setLast(false));
     db.components().insertSnapshot(project1, t -> t.setLast(false));
@@ -145,14 +146,14 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void selectLastSnapshotsByRootComponentUuids_returns_empty_list_if_empty_input() {
+  public void selectLastAnalysesByRootComponentUuids_returns_empty_list_if_empty_input() {
     List<SnapshotDto> result = underTest.selectLastAnalysesByRootComponentUuids(dbSession, emptyList());
 
     assertThat(result).isEmpty();
   }
 
   @Test
-  public void selectLastSnapshotsByRootComponentUuids_returns_snapshots_flagged_as_last() {
+  public void selectLastAnalysesByRootComponentUuids_returns_snapshots_flagged_as_last() {
     ComponentDto firstProject = db.components().insertComponent(newPrivateProjectDto("PROJECT_UUID_1"));
     dbClient.snapshotDao().insert(dbSession, newAnalysis(firstProject).setLast(false));
     SnapshotDto lastSnapshotOfFirstProject = dbClient.snapshotDao().insert(dbSession, newAnalysis(firstProject).setLast(true));
@@ -165,6 +166,63 @@ public class SnapshotDaoTest {
     assertThat(result).extracting(SnapshotDto::getUuid).containsOnly(lastSnapshotOfFirstProject.getUuid(), lastSnapshotOfSecondProject.getUuid());
   }
 
+  @Test
+  public void selectLastAnalysisDateByProject_takes_all_branches_into_account() {
+    ComponentDto firstProject = db.components().insertPrivateProject();
+    ComponentDto branch1 = db.components().insertProjectBranch(firstProject);
+    ComponentDto branch2 = db.components().insertProjectBranch(firstProject);
+
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(firstProject).setLast(false).setCreatedAt(1L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(branch1).setLast(false).setCreatedAt(2L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(branch2).setLast(false).setCreatedAt(10L));
+
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(firstProject).setLast(true).setCreatedAt(7L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(branch1).setLast(true).setCreatedAt(8L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(branch2).setLast(true).setCreatedAt(9L));
+
+    Optional<Long> date = underTest.selectLastAnalysisDateByProject(dbSession, firstProject.uuid());
+
+    assertThat(date).contains(9L);
+  }
+
+  @Test
+  public void selectLastAnalysisDateByProject_is_empty_if_no_snapshot() {
+    ComponentDto firstProject = db.components().insertPrivateProject();
+    ComponentDto branch1 = db.components().insertProjectBranch(firstProject);
+    ComponentDto branch2 = db.components().insertProjectBranch(firstProject);
+
+    assertThat(underTest.selectLastAnalysisDateByProject(dbSession, firstProject.uuid())).isEmpty();
+  }
+
+  @Test
+  public void selectLastAnalysisDateByProjects_is_empty_if_no_project_passed() {
+    assertThat(underTest.selectLastAnalysisDateByProjects(dbSession, emptyList())).isEmpty();
+  }
+
+  @Test
+  public void selectLastAnalysisDateByProjects_returns_all_existing_projects_with_a_snapshot() {
+    ComponentDto project1 = db.components().insertPrivateProject();
+
+    ComponentDto project2 = db.components().insertPrivateProject();
+    ComponentDto branch1 = db.components().insertProjectBranch(project2);
+    ComponentDto branch2 = db.components().insertProjectBranch(project2);
+
+    ComponentDto project3 = db.components().insertPrivateProject();
+
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(project1).setLast(false).setCreatedAt(2L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(project1).setLast(true).setCreatedAt(1L));
+
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(project2).setLast(true).setCreatedAt(2L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(branch2).setLast(false).setCreatedAt(5L));
+    dbClient.snapshotDao().insert(dbSession, newAnalysis(branch1).setLast(true).setCreatedAt(4L));
+
+    List<ProjectLastAnalysisDateDto> lastAnalysisByProject = underTest.selectLastAnalysisDateByProjects(dbSession,
+      List.of(project1.uuid(), project2.uuid(), project3.uuid(), "non-existing"));
+
+    assertThat(lastAnalysisByProject).extracting(ProjectLastAnalysisDateDto::getProjectUuid, ProjectLastAnalysisDateDto::getDate)
+      .containsOnly(tuple(project1.uuid(), 1L), tuple(project2.uuid(), 4L));
+  }
+
   @Test
   public void selectAnalysesByQuery_all() {
     Random random = new Random();
@@ -256,7 +314,7 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void select_first_snapshots() {
+  public void selectOldestSnapshot() {
     ComponentDto project = db.components().insertPrivateProject();
     db.getDbClient().snapshotDao().insert(dbSession,
       newAnalysis(project).setCreatedAt(5L),
@@ -422,7 +480,7 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void is_last_snapshot_when_no_previous_snapshot() {
+  public void isLast_is_true_when_no_previous_snapshot() {
     SnapshotDto snapshot = defaultSnapshot();
 
     boolean isLast = SnapshotDao.isLast(snapshot, null);
@@ -431,7 +489,7 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void is_last_snapshot_when_previous_snapshot_is_older() {
+  public void isLast_is_true_when_previous_snapshot_is_older() {
     Date today = new Date();
     Date yesterday = DateUtils.addDays(today, -1);
 
@@ -444,7 +502,7 @@ public class SnapshotDaoTest {
   }
 
   @Test
-  public void is_not_last_snapshot_when_previous_snapshot_is_newer() {
+  public void isLast_is_false_when_previous_snapshot_is_newer() {
     Date today = new Date();
     Date yesterday = DateUtils.addDays(today, -1);
 
diff --git a/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/BulkDeleteAction.java b/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/BulkDeleteAction.java
index 4ebd4a4ec2d..bdf03a08326 100644
--- a/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/BulkDeleteAction.java
+++ b/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/BulkDeleteAction.java
@@ -116,7 +116,7 @@ public class BulkDeleteAction implements ProjectsWsAction {
       .setPossibleValues(Visibility.getLabels());
 
     action.createParam(PARAM_ANALYZED_BEFORE)
-      .setDescription("Filter the projects for which last analysis is older than the given date (exclusive).<br> " +
+      .setDescription("Filter the projects for which last analysis of any branch is older than the given date (exclusive).<br> " +
         "Either a date (server timezone) or datetime can be provided.")
       .setSince("6.6")
       .setExampleValue("2017-10-19 or 2017-10-19T13:00:00+0200");
diff --git a/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/SearchAction.java b/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/SearchAction.java
index 98f181aefce..2a05e663701 100644
--- a/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/SearchAction.java
+++ b/server/sonar-webserver-webapi/src/main/java/org/sonar/server/project/ws/SearchAction.java
@@ -23,6 +23,7 @@ import java.util.HashSet;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
+import java.util.stream.Collectors;
 import javax.annotation.Nullable;
 import org.sonar.api.server.ws.Request;
 import org.sonar.api.server.ws.Response;
@@ -35,6 +36,7 @@ import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.component.ComponentDto;
 import org.sonar.db.component.ComponentQuery;
+import org.sonar.db.component.ProjectLastAnalysisDateDto;
 import org.sonar.db.component.SnapshotDto;
 import org.sonar.db.permission.GlobalPermission;
 import org.sonar.server.project.Visibility;
@@ -105,7 +107,7 @@ public class SearchAction implements ProjectsWsAction {
       .setPossibleValues(Visibility.getLabels());
 
     action.createParam(PARAM_ANALYZED_BEFORE)
-      .setDescription("Filter the projects for which last analysis is older than the given date (exclusive).<br> " +
+      .setDescription("Filter the projects for which the last analysis of all branches are older than the given date (exclusive).<br> " +
         "Either a date (server timezone) or datetime can be provided.")
       .setSince("6.6")
       .setExampleValue("2017-10-19 or 2017-10-19T13:00:00+0200");
@@ -154,10 +156,13 @@ public class SearchAction implements ProjectsWsAction {
       Paging paging = buildPaging(dbSession, request, query);
       List<ComponentDto> components = dbClient.componentDao().selectByQuery(dbSession, query, paging.offset(), paging.pageSize());
       Set<String> componentUuids = components.stream().map(ComponentDto::uuid).collect(MoreCollectors.toHashSet(components.size()));
+      Map<String, Long> lastAnalysisDateByComponentUuid = dbClient.snapshotDao().selectLastAnalysisDateByProjects(dbSession, componentUuids).stream()
+        .collect(Collectors.toMap(ProjectLastAnalysisDateDto::getProjectUuid, ProjectLastAnalysisDateDto::getDate));
       Map<String, SnapshotDto> snapshotsByComponentUuid = dbClient.snapshotDao()
         .selectLastAnalysesByRootComponentUuids(dbSession, componentUuids).stream()
         .collect(MoreCollectors.uniqueIndex(SnapshotDto::getComponentUuid, identity()));
-      return buildResponse(components, snapshotsByComponentUuid, paging);
+
+      return buildResponse(components, snapshotsByComponentUuid, lastAnalysisDateByComponentUuid, paging);
     }
   }
 
@@ -171,7 +176,7 @@ public class SearchAction implements ProjectsWsAction {
       query.setPartialMatchOnKey(true);
     });
     ofNullable(request.getVisibility()).ifPresent(v -> query.setPrivate(Visibility.isPrivate(v)));
-    ofNullable(request.getAnalyzedBefore()).ifPresent(d -> query.setAnalyzedBefore(parseDateOrDateTime(d).getTime()));
+    ofNullable(request.getAnalyzedBefore()).ifPresent(d -> query.setAllBranchesAnalyzedBefore(parseDateOrDateTime(d).getTime()));
     query.setOnProvisionedOnly(request.isOnProvisionedOnly());
     ofNullable(request.getProjects()).ifPresent(keys -> query.setComponentKeys(new HashSet<>(keys)));
 
@@ -185,7 +190,8 @@ public class SearchAction implements ProjectsWsAction {
       .andTotal(total);
   }
 
-  private static SearchWsResponse buildResponse(List<ComponentDto> components, Map<String, SnapshotDto> snapshotsByComponentUuid, Paging paging) {
+  private static SearchWsResponse buildResponse(List<ComponentDto> components, Map<String, SnapshotDto> snapshotsByComponentUuid,
+    Map<String, Long> lastAnalysisDateByComponentUuid, Paging paging) {
     SearchWsResponse.Builder responseBuilder = newBuilder();
     responseBuilder.getPagingBuilder()
       .setPageIndex(paging.pageIndex())
@@ -194,20 +200,19 @@ public class SearchAction implements ProjectsWsAction {
       .build();
 
     components.stream()
-      .map(dto -> dtoToProject(dto, snapshotsByComponentUuid.get(dto.uuid())))
+      .map(dto -> dtoToProject(dto, snapshotsByComponentUuid.get(dto.uuid()), lastAnalysisDateByComponentUuid.get(dto.uuid())))
       .forEach(responseBuilder::addComponents);
     return responseBuilder.build();
   }
 
-  private static Component dtoToProject(ComponentDto dto, @Nullable SnapshotDto snapshot) {
+  private static Component dtoToProject(ComponentDto dto, @Nullable SnapshotDto snapshot, @Nullable Long lastAnalysisDate) {
     Component.Builder builder = Component.newBuilder()
       .setKey(dto.getDbKey())
       .setName(dto.name())
       .setQualifier(dto.qualifier())
       .setVisibility(dto.isPrivate() ? PRIVATE.getLabel() : PUBLIC.getLabel());
     if (snapshot != null) {
-      // FIXME created_at should not be nullable
-      ofNullable(snapshot.getCreatedAt()).ifPresent(d -> builder.setLastAnalysisDate(formatDateTime(d)));
+      ofNullable(lastAnalysisDate).ifPresent(d -> builder.setLastAnalysisDate(formatDateTime(d)));
       ofNullable(snapshot.getRevision()).ifPresent(builder::setRevision);
     }
 
diff --git a/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/BulkDeleteActionTest.java b/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/BulkDeleteActionTest.java
index 05fa73c3157..d47cbb4f59d 100644
--- a/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/BulkDeleteActionTest.java
+++ b/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/BulkDeleteActionTest.java
@@ -149,7 +149,9 @@ public class BulkDeleteActionTest {
     ComponentDto oldProject = db.components().insertPublicProject();
     db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(oldProject).setCreatedAt(aLongTimeAgo));
     ComponentDto recentProject = db.components().insertPublicProject();
-    db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(recentProject).setCreatedAt(recentTime));
+    db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(recentProject).setCreatedAt(aLongTimeAgo));
+    ComponentDto branch = db.components().insertProjectBranch(recentProject);
+    db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(branch).setCreatedAt(recentTime));
     db.commit();
 
     ws.newRequest()
diff --git a/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/SearchActionTest.java b/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/SearchActionTest.java
index 06e5511170a..5c9278d7d9e 100644
--- a/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/SearchActionTest.java
+++ b/server/sonar-webserver-webapi/src/test/java/org/sonar/server/project/ws/SearchActionTest.java
@@ -20,6 +20,8 @@
 package org.sonar.server.project.ws;
 
 import com.google.common.base.Joiner;
+import java.time.OffsetDateTime;
+import java.time.ZoneOffset;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collections;
@@ -29,6 +31,7 @@ import org.junit.Rule;
 import org.junit.Test;
 import org.sonar.api.server.ws.WebService;
 import org.sonar.api.server.ws.WebService.Param;
+import org.sonar.api.utils.DateUtils;
 import org.sonar.db.DbTester;
 import org.sonar.db.component.ComponentDto;
 import org.sonar.db.component.ComponentTesting;
@@ -46,10 +49,11 @@ import static java.util.Collections.singletonList;
 import static java.util.Optional.ofNullable;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.assertThatThrownBy;
+import static org.assertj.core.api.Assertions.tuple;
 import static org.sonar.api.server.ws.WebService.Param.PAGE;
 import static org.sonar.api.server.ws.WebService.Param.PAGE_SIZE;
 import static org.sonar.api.server.ws.WebService.Param.TEXT_QUERY;
-import static org.sonar.api.utils.DateUtils.formatDate;
+import static org.sonar.api.utils.DateUtils.formatDateTime;
 import static org.sonar.api.utils.DateUtils.parseDateTime;
 import static org.sonar.db.component.ComponentTesting.newDirectory;
 import static org.sonar.db.component.ComponentTesting.newFileDto;
@@ -186,18 +190,33 @@ public class SearchActionTest {
   public void search_for_old_projects() {
     userSession.addPermission(ADMINISTER);
     long aLongTimeAgo = 1_000_000_000L;
+    long inBetween = 2_000_000_000L;
     long recentTime = 3_000_000_000L;
+
     ComponentDto oldProject = db.components().insertPublicProject();
     db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(oldProject).setCreatedAt(aLongTimeAgo));
+    ComponentDto branch = db.components().insertProjectBranch(oldProject);
+    db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(branch).setCreatedAt(inBetween));
+
     ComponentDto recentProject = db.components().insertPublicProject();
     db.getDbClient().snapshotDao().insert(db.getSession(), newAnalysis(recentProject).setCreatedAt(recentTime));
     db.commit();
 
-    SearchWsResponse result = call(SearchRequest.builder().setAnalyzedBefore(formatDate(new Date(recentTime))).build());
+    SearchWsResponse result = call(SearchRequest.builder().setAnalyzedBefore(toStringAtUTC(new Date(recentTime + 1_000))).build());
+    assertThat(result.getComponentsList()).extracting(Component::getKey, Component::getLastAnalysisDate)
+      .containsExactlyInAnyOrder(tuple(oldProject.getKey(), formatDateTime(inBetween)), tuple(recentProject.getKey(), formatDateTime(recentTime)));
 
-    assertThat(result.getComponentsList()).extracting(Component::getKey)
-      .containsExactlyInAnyOrder(oldProject.getKey())
-      .doesNotContain(recentProject.getKey());
+    result = call(SearchRequest.builder().setAnalyzedBefore(toStringAtUTC(new Date(recentTime))).build());
+    assertThat(result.getComponentsList()).extracting(Component::getKey, Component::getLastAnalysisDate)
+      .containsExactlyInAnyOrder(tuple(oldProject.getKey(), formatDateTime(inBetween)));
+
+    result = call(SearchRequest.builder().setAnalyzedBefore(toStringAtUTC(new Date(aLongTimeAgo + 1_000L))).build());
+    assertThat(result.getComponentsList()).isEmpty();
+  }
+
+  private static String toStringAtUTC(Date d) {
+    OffsetDateTime offsetTime = d.toInstant().atOffset(ZoneOffset.UTC);
+    return DateUtils.formatDateTime(offsetTime);
   }
 
   @Test
-- 
2.39.5