Fix `doctor --run check-db-consistency --fix` with label fix (#16094)

* Add doctor for wrong label and issue_label data

* Fix labels and issue labels check

* Remove unnecessary functions

1 files changed, 24 insertions, 17 deletions

diff --git a/models/consistency.go b/models/consistency.go
index 77a8018266..9cfd02195e 100644
--- a/models/consistency.go
+++ b/models/consistency.go
@@ -180,17 +180,21 @@ func CountOrphanedLabels() (int64, error) {
 	}
 
 	norepo, err := x.Table("label").
-		Join("LEFT", "repository", "label.repo_id=repository.id").
-		Where(builder.IsNull{"repository.id"}).And(builder.Gt{"label.repo_id": 0}).
-		Count("id")
+		Where(builder.And(
+			builder.Gt{"repo_id": 0},
+			builder.NotIn("repo_id", builder.Select("id").From("repository")),
+		)).
+		Count()
 	if err != nil {
 		return 0, err
 	}
 
 	noorg, err := x.Table("label").
-		Join("LEFT", "`user`", "label.org_id=`user`.id").
-		Where(builder.IsNull{"`user`.id"}).And(builder.Gt{"label.org_id": 0}).
-		Count("id")
+		Where(builder.And(
+			builder.Gt{"org_id": 0},
+			builder.NotIn("org_id", builder.Select("id").From("user")),
+		)).
+		Count()
 	if err != nil {
 		return 0, err
 	}
@@ -206,17 +210,21 @@ func DeleteOrphanedLabels() error {
 	}
 
 	// delete labels with none existing repos
-	if _, err := x.In("id", builder.Select("label.id").From("label").
-		Join("LEFT", "repository", "label.repo_id=repository.id").
-		Where(builder.IsNull{"repository.id"}).And(builder.Gt{"label.repo_id": 0})).
+	if _, err := x.
+		Where(builder.And(
+			builder.Gt{"repo_id": 0},
+			builder.NotIn("repo_id", builder.Select("id").From("repository")),
+		)).
 		Delete(Label{}); err != nil {
 		return err
 	}
 
 	// delete labels with none existing orgs
-	if _, err := x.In("id", builder.Select("label.id").From("label").
-		Join("LEFT", "`user`", "label.org_id=`user`.id").
-		Where(builder.IsNull{"`user`.id"}).And(builder.Gt{"label.org_id": 0})).
+	if _, err := x.
+		Where(builder.And(
+			builder.Gt{"org_id": 0},
+			builder.NotIn("org_id", builder.Select("id").From("user")),
+		)).
 		Delete(Label{}); err != nil {
 		return err
 	}
@@ -227,15 +235,14 @@ func DeleteOrphanedLabels() error {
 // CountOrphanedIssueLabels return count of IssueLabels witch have no label behind anymore
 func CountOrphanedIssueLabels() (int64, error) {
 	return x.Table("issue_label").
-		Join("LEFT", "label", "issue_label.label_id = label.id").
-		Where(builder.IsNull{"label.id"}).Count()
+		NotIn("label_id", builder.Select("id").From("label")).
+		Count()
 }
 
 // DeleteOrphanedIssueLabels delete IssueLabels witch have no label behind anymore
 func DeleteOrphanedIssueLabels() error {
-	_, err := x.In("id", builder.Select("issue_label.id").From("issue_label").
-		Join("LEFT", "label", "issue_label.label_id = label.id").
-		Where(builder.IsNull{"label.id"})).
+	_, err := x.
+		NotIn("label_id", builder.Select("id").From("label")).
 		Delete(IssueLabel{})
 
 	return err